在並發截斷命令期間伺服器崩潰後 MySQL INNODB 損壞
我的伺服器今天崩潰了，我認為是由於我們的一個 INNODB 表上的並發截斷表命令。伺服器可以重新啟動，但是啟動後，每次我嘗試發出 SQL 命令時，都會出現以下錯誤：
ERROR 2006 (HY000): MySQL server has gone away這是日誌中發生的事情：
121206 01:11:12 mysqld restarted 121206 1:11:13 InnoDB: Started; log sequence number 275 559321759 InnoDB: !!! innodb_force_recovery is set to 1 !!! 121206 1:11:13 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.95-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution InnoDB: Error: trying to load index PRIMARY for table InnoDB: but the index tree has been freed! 121206 1:11:37 - mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=1048576 max_used_connections=1 max_connections=400 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 950272 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x9900950 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x46353fa0, backtrace may not be correct. Stack range sanity check OK, backtrace follows: (nil) New value of fp=0x9900950 failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x993e500 = thd->thread_id=1 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.我在網上搜尋過，我得到提示這是一個 MySQL 錯誤，但我不知道如何解決它。我正在使用 MySQL 5.0.95 版。
似乎我必須創建一個新數據庫並將舊數據轉儲到新數據庫中，但是如果我什至不能向目前數據庫發出任何 SQL 命令，我該怎麼辦？
— 更新 —
版本：‘5.0.95-log’ 套接字：’/var/lib/mysql/mysql.sock’ 埠：3306 源分佈 InnoDB：錯誤：嘗試為表 InnoDB 載入索引 PRIMARY：但是索引樹已被釋放！121206 4:13:41 - mysqld 得到信號 11；這可能是因為您遇到了錯誤。此二進製文件或與之鍊接的庫之一也可能已損壞、建構不正確或配置錯誤。此錯誤也可能是由硬體故障引起的。我們將盡最大努力收集一些資訊，希望能幫助診斷問題，但由於我們已經崩潰，肯定有問題，這可能會失敗。
key_buffer_size=134217728 read_buffer_size=1048576 max_used_connections=1 max_connections=400 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 950272 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x17fb8950 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x464a3fa0, backtrace may not be correct. Stack range sanity check OK, backtrace follows: (nil) New value of fp=0x17fb8950 failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x17ff6500 = thd->thread_id=3 The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 121206 04:13:41 mysqld restarted InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 121206 4:13:42 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 121206 4:13:43 InnoDB: Started; log sequence number 275 559323148 121206 4:13:43 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.95-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
方面#1
首先引起我注意的是這條線
InnoDB：錯誤：嘗試為表 / 載入索引 PRIMARY
這表明您有一個使用 InnoDB 儲存引擎的表
InnoDB 的有趣之處在於 PRIMARY KEY 的儲存方式。它儲存在一個名為gen_clust_index的結構中，或者更常見的稱為聚群索引。
我的直接猜測是某個 PRIMARY KEY 條目太大
請考慮一些關於使用長主鍵的好、壞和醜的文章：
然後看看是否
<DB Hidden>.<Table Hidden>需要重新設計。方面#2
就您關於並行截斷表的猜想而言，這聽起來有點危險。為什麼？InnoDB 執行 TRUNCATE TABLE 作為
DDLnotDML。我以前寫過這個：
Jul 09, 2012:什麼會導致 TRUNCATE TABLE 需要很長時間？Jan 17, 2012：InnoDB“每表”文件大小的問題Sep 28, 2011:如何恢復文件被移動的 InnoDB 表方面#3
一些調整建議
請將以下內容添加到
my.ini[mysqld] max_allowed_packet=1G innodb_fast_shutdown=0啟動mysql
在另一個會話中，執行
tail -f <errorlogfile>並觀看 InnoDB Crash Recovery。如果 mysql 已完全啟動備份並且 InnoDB 崩潰恢復已完成，請嘗試立即關閉 mysql。您可能需要調整 InnoDB 事務日誌的大小。
很抱歉這些瘋狂的建議，但我在這裡盲目飛行。
請在問題中發布以下內容：
- 你的整個
my.cnf- 板上有多少 RAM
更新 2012-12-05 12:09 EDT
請執行以下操作：
STEP 01) 將這些更改添加到
my.cnf[mysqld] max_allowed_packet=1G innodb_fast_shutdown=0 innodb_thread_concurrency=0步驟 02)
service mysql restart確保 mysql 出現
STEP 03) 你需要調整 ib_logfile0 和 ib_logfile1 的大小（24M 可能太小了）
service mysql stop cd /var/lib/mysql mv ib_logfile0 ib_logfile0.bak mv ib_logfile1 ib_logfile1.bakSTEP 04) 將這些更改添加到
my.cnf[mysqld] innodb_log_file_size=512M innodb_log_buffer_size=8M步驟 05)
service mysql startmysqld 將重新創建 ib_logfile0 和 ib_logfile1 512M
現在，試著看看會發生什麼……
更新 2012-12-05 12:18 EDT
同時，請閱讀我在 mysql 數據包上的 ServerFault 文章及其對innodb_log_file_size和innodb_log_buffer_size的大小影響，這是我從其他人的 ServerFault 文章中了解到的。
更新 2012-12-05 14:28 EDT
我從這個問題中編輯了對客戶表的所有引用。
根本原因是一個損壞的頁面，
ibdata1其中混合了數據和索引頁面。我幫助 Andrew 遷移數據，使用innodb_file_per_table重新創建 ibdata1 ，然後 Andrew 重新載入了數據。