1

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.

4
  • Were you upgrading from an older version?
    – Rick James
    Jan 8, 2016 at 2:03
  • I suspect yes. But I don't know where I can find out. And as mentioned, trying mysql_upgrade failed with the above errors.
    – donquixote
    Jan 8, 2016 at 12:16
  • It could be related to force reboots after system freeze.
    – donquixote
    Jan 8, 2016 at 12:17
  • @RickJames I added some information to the question. Seems I can magically fix the problem, but I don't understand what is really happening.
    – donquixote
    Jan 8, 2016 at 19:25

1 Answer 1

1

"Hijacking" -- Note the difference between "mysqld" and "mysql". The former is the server; the latter is the client. Run the server (mysqld) with the skip option; then run the client (mysql) separately.

--skip-grant-tables avoids looking at mysql.user, which is corrupted. Running that way leaves you vulnerable. So suggest follow the rest of the instructions in the manual Resetting the Root Password: Generic Instructions. Note that that ends with restarting mysqld. If that fails, we can look at more drastic measures.

11
  • Ok. I just now realize that with the current instance I can start a mysql cli without a username or password, and create databases. I was not aware that this effect of mysqld --skip-grant-tables continues.. I assumed it would be just for that cli where I typed it :) I will try the instructions and report back here.
    – donquixote
    Jan 8, 2016 at 20:43
  • This said, I don't understand how or why resetting the root password would fix what looks like a warning for corrupt tables or incorrect file format. Even if this happens to work, it does not really explain what is going on.
    – donquixote
    Jan 10, 2016 at 11:59
  • Well, guess what: As part of the fix from dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html: mysql> FLUSH PRIVILEGES; ERROR 130 (HY000): Incorrect file format 'user'. Not really surprising.
    – donquixote
    Jan 10, 2016 at 14:45
  • OK, I'll look at it from a different perspective... What version are you upgrading from?
    – Rick James
    Jan 10, 2016 at 17:31
  • Well, as said, how can I find out? My guess is 5.5 to 5.6, but this is really just a guess. Maybe I don't need to upgrade at all, and the db is just borked. See also dba.stackexchange.com/questions/125810/…
    – donquixote
    Jan 10, 2016 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.