I was trying a mysql_upgrade, and got a number of errors from mysqlcheck. I figured for troubleshooting it would be better to call mysqlcheck directly:
So, this command:
mysqlcheck mysql
Gives this output:
mysql.columns_priv
Error : Incorrect file format 'columns_priv'
error : Corrupt
mysql.db
Error : Incorrect file format 'db'
error : Corrupt
mysql.event
Error : Incorrect file format 'event'
error : Corrupt
mysql.func
Error : Incorrect file format 'func'
error : Corrupt
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin
Error : Incorrect file format 'plugin'
error : Corrupt
mysql.proc
Error : Incorrect file format 'proc'
error : Corrupt
mysql.procs_priv
Error : Incorrect file format 'procs_priv'
error : Corrupt
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
Error : Incorrect file format 'tables_priv'
error : Corrupt
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
Error : Incorrect file format 'user'
error : Corrupt
mysql Ver 14.14 Distrib 5.6.27, for debian-linux-gnu (x86_64) using EditLine wrapper
If it helps, trying
sudo service mysql start
gives
2016-01-08 13:19:21 6867 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Incorrect file format 'plugin'
2016-01-08 13:19:21 6867 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2016-01-08 13:19:21 6867 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-08 13:19:21 6867 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-08 13:19:21 6867 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-08 13:19:21 6867 [Note] InnoDB: Memory barrier is not used
2016-01-08 13:19:21 6867 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-01-08 13:19:21 6867 [Note] InnoDB: Using Linux native AIO
2016-01-08 13:19:21 6867 [Note] InnoDB: Using CPU crc32 instructions
2016-01-08 13:19:21 6867 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-01-08 13:19:21 6867 [Note] InnoDB: Completed initialization of buffer pool
2016-01-08 13:19:21 6867 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-08 13:19:21 6867 [Note] InnoDB: The log sequence numbers 53348439582 and 53348439582 in ibdata files do not match the log sequence number 53358267557 in the ib_logfiles!
2016-01-08 13:19:21 6867 [Note] InnoDB: Database was not shutdown normally!
2016-01-08 13:19:21 6867 [Note] InnoDB: Starting crash recovery.
2016-01-08 13:19:21 6867 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-01-08 13:19:22 6867 [Note] InnoDB: Restoring possible half-written data pages
2016-01-08 13:19:22 6867 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 22857733, file name ./mysql-bin.000024
2016-01-08 13:19:22 6867 [ERROR] InnoDB: Table ******/field_data_og_group_ref in the InnoDB data dictionary has tablespace id 32634, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:22 6867 [ERROR] InnoDB: Table ******/field_revision_og_group_ref in the InnoDB data dictionary has tablespace id 32635, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:22 6867 [ERROR] InnoDB: Table mysql/gtid_slave_pos in the InnoDB data dictionary has tablespace id 32621, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:22 7fbc27fcb780 InnoDB: Error: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has tablespace id 32620,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mysql/innodb_index_stats and id 32646, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:22 7fbc27fcb780 InnoDB: Error: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has tablespace id 32619,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mysql/innodb_table_stats and id 32645, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:22 6867 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-08 13:19:22 6867 [Note] InnoDB: Waiting for purge to start
2016-01-08 13:19:22 6867 [Note] InnoDB: 5.6.27 started; log sequence number 53358267557
2016-01-08 13:19:22 6867 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-01-08 13:19:22 6867 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2016-01-08 13:19:22 6867 [Note] Server socket created on IP: '127.0.0.1'.
2016-01-08 13:19:22 6867 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'
2016-01-08 13:19:22 6920 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Incorrect file format 'plugin'
2016-01-08 13:19:22 6920 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2016-01-08 13:19:22 6920 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-08 13:19:22 6920 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-08 13:19:22 6920 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-08 13:19:22 6920 [Note] InnoDB: Memory barrier is not used
2016-01-08 13:19:22 6920 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-01-08 13:19:22 6920 [Note] InnoDB: Using Linux native AIO
2016-01-08 13:19:22 6920 [Note] InnoDB: Using CPU crc32 instructions
2016-01-08 13:19:22 6920 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-01-08 13:19:22 6920 [Note] InnoDB: Completed initialization of buffer pool
2016-01-08 13:19:22 6920 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-08 13:19:22 6920 [Note] InnoDB: The log sequence numbers 53348439582 and 53348439582 in ibdata files do not match the log sequence number 53358267557 in the ib_logfiles!
2016-01-08 13:19:22 6920 [Note] InnoDB: Database was not shutdown normally!
2016-01-08 13:19:22 6920 [Note] InnoDB: Starting crash recovery.
2016-01-08 13:19:22 6920 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-01-08 13:19:23 6920 [Note] InnoDB: Restoring possible half-written data pages
2016-01-08 13:19:23 6920 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 22857733, file name ./mysql-bin.000024
2016-01-08 13:19:23 6920 [ERROR] InnoDB: Table ******/field_data_og_group_ref in the InnoDB data dictionary has tablespace id 32634, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:23 6920 [ERROR] InnoDB: Table ******/field_revision_og_group_ref in the InnoDB data dictionary has tablespace id 32635, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:23 6920 [ERROR] InnoDB: Table mysql/gtid_slave_pos in the InnoDB data dictionary has tablespace id 32621, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:23 7f13744fb780 InnoDB: Error: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has tablespace id 32620,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mysql/innodb_index_stats and id 32646, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:23 7f13744fb780 InnoDB: Error: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has tablespace id 32619,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mysql/innodb_table_stats and id 32645, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:23 6920 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-08 13:19:23 6920 [Note] InnoDB: Waiting for purge to start
2016-01-08 13:19:23 6920 [Note] InnoDB: 5.6.27 started; log sequence number 53358267557
2016-01-08 13:19:23 6920 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-01-08 13:19:23 6920 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2016-01-08 13:19:23 6920 [Note] Server socket created on IP: '127.0.0.1'.
2016-01-08 13:19:23 6920 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'
2016-01-08 13:19:24 6973 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Incorrect file format 'plugin'
2016-01-08 13:19:24 6973 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2016-01-08 13:19:24 6973 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-01-08 13:19:24 6973 [Note] InnoDB: The InnoDB memory heap is disabled
2016-01-08 13:19:24 6973 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-01-08 13:19:24 6973 [Note] InnoDB: Memory barrier is not used
2016-01-08 13:19:24 6973 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-01-08 13:19:24 6973 [Note] InnoDB: Using Linux native AIO
2016-01-08 13:19:24 6973 [Note] InnoDB: Using CPU crc32 instructions
2016-01-08 13:19:24 6973 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2016-01-08 13:19:24 6973 [Note] InnoDB: Completed initialization of buffer pool
2016-01-08 13:19:24 6973 [Note] InnoDB: Highest supported file format is Barracuda.
2016-01-08 13:19:24 6973 [Note] InnoDB: The log sequence numbers 53348439582 and 53348439582 in ibdata files do not match the log sequence number 53358267557 in the ib_logfiles!
2016-01-08 13:19:24 6973 [Note] InnoDB: Database was not shutdown normally!
2016-01-08 13:19:24 6973 [Note] InnoDB: Starting crash recovery.
2016-01-08 13:19:24 6973 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-01-08 13:19:24 6973 [Note] InnoDB: Restoring possible half-written data pages
2016-01-08 13:19:24 6973 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 22857733, file name ./mysql-bin.000024
2016-01-08 13:19:24 6973 [ERROR] InnoDB: Table ******/field_data_og_group_ref in the InnoDB data dictionary has tablespace id 32634, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:24 6973 [ERROR] InnoDB: Table ******/field_revision_og_group_ref in the InnoDB data dictionary has tablespace id 32635, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:24 6973 [ERROR] InnoDB: Table mysql/gtid_slave_pos in the InnoDB data dictionary has tablespace id 32621, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:24 7fe5616f3780 InnoDB: Error: table 'mysql/innodb_index_stats'
InnoDB: in InnoDB data dictionary has tablespace id 32620,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mysql/innodb_index_stats and id 32646, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:24 7fe5616f3780 InnoDB: Error: table 'mysql/innodb_table_stats'
InnoDB: in InnoDB data dictionary has tablespace id 32619,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mysql/innodb_table_stats and id 32645, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
2016-01-08 13:19:24 6973 [Note] InnoDB: 128 rollback segment(s) are active.
2016-01-08 13:19:24 6973 [Note] InnoDB: Waiting for purge to start
2016-01-08 13:19:24 6973 [Note] InnoDB: 5.6.27 started; log sequence number 53358267557
2016-01-08 13:19:24 6973 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2016-01-08 13:19:24 6973 [Note] - '127.0.0.1' resolves to '127.0.0.1';
2016-01-08 13:19:24 6973 [Note] Server socket created on IP: '127.0.0.1'.
2016-01-08 13:19:24 6973 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'
More info. In other stackexchange questions, people suggested to start mysql with
sudo mysqld --skip-grant-tables
Doing this gives
2016-01-08 20:09:03 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-01-08 20:09:03 0 [Note] mysqld (mysqld 5.6.27-0ubuntu0.14.04.1) starting as process 5364 ...
.. and unrecoverably hijacks the cli window (let's call it "shell A"). Ctrl-C has no effect. I have to open a new window to do anything meaningful.
In a new cli ("shell B"), I try again
sudo service mysql restart
And see, it works!
stop: Unknown instance:
mysql start/running, process 5442
Now I kill the shell A that was hijacked by sudo mysqld --skip-grant-tables.. and try again the restart in shell B.
sudo service mysql restart
For a while, nothing happens. Then, after some waiting, I get
mysql stop/waiting
mysql start/running, process 5748
So: It seems the sudo mysqld --skip-grant-tables has magically fixed the problem. I think this is reproducible, remembering that last time it was the same.
But I don't really understand why.
