InnoDB table SELECT 返回 ERROR 2006 (HY000): MySQL server has gone away (after power outage)

此伺服器是執行 CentOS 6.0、MySQL 5.5.21 的虛擬機

有一個名為 的數據庫devSystem。裡面有 InnoDB 表。執行以下命令會導致錯誤ERROR 2006 (HY000): MySQL server has gone away。我以前沒有廣泛使用過 InnoDB，所以還沒有遇到過這類問題。我只能假設它特定於 InnoDB，因為它沒有使用 MyISAM 浮出水面。無論如何，我嘗試執行的命令如下。

mysql -u root -p
mysql> USE `devSystem`;
-- database changed
mysql> SHOW TABLES;

MySQL 客戶端返回的完整錯誤

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: devSystem

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

現在嘗試從baskets存在的表中選擇devSystem

mysql> select * from baskets\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: devSystem

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

為了比較，我嘗試了第二個數據庫，它在同一台伺服器上也有 InnoDB 表

mysql> USE `testSystem`;
-- database changed
mysql> SHOW TABLES;

這會正確輸出表格。出於這個原因，我推測這是一個devSystem具體的問題。

我試過在這裡搜尋，但是其他類似的查詢似乎沒有幫助，有沒有人有任何建議/建議可以幫助我解決這個問題？到目前為止，這已經浪費了我整個上午！

目前的選項似乎是刪除數據庫並重新開始（但是會失去大量工作。我有一個幾乎最新的數據庫設計，但是沒有備份迄今為止生成的數據）

update 1 添加innodb_force_recovery = 6到my.cnf允許SHOW TABLE STATUS成功執行，<= 5 的值仍然會導致如上所示的錯誤。使用此標誌SELECT * FROM baskets執行，但是一個表具體返回的錯誤仍然表明它是一個可能導致問題的表？

mysql> SELECT * FROM supplierOptionalExtras_relationships;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: devSystem

ERROR 2013 (HY000): Lost connection to MySQL server during query

結論

最終，在掃描恢復文件更多小時後，我只能得出結論，我的數據失去了（正如 RolandoMySQLDBA 所說）。為了做到這一點，我遵循了 DTest 關於 Percona 恢復工具的建議，但是數據損壞意味著這些工具無法從我的ibdata1文件中提取特定表的數據。

最後我使用了 RolandoMySQLDBA 的答案並做了以下

1. 遵循此處的說明https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261不包括崩潰的數據庫
2. 用於innodb_force_recovery = 6從有崩潰表的數據庫中獲取所有非崩潰表數據
3. 關閉 MySQl，removed innodb_force_recovery = 6，刪除 ibdata/iblog 文件（詳見步驟 1 中的連結）
4. 啟動 MySQL 並載入轉儲數據
5. 從設計文件重新創建壞表
6. 手動重新填充數據

當然，這意味著受影響的表中的全部數據失去是一個問題，但是我只能希望如果innodb_file_per_table再次發生這種情況，添加將有助於數據恢復 - 我打算在某個時候扼殺電源以嘗試在測試中重現它數據庫。

---

下面是來自日誌的一些更詳細的資訊。

我的.cnf

這只是一個開發系統，所以 my.cnf 非常基本，實際上與預設值相同

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

從伺服器開始到執行失敗查詢之前的 MySQL 日誌

120302 10:57:42 [Note] /usr/libexec/mysqld: Normal shutdown

120302 10:57:42 [Note] Event Scheduler: Purging the queue. 0 events
120302 10:57:42  InnoDB: Starting shutdown...
120302 10:57:42  InnoDB: Shutdown completed; log sequence number 285938465
120302 10:57:42 [Note] /usr/libexec/mysqld: Shutdown complete

