mysql_upgrade失败 - innodb表不存在？ [英] mysql_upgrade failed - innodb tables doesn't exist?
本文介绍了mysql_upgrade失败 - innodb表不存在？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
错误：
root @ 17aa74cbc5e2＃mysql_upgrade -uroot -password
警告：在命令行界面上使用密码可能不安全。
寻找'mysql'为：mysql
寻找'mysqlcheck'为：mysqlcheck
运行'mysqlcheck'连接参数：'--port = 3306''--socket = / var /run/mysqld/mysqld.sock'
警告：在命令行界面上使用密码可能不安全。
运行'mysqlcheck'连接参数：'--port = 3306''--socket = / var / run / mysqld / mysqld.sock'
警告：在命令行界面上使用密码不安全
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats
错误：表'mysql.innodb_index_stats'不存在
状态：操作失败
mysql.innodb_table_stats
错误：表'mysql.innodb_table_stats'不存在
状态：操作失败
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info
错误：表'mysql.slave_master_info'不存在
状态：操作失败
mysql.slave_relay_log_info
错误：表'mysql.slave_relay_log_info'不存在
状态：操作失败
mysql.slave_worker_info
错误：表'mysql.slave_worker_info'doesn' t存在
状态：操作失败
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
修复表
mysql.innodb_index_stats
错误：表'mysql.innodb_index_stats'不存在
状态：操作失败
mysql.innodb_table_stats
错误：表'mysql.innodb_table_stats'不存在
状态：操作失败
mysql.slave_master_info
错误：表'mysql.slave_master_info'不存在
状态：操作失败
mysql.slave_relay_log_info
错误：表'mysql.slave_relay_log_info'不存在
状态：操作失败
mysql.slave_worker_info
错误：表' mysql.slave_worker_info'不存在
状态：操作失败
运行'mysql_fix_privilege_tables'...
警告：在命令行界面上使用密码可能不安全。
第62行错误1146（42S02）：表'mysql.innodb_table_stats'不存在
错误1243（HY000）在第63行：未知的预处理语句处理程序（stmt）给EXECUTE
错误1243（HY000）在第64行：未知的准备好的语句处理程序（stmt）给予DEALLOCATE PREPARE
错误1146（42S02）在行66：表'mysql.innodb_index_stats'不存在
ERROR 1243（ HY000）在第67行：未知的预处理语句处理程序（stmt）给EXECUTE
错误1243（HY000）在第68行：未知的准备好的声明处理程序（stmt）给予DEALLOCATE PREPARE
错误1146（42S02）at第81行：表'mysql.slave_relay_log_info'不存在
错误1243（HY000）在第82行：未知的准备好的语句处理程序（stmt）给EXECUTE
错误1243（HY000）在第83行：未知准备的语句处理程序（stmt）给予DEALLOCATE PREPARE
错误1146（42S02）在行110：表'mysql.slave_master_info'不存在
错误1243（HY000）在行111：Un已知的准备好的语句处理程序（stmt）给予EXECUTE
错误1243（HY000）在第112行：未知的准备好的语句处理程序（stmt）给予DEALLOCATE PREPARE
错误1146（42S02）在第128行：表'mysql .slave_worker_info'不存在
错误1243（HY000）在第129行：未知的准备好的语句处理程序（stmt）给EXECUTE
错误1243（HY000）在第130行：未知的准备好的语句处理程序（stmt）给予DEALLOCATE PREPARE
错误1146（42S02）在行1896：表'mysql.slave_master_info'不存在
错误1146（42S02）行1897：表'mysql.slave_master_info'不存在
第1898行错误1146（42S02）：表'mysql.slave_master_info'不存在
第1899行错误1146（42S02）：表'mysql.slave_worker_info'不存在
错误1146（42S02）在第1900行：表'mysql.slave_relay_log_info'不存在
错误1146（42S02）在行1904：表'mysql.innodb_table_stats'不存在
第1908行错误1146（42S02）：表'mysql.innodb_index_stats'不存在
致命错误：升级失败
解决方案
我可以通过删除 / var / lib / mysql / ibdata * 旧容器中的文件（5.5）。我将旧容器（5.5）的 / var / lib / mysql 目录中的内容复制到新容器（5.6） / var / lib / MySQL的。如果我跳过复制 / var / lib / mysql / ibdata * 文件，这是正常的。
+ --------------------------- +
| Tables_in_mysql |
+ --------------------------- +
| columns_priv |
| db |
|事件|
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
- |主机|
+ | innodb_index_stats |
+ | innodb_table_stats |
| ndb_binlog_index |
|插件|
| proc |
| procs_priv |
| proxies_priv |
|服务器|
+ | slave_master_info |
+ | slave_relay_log_info |
+ | slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
|用户|
+ --------------------------- +
发现这些表是在mysql-5.6中添加/删除的。复制/使用旧的ibdata文件导致与innodb表和ibdata文件不一致。虽然.frm，.idb文件存在，因为较旧的ibdata它的失败。
正如这个链接，尝试删除该文件， mysql_upgrade 成功完成。 >
由mysql_install_db创建的ibdata *数据文件已损坏，必须在启动mysqld之前将其删除！
I am upgrading my mysql-5.5 docker container database to mysql-5.6 docker container. I was able to fix all other problems. Finally my server is running with 5.6. But when i run mysql_upgrade i am getting the following error.
ERROR:
root@17aa74cbc5e2# mysql_upgrade -uroot -password
Warning: Using a password on the command line interface can be insecure.
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist
status : Operation failed
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slave_master_info
Error : Table 'mysql.slave_master_info' doesn't exist
status : Operation failed
mysql.slave_relay_log_info
Error : Table 'mysql.slave_relay_log_info' doesn't exist
status : Operation failed
mysql.slave_worker_info
Error : Table 'mysql.slave_worker_info' doesn't exist
status : Operation failed
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Repairing tables
mysql.innodb_index_stats
Error : Table 'mysql.innodb_index_stats' doesn't exist
status : Operation failed
mysql.innodb_table_stats
Error : Table 'mysql.innodb_table_stats' doesn't exist
status : Operation failed
mysql.slave_master_info
Error : Table 'mysql.slave_master_info' doesn't exist
status : Operation failed
mysql.slave_relay_log_info
Error : Table 'mysql.slave_relay_log_info' doesn't exist
status : Operation failed
mysql.slave_worker_info
Error : Table 'mysql.slave_worker_info' doesn't exist
status : Operation failed
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 62: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1243 (HY000) at line 63: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 64: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1146 (42S02) at line 66: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1243 (HY000) at line 67: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 68: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1146 (42S02) at line 81: Table 'mysql.slave_relay_log_info' doesn't exist
ERROR 1243 (HY000) at line 82: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 83: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1146 (42S02) at line 110: Table 'mysql.slave_master_info' doesn't exist
ERROR 1243 (HY000) at line 111: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 112: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1146 (42S02) at line 128: Table 'mysql.slave_worker_info' doesn't exist
ERROR 1243 (HY000) at line 129: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1243 (HY000) at line 130: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
ERROR 1146 (42S02) at line 1896: Table 'mysql.slave_master_info' doesn't exist
ERROR 1146 (42S02) at line 1897: Table 'mysql.slave_master_info' doesn't exist
ERROR 1146 (42S02) at line 1898: Table 'mysql.slave_master_info' doesn't exist
ERROR 1146 (42S02) at line 1899: Table 'mysql.slave_worker_info' doesn't exist
ERROR 1146 (42S02) at line 1900: Table 'mysql.slave_relay_log_info' doesn't exist
ERROR 1146 (42S02) at line 1904: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 1908: Table 'mysql.innodb_index_stats' doesn't exist
FATAL ERROR: Upgrade failed
解决方案
I was able to fix this by deleting /var/lib/mysql/ibdata* files from the old container(5.5). I am copying content inside /var/lib/mysql directory of my old container(5.5) to the new container(5.6) /var/lib/mysql. It's working if i skip copying /var/lib/mysql/ibdata* file.
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
- | host |
+ | innodb_index_stats |
+ | innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
+ | slave_master_info |
+ | slave_relay_log_info |
+ | slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
Found that these are tables are added/removed in mysql-5.6. Copying/Using old ibdata files leads to inconsistency with innodb tables and ibdata files. Though the .frm,.idb files are present, because of older ibdata its failing.
As mentioned in this link, tried to delete that file and mysql_upgrade finished successfully.
The ibdata* data files created by mysql_install_db are corrupt and must be trashed before starting mysqld !
这篇关于mysql_upgrade失败 - innodb表不存在？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
