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服务正常运行。

你可能感兴趣的