MySQL Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'

Question

MySQL (Percona 5.6) will not start.

This error has happened to me several times. Each time, I have had to remove MySQL data directories and reinstall MySQL.

Is there another way to fix MySQL? (Specifically one in which the data not blown away?)

/var/log/mysql/error.log

150214 16:36:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-02-14 16:36:40 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2015-02-14 16:36:40 0 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
2015-02-14 16:36:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-02-14 16:36:40 1018 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2015-02-14 16:36:40 1018 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Incorrect file format 'plugin'
2015-02-14 16:36:40 1018 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2015-02-14 16:36:40 1018 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-02-14 16:36:40 1018 [Note] InnoDB: The InnoDB memory heap is disabled
2015-02-14 16:36:40 1018 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-02-14 16:36:40 1018 [Note] InnoDB: Memory barrier is not used
2015-02-14 16:36:40 1018 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-02-14 16:36:40 1018 [Note] InnoDB: Using Linux native AIO
2015-02-14 16:36:40 1018 [Note] InnoDB: Using CPU crc32 instructions
2015-02-14 16:36:40 1018 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2015-02-14 16:36:40 1018 [Note] InnoDB: Completed initialization of buffer pool
2015-02-14 16:36:40 1018 [Note] InnoDB: Highest supported file format is Barracuda.
2015-02-14 16:36:40 1018 [Note] InnoDB: The log sequence numbers 714340126 and 714340126 in ibdata files do not match the log sequence number 716513090 in the ib_logfiles!
2015-02-14 16:36:40 1018 [Note] InnoDB: Database was not shutdown normally!
2015-02-14 16:36:40 1018 [Note] InnoDB: Starting crash recovery.
2015-02-14 16:36:40 1018 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-02-14 16:36:40 1018 [Note] InnoDB: Restoring possible half-written data pages 
2015-02-14 16:36:40 1018 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 292596, file name binlog.000056
2015-02-14 16:36:40 1018 [Note] InnoDB: 128 rollback segment(s) are active.
2015-02-14 16:36:40 1018 [Note] InnoDB: Waiting for purge to start
2015-02-14 16:36:40 1018 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.21-70.1 started; log sequence number 716513090
2015-02-14 16:36:40 1018 [Note] Recovering after a crash using binlog
2015-02-14 16:36:40 1018 [Note] Starting crash recovery...
2015-02-14 16:36:40 1018 [Note] Crash recovery finished.
2015-02-14 16:36:40 1018 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-02-14 16:36:40 1018 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-02-14 16:36:40 1018 [Note] Server hostname (bind-address): '*'; port: 3306
2015-02-14 16:36:40 1018 [Note] IPv6 is available.
2015-02-14 16:36:40 1018 [Note]   - '::' resolves to '::';
2015-02-14 16:36:40 1018 [Note] Server socket created on IP: '::'.
2015-02-14 16:36:40 1018 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'user'
150214 16:36:40 mysqld_safe mysqld from pid file /tmp/mysqld.pid ended

Psittacosis

you can try launching mysql_upgrade (as suggested in the logs); or eventually mysql_install_db – Uniformed

You probably have something messing up with your package... Do you have by chance something that put back normal MySQL (vs percona?) and then back to percona? When you get it running do a backup of your "mysql" database you should be able to put it back if that's the case. – Ruckus

Answer 1

Thanks to John for putting me on the right track, I had a few other hoops to jump through on my system. Hope this helps someone.

This is a corrupt privilege table. Maybe caused by an upgrade or power failure. My system OpenSUSE 13.2, MySQL 5.6. Simple reinstall does not fix, must delete all traces of MySQL before reinstall OR…

Close down all instances of MySQL

$ systemctl stop mysql.service
$ pkill -9 mysqld

Start server bypassing privilege system

$ sudo mysqld_safe --user=root --skip-grant-tables

Start MySQL command line tool

$ mysql

If you receive

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

mysqld_safe is running with its socket somewhere else. Find it with.

$ sudo find / -type s

Mine was in /var/run/mysql/ Edit the socket line in my.cnf, making a note of your existing socket setting. My line became

socket=/var/run/mysql/mysql.sock

Return to 'Close down all instances of MySQL' (the top of these instructions). Follow them through to 'Start MySQL command line tool'. Hopefully you can open mysql successfully. From mysql command line.

mysql> use mysql
mysql> repair table user use_frm;
mysql> exit

Close down all instances of MySQL

$ systemctl stop mysql.service
$ pkill -9 mysqld

Re-edit my.cnf, returning the socket line to its original setting.

I had to reset permissions on 2 files in my mysql data directory.

$ chown mysql:mysql server2.err
$ chown mysql:mysql server2.pid

Start the MySQL server

$ systemctl start mysql.service

I then got the same original error with another table (db)

[ERROR] Fatal error: Can't open and lock privilege  tables: Incorrect file format 'db'

And had to repeat the above procedure multiple times altering the 'repair' command until all privilege tables were fixed.

mysql> repair table db use_frm;

Opossum

mysqld_safe --user=root --skip-grant-tables saved my life!! – Davie

Thanks heaps for this answer! I also had to keep going for 'host' table too but I got there with this :) – Acreage

Answer 2

I managed so solve this problem with answer from ruby.b

You'll have to repair your host table. To do this, issue the following command to start your server bypassing the privilege system

In one terminal, run

$ sudo mysqld --skip-grant-tables

Open another terminal and execute these commands

$ mysql
mysql> use mysql
mysql> repair table host use_frm;
mysql> exit

And restart the mysql service

$ sudo service mysql restart

Speculum

thank you very much. in my case i had to fix the user table instead of the host table so i did: repair table user use_frm; instead and it worked! – Driest

Thank you so much for this solution. It worked for me for my phpMyAdmin usin xampp. – Butterflies

You have to use the name of the table that is giving you errors (see in logs) instead of host. That worked for me. Example: repair table db use_frm; for me. – Amadoamador

Answer 3

Here is the setup for @John Linhart's answer, if you're running Docker:

First, start a new docker container with the appropriate tag from the mysql-container (the same you used to write the DB with).

$ docker run --rm -it -v <named_volume>:/var/lib/mysql mysql:<tag> /bin/bash

This will launch a new container with the correct named volume (or mounted volume) mounted in the container and drop you into a shell as root. The mysqld-Daemon will refuse to launch as root though, so we'll run it as the mysql-user:

$ whoami
root
$ which mysqld
/usr/sbin/mysqld
$ su mysql
$ whoami
mysql
$ /usr/sbin/mysqld --skip-grant-tables
....

Now to run the SQL commands, we'll connect to the running container from a new terminal:

$ docker ps 
CONTAINER ID [...]
abc123 [...]
$ docker exec -it abc123 /bin/bash
# We're on the container now!
$ whoami
root
$ mysql
...

And continue from there. When you're done, leave the container on the second terminal via exit. The terminal running mysqld will not respond to CMD+C, so we'll stop the container via Docker:

$ docker ps
CONTAINER ID [...]
abc123 [...]
$ docker stop abc123

Agitato