:::: MENU ::::

MySQLを5.1から5.6.10に上げたらなんか壊れた話

おそらく MySQL5.1 から 5.6 へ上げた影響だと思われるが、
ユーザの権限を設定することができなくなっていた。

mysql> grant all privileges on testdb.* to testuser@localhost identified by 'testpass';
ERROR 2013 (HY000): Lost connection to MySQL server during query

エラーログには以下のような文言がでていた。
バージョン違いの SQL をぶち込んだため、5.6 で必要なテーブルがなくなったのかな。

InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

これ以外にもエラーが出ているテーブルがたくさんあったが、
たぶんすべての原因はこいつ。

ググるとどうにも MySQL にバグ?っぽいので、
対応 SQL ファイルを取り込んでみる。

# mysql -u root -p -t mysql < five-tables.sql
# mysql -u root -p
mysql> grant all privileges on testdb.* to testuser@localhost identified by 'testpass';
ERROR 2013 (HY000): Lost connection to MySQL server during query

はい、ざんねーん。変わりません。
でもエラーログの内容は変わってました。

...
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_user_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_host_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_global_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'events_statements_summary_by_digest' has the wrong structure
[ERROR] Native table 'performance_schema'.'users' has the wrong structure
[ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
[ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
[ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
[ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
[ERROR] Native table 'performance_schema'.'socket_summary_by_event_name' has the wrong structure
[ERROR] Native table 'performance_schema'.'session_connect_attrs' has the wrong structure
[ERROR] Native table 'performance_schema'.'session_account_connect_attrs' has the wrong structure
...

データベースが壊れているっぽいですね。

上記 URL を参考にすべての DB を upgrade してみる。

# mysql_upgrade -u root
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/lib/mysql/mysql.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/lib/mysql/mysql.sock'
mroonga.diaries                                    OK
mroonga.memos                                      OK
mroonga.shops                                      OK
mroonga.snippet_test                               OK
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.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
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                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
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
test01.attachment                                  OK
test01.auth_cookie                                 OK
test01.cache                                       OK
test01.component                                   OK
test01.enum                                        OK
test01.milestone                                   OK
test01.node_change                                 OK
test01.permission                                  OK
test01.report                                      OK
test01.repository                                  OK
test01.revision                                    OK
test01.session                                     OK
test01.session_attribute                           OK
test01.system                                      OK
test01.ticket                                      OK
test01.ticket_change                               OK
test01.ticket_custom                               OK
test01.version                                     OK
test01.wiki                                        OK
wordpress.wp_commentmeta                           OK
wordpress.wp_comments                              OK
wordpress.wp_links                                 OK
wordpress.wp_options                               OK
wordpress.wp_popularpostsdata                      OK
wordpress.wp_popularpostsdatacache                 OK
wordpress.wp_postmeta                              OK
wordpress.wp_posts                                 OK
wordpress.wp_term_relationships                    OK
wordpress.wp_term_taxonomy                         OK
wordpress.wp_terms                                 OK
wordpress.wp_usermeta                              OK
wordpress.wp_users                                 OK
Running 'mysql_fix_privilege_tables'...
OK

再度挑戦!

mysql> grant all privileges on testdb.* to testuser@localhost identified by 'testpass';
Query OK, 0 rows affected (0.00 sec)

できたーー ♪