mysql Innodb – Table ‘user’ is marked as crashed and should be repaired


I recently came across a Cpanel server (CentOS 5) upon which mysqld refused to start after /var was at 100%. After tailing the mysql error log in the default /var/lib/mysql/HOSTNAME.err it was no surprise to find that the mysql user table had been marked as crashed. On RHEL/CentOS servers, you cannot simply add the “innodb_force_recovery = 1″ (or whatever recovery level…2,3,4,5,6) to the /etc/my.cnf and do the regular service mysql start. You’ll have to edit the my.cnf to enable recovery and start mysql from the command line and not by the init script/service command. Only once you have mysql started on the command line can you run your repair on the mysql user table. Here is quick run-down with commands and queries to run on your Cpanel server;

[root@HOSTNAME mysql]# tail -f /var/lib/mysql/HOSTNAME.err
110108 10:37:45  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 1 3749263016.
InnoDB: Doing recovery: scanned up to log sequence number 1 3749263050
InnoDB: Last MySQL binlog file position 0 79, file name ./host89-bin.000005
110108 10:37:45  InnoDB: Flushing modified pages from the buffer pool...
110108 10:37:45  InnoDB: Started; log sequence number 1 3749263050
InnoDB: !!! innodb_force_recovery is set to 1 !!!
110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed and should be repaired
110108 10:37:45  mysqld ended

Now, go ahead and enable innodb forced recovery by opening your /etc/my.cnf in your favorite text editor and make sure you have something like this:

[mysqld]
innodb_force_recovery = 1

Start mysql from the command line after enabling innodb forced recovery:

[root@HOSTNAME ~]#/usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/HOSTNAME.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock

Finally, it’s time to get back in to mysql and get your life back… or mysql user table at least!

[root@HOSTNAME ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 173 to server version: 4.1.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
mysql> check table user;
+------------+-------+----------+----------------------------------------------------------+
| Table      | Op    | Msg_type | Msg_text                                                 |
+------------+-------+----------+----------------------------------------------------------+
| mysql.user | check | warning  | Table is marked as crashed                               |
| mysql.user | check | warning  | 6 clients are using or haven't closed the table properly |
| mysql.user | check | error    | Record at pos: 24992 is not remove-marked                |
| mysql.user | check | error    | record delete-link-chain corrupted                       |
| mysql.user | check | error    | Corrupt                                                  |
+------------+-------+----------+----------------------------------------------------------+
5 rows in set (0.02 sec)

mysql> repair table user;
+------------+--------+----------+------------------------------------------+
| Table      | Op     | Msg_type | Msg_text                                 |
+------------+--------+----------+------------------------------------------+
| mysql.user | repair | warning  | Number of rows changed from 1384 to 1385 |
| mysql.user | repair | status   | OK                                       |
+------------+--------+----------+------------------------------------------+
2 rows in set (0.48 sec)

mysql> check table user;
+------------+-------+----------+----------+
| Table      | Op    | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.user | check | status   | OK       |
+------------+-------+----------+----------+
1 row in set (0.01 sec)

mysql>exit

Now, don’t forget to REMOVE the innodb_force_recovery line from your my.cnf you added earlier! After that, just start mysql as you normally would.

[root@HOSTNAME mysql]# service mysql start
  1. #1 by akarat on March 30, 2011 - 4:21 am

    Thank you for your post. we are repair the mysql successfull.
    art
    Thailand

    • #2 by admin on April 15, 2011 - 10:27 am

      You are very welcome. I’m glad to have helped you out.

  2. #3 by Vinsente on April 19, 2012 - 3:21 am

    Unpleasant problem. Solved on Windows using this:

    – (1) starting server workaround
    mysqld –console –skip-grant-tables –skip-external-locking

    – (2) fixing problem
    mysqlcheck –repair mysql user

    – (3) stop server and start it normally

  3. #4 by Anandbabu on January 16, 2013 - 10:56 pm

    Thankyou, you are save my time and money…

(will not be published)