120302 10:57:42 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120302 10:57:43 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120302 10:57:43 [Note] Plugin 'FEDERATED' is disabled.
120302 10:57:43 InnoDB: The InnoDB memory heap is disabled
120302 10:57:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120302 10:57:43 InnoDB: Compressed tables use zlib 1.2.3
120302 10:57:43 InnoDB: Using Linux native AIO
120302 10:57:43 InnoDB: Initializing buffer pool, size = 128.0M
120302 10:57:43 InnoDB: Completed initialization of buffer pool
120302 10:57:43 InnoDB: highest supported file format is Barracuda.
120302 10:57:43  InnoDB: Waiting for the background threads to start
120302 10:57:44 InnoDB: 1.1.8 started; log sequence number 285938465
120302 10:57:44 [Note] Event Scheduler: Loaded 0 events
120302 10:57:44 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.15'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi

查詢失敗後的 MySQL 日誌行

120302 10:58:39  InnoDB: Assertion failure in thread 140030446421760 in file btr0btr.c line 695
InnoDB: Failing assertion: (ibool)!!page_is_comp(buf_block_get_frame(block)) == dict_table_is_comp(index->table)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
120302 10:58:39 - mysqld got signal 6 ;
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=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338483 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x30396e0
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...
stack_bottom = 0x7f5b61043d98 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x33)[0x77b6e3]
/usr/libexec/mysqld(handle_segfault+0x465)[0x50fc35]
/lib64/libpthread.so.0[0x37a9e0f4c0]
/lib64/libc.so.6(gsignal+0x35)[0x37a9a329a5]
/lib64/libc.so.6(abort+0x175)[0x37a9a34185]
/usr/libexec/mysqld[0x82a4bb]
/usr/libexec/mysqld[0x82a60c]
/usr/libexec/mysqld[0x85d133]
/usr/libexec/mysqld[0x862207]
/usr/libexec/mysqld[0x7e37a3]
/usr/libexec/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3d)[0x66903d]
/usr/libexec/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x537)[0x5edf67]
/usr/libexec/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0xc33)[0x54d2d3]
/usr/libexec/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x683)[0x54e043]
/usr/libexec/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x4b)[0x54e61b]
/usr/libexec/mysqld(_Z18mysqld_list_fieldsP3THDP10TABLE_LISTPKc+0x23)[0x5c5623]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x174f)[0x581f2f]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xd2)[0x60e9e2]
/usr/libexec/mysqld(handle_one_connection+0x50)[0x60eaf0]
/lib64/libpthread.so.0[0x37a9e077e1]
/lib64/libc.so.6(clone+0x6d)[0x37a9ae18ed]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f5b50004c20): is an invalid pointer
Connection ID (thread ID): 2
Status: NOT_KILLED

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.
120302 10:58:39 mysqld_safe Number of processes running now: 0
120302 10:58:39 mysqld_safe mysqld restarted
120302 10:58:39 [Note] Plugin 'FEDERATED' is disabled.
120302 10:58:39 InnoDB: The InnoDB memory heap is disabled
120302 10:58:39 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120302 10:58:39 InnoDB: Compressed tables use zlib 1.2.3
120302 10:58:39 InnoDB: Using Linux native AIO
120302 10:58:39 InnoDB: Initializing buffer pool, size = 128.0M
120302 10:58:39 InnoDB: Completed initialization of buffer pool
120302 10:58:39 InnoDB: highest supported file format is Barracuda.
120302 10:58:39  InnoDB: Waiting for the background threads to start
120302 10:58:40 InnoDB: 1.1.8 started; log sequence number 285938465
120302 10:58:40 [Note] Event Scheduler: Loaded 0 events
120302 10:58:40 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.15'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi
120302 10:58:47  InnoDB: Assertion failure in thread 140051237820160 in file btr0btr.c line 695
InnoDB: Failing assertion: (ibool)!!page_is_comp(buf_block_get_frame(block)) == dict_table_is_comp(index->table)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
120302 10:58:47 - mysqld got signal 6 ;
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=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338483 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x231b6e0
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...
stack_bottom = 0x7f603847cd98 thread_stack 0x40000
/usr/libexec/mysqld(my_print_stacktrace+0x33)[0x77b6e3]
/usr/libexec/mysqld(handle_segfault+0x465)[0x50fc35]
/lib64/libpthread.so.0[0x37a9e0f4c0]
/lib64/libc.so.6(gsignal+0x35)[0x37a9a329a5]
/lib64/libc.so.6(abort+0x175)[0x37a9a34185]
/usr/libexec/mysqld[0x82a4bb]
/usr/libexec/mysqld[0x82a60c]
/usr/libexec/mysqld[0x85d133]
/usr/libexec/mysqld[0x862207]
/usr/libexec/mysqld[0x7e37a3]
/usr/libexec/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x3d)[0x66903d]
/usr/libexec/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x537)[0x5edf67]
/usr/libexec/mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0xc33)[0x54d2d3]
/usr/libexec/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x683)[0x54e043]
/usr/libexec/mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x4b)[0x54e61b]
/usr/libexec/mysqld(_Z18mysqld_list_fieldsP3THDP10TABLE_LISTPKc+0x23)[0x5c5623]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x174f)[0x581f2f]
/usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0xd2)[0x60e9e2]
/usr/libexec/mysqld(handle_one_connection+0x50)[0x60eaf0]
/lib64/libpthread.so.0[0x37a9e077e1]
/lib64/libc.so.6(clone+0x6d)[0x37a9ae18ed]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f6014004c20): is an invalid pointer
Connection ID (thread ID): 1
Status: NOT_KILLED

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.
120302 10:58:47 mysqld_safe Number of processes running now: 0
120302 10:58:47 mysqld_safe mysqld restarted
120302 10:58:47 [Note] Plugin 'FEDERATED' is disabled.
120302 10:58:47 InnoDB: The InnoDB memory heap is disabled
120302 10:58:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120302 10:58:47 InnoDB: Compressed tables use zlib 1.2.3
120302 10:58:47 InnoDB: Using Linux native AIO
120302 10:58:47 InnoDB: Initializing buffer pool, size = 128.0M
120302 10:58:47 InnoDB: Completed initialization of buffer pool
120302 10:58:47 InnoDB: highest supported file format is Barracuda.
120302 10:58:47  InnoDB: Waiting for the background threads to start
120302 10:58:48 InnoDB: 1.1.8 started; log sequence number 285938465
120302 10:58:48 [Note] Event Scheduler: Loaded 0 events
120302 10:58:48 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.15'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi

