Gangaraju Gangaraju - 1 month ago 29
MySQL Question

mysql_upgrade failed - innodb tables doesn't exist?

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

Answer

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 !