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 ”
2021-05-23T03:15:08.788994Z 0 [ERROR] InnoDB: Table ” 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 ” 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 ”
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服务正常运行。