這看起來奇怪的熟悉。

我已經看到我的一個網路託管客戶端的數據庫伺服器發生了這種情況。有一個特定的表在你每次訪問它時都會崩潰 mysqld，即使是SHOW CREATE TABLE.

問題源於損壞的數據字典。真的沒有辦法改正。您可以嘗試更改 .ibd 文件中的 tablespace_id，但頭痛源於將 tablespace_id 列表定位到 ibdata1 內部。

即使在與原始 InnoDB 表相同的數據庫中創建同名的 MyISAM 表，也無法將其轉換為 InnoDB，因為 tablespace_id 已經與表名關聯。當然，這是一種損壞的狀態。這就像在 ibdata1 中有一個海溝洞，如果不進行一些探索性手術，您將無法修補。

除了包含損壞表的數據庫之外，您可能必須對所有內容進行 mysqldump。然後，您將不得不對該數據庫中的每個表進行 mysqldump，但損壞的表除外。請記住，搞砸的是表的數據字典視圖，不一定是表的數據。

清理所有內容的唯一可靠方法是執行我剛剛指定的 mysqldumps，關閉 mysql，rm -rf 除 /var/lib/mytsql/mysql 之外的所有數據庫文件夾，刪除 ibdata1，刪除 ib_logfile0，刪除 ib_logfile1，啟動 mysql，重新載入所有 mysqldumps。請參閱我關於清理 InnoDB 基礎架構的 StackOverflow 文章。

由於您沒有使用 innodb_file_per_table，因此 ibdata1 中具有這種損壞狀態的任何表都會作為戰爭的傷亡人員而失去。節哀順變。

供將來參考，請點擊此處查看InnoDB 及其內部的藝術構想。

引用自：https://dba.stackexchange.com/questions/14259