MySQL Error number: MY-012981; Symbol: ER_IB_MSG_1156; SQLSTATE: HY000 报错 故障修复 远程处理

文档解释

Error number: MY-012981; Symbol: ER_IB_MSG_1156; SQLSTATE: HY000

Message: Cannot continue operation.

错误说明

ER_IB_MSG_1156 是MySQL/InnoDB特定错误代码，它指示一个潜在的不可恢复的数据库损坏，就像损坏的缓存文件或无效的表空间文件。当MySQL/ InnoDB存储引擎判断它无法恢复数据库时，会将此错误写入日志。

常见案例

此错误通常由应用或运维的交互引发，具体表现为在MySQL访问时发生报错，内容为ER_IB_MSG_1156，这时无法操作数据库或建表，且伴随其他InnoDB异常消息，示例如下：

2021-05-23T03:15:08.788234Z 0 [ERROR] InnoDB: Operating system error number 11 in a file operation.

2021-05-23T03:15:08.788271Z 0 [ERROR] InnoDB: Error number 11 means ‘Resource temporarily unavailable’.

2021-05-23T03:15:08.788278Z 0 [ERROR] InnoDB: Does InnoDB need to be closed down?

2021-05-23T03:15:08.788283Z 0 [ERROR] InnoDB: Check if there are any InnoDB files which can be renamed or removed.

2021-05-23T03:15:08.788288Z 0 [ERROR] InnoDB: Cannot continue operation.

2021-05-23T03:15:08.788513Z 0 [ERROR] InnoDB: Error: Failing system call in file and line

2021-05-23T03:15:08.788518Z 0 [ERROR] InnoDB: ibuf0ibuf.cc:1672

2021-05-23T03:15:08.788525Z 0 [ERROR] InnoDB: Cannot continue operation.

2021-05-23T03:15:08.788558Z 0 [ERROR] InnoDB: Cannot continue operation.

2021-05-23T03:15:08.788580Z 0 [ERROR] InnoDB: Failed to load metadata for table ‘xxx’

2021-05-23T03:15:08.788994Z 0 [ERROR] InnoDB: Table ‘xxx’ does not exist in the InnoDB internal

2021-05-23T03:15:08.789000Z 0 [ERROR] InnoDB: data dictionary though MySQL is trying to drop it.

2021-05-23T03:15:08.789035Z 0 [ERROR] InnoDB: Cannot delete ‘xxx’ table

2021-05-23T03:15:08.789060Z 0 [ERROR] InnoDB: Error: trying to delete a table, but the .ibd file does not exist though the table existed in the InnoDB internal data dictionary. Have you deleted the .frm file and not used DROP TABLE?

2021-05-23T03:15:08.789195Z 0 [Note] InnoDB: Operating system error number 11 in a file operation.

2021-05-23T03:15:08.789603Z 0 [ERROR] InnoDB: Error: cannot delete or rename ‘xxx’

2021-05-23T03:15:08.789609Z 0 [ERROR] InnoDB: because it is not in the InnoDB internal

2021-05-23T03:15:08.789614Z 0 [ERROR] InnoDB: data dictionary though MySQL is trying to drop it.

2021-05-23T03:15:08.789694Z 0 [ERROR] InnoDB: Aborting

2021-05-23T03:15:08.789697Z 0 [Note] InnoDB: Sending signal in the background to central management server

2021-05-23T03:15:08.789724Z 0 [ERROR] InnoDB: Error in sending signal to the central management server.

2021-05-23T03:15:08.789792Z 0 [ERROR] InnoDB: Error: may have encountered a bad index in the system tables

2021-05-23T03:15:08.789797Z 0 [ERROR] InnoDB: Error: The table may have been corrupted

2021-05-23T03:15:08.789803Z 0 [ERROR] InnoDB: Error: in InnoDB’s internal data dictionary

2021-05-23T03:15:08.789842Z 0 [ERROR] InnoDB: Unable to repair the table and its indexes

2021-05-23T03:15:08.789885Z 0 [ERROR] InnoDB: Internal error number 1156 in the InnoDB storage engine.

解决方法

首先，尝试使用mysqladmin shutdown -u root shutdown 关闭MySQL服务，如果在关闭MySQL服务时，发现没有反应或服务无法完全关闭，则可能是有某些任务还在运行，此时可以尝试使用kill -9 释放进程，查看mysql进程的命令为：ps -ef|grep mysqld。

然后，使用sudo mysql_upgrade 运行升级操作，mysql_upgrade 是mysql提供的一个升级程序，用于更新或者删除旧版本的数据库结构，有时可以用来解决数据库结构冲突造成的问题，此外，还要检查日志中关于慢查询的日志，以查看是否有异常的查询导致服务器停止运行，如果有，可以考虑优化SQL语句或者做热备实现负载均衡。

此外，还可以考虑重新检查数据文件和日志文件，查看其读写权限时候符合MySQL需求，如果数据文件和日志文件丢失则需要备份文件进行恢复。

最后，需要重启MySQL服务，检查表结构，并清理缓存，以确保MySQL服务正常运行。

香港美国云服务器首选树叶云，2H2G首月10元开通。
树叶云（shuyeidc.com）提供简单好用，价格厚道的香港/美国云服务器和独立服务器。IDC+ISP+ICP资质。ARIN和APNIC会员。成熟技术团队15年行业经验。