MySQL Bugs: #28602: Can't open and lock privilege tables: Incorrect key file for table 'db'; try to

Bug #28602	Can't open and lock privilege tables: Incorrect key file for table 'db'; try to
Submitted:	22 May 2007 17:39	Modified:	29 Apr 2009 18:44
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	corruption, qc

Description:
Hi,

After an upgrade from 5.1.17 to 5.1.18, the server no longer wants to start, see the following log. Checking and repairing the table don't seem to work.

>mysqld-nt --console
070522 19:36:35 [Warning] Server variable data_file_path of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:35 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:35 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:35 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:35 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:36  InnoDB: Started; log sequence number 0 80152
070522 19:36:36 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect key file for table 'db'; try to repair it

>myisamchk.exe -e ..\data\mysql\db
Checking MyISAM file: ..\data\mysql\db
Data records:      11   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check records and index references

>myisamchk.exe -r ..\data\mysql\db
- recovering (with sort) MyISAM-table '..\data\mysql\db'
Data records: 11
- Fixing index 1
- Fixing index 2

>mysqld-nt --console
070522 19:36:51 [Warning] Server variable data_file_path of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:51 [Warning] Server variable data_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:51 [Warning] Server variable flush_method of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:51 [Warning] Server variable log_arch_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:51 [Warning] Server variable log_group_home_dir of plugin InnoDB was forced to be read-only: string variable without update_func and PLUGIN_VAR_MEMALLOC flag
070522 19:36:51  InnoDB: Started; log sequence number 0 80152
070522 19:36:51 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect key file for table 'db'; try to repair it

>

How to repeat:
-

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for the mysql.db table.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1406
Server version: 6.0.0-alpha-community-nt-debug MySQL Community Server (GPL)

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

mysql> use b;
Database changed
mysql> show create table db;
ERROR 1034 (HY000): Incorrect key file for table 'db'; try to repair it
mysql> repair table db;
+-------+--------+----------+-----------------------------------------------------+
| Table | Op     | Msg_type | Msg_text                                            |
+-------+--------+----------+-----------------------------------------------------+
| b.db  | repair | error    | Incorrect key file for table 'db'; try to repair it |
+-------+--------+----------+-----------------------------------------------------+
1 row in set, 1 warning (0.03 sec)

mysql> show table status like 'db';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+-----------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment                                             |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+-----------------------------------------------------+
| db   | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | Incorrect key file for table 'db'; try to repair it |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql>

Have you tried to run myisamchk on that table? Can you just upload db.* files to this report?

I've upgraded from 5.1.16 to 5.1.18 some time ago on Windows XP and had not got any problems like you described.

C:\Program Files\MySQL\MySQL Server 5.1\data\test>..\..\bin\myisamchk.exe db
Checking MyISAM file: db
Data records:      11   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2

Unrepairable table

Attachment: db.zip (application/zip, text), 1.36 KiB.

Hi - since Mysql 5.1.18 and higher i also get this error. Try to repair, etc. but nothing helped. I made a fresh installation and the mysql did not start.

After removing the STRICT MODE in my.ini the server started but some databases seems to be coruppted. i switched back to 5.1.17 and all worked fine.

This doesn't only occur on Windows.  We have a Linux (Fedora Core 3, using glibc23 i386 rpm version) with 5.1.19 and on upgrade (from 5.1.17) we had exactly the same issue with the mysql database.

The only way I was able to resolve it was to stop the server, copy the mysql database files from a working server and restarting.

Since restart we have had an issue with a table in another database of exactly the same type.  This occurred after a few days of running, with little or no activity (the server is for staging and only gets spasmodic use).   The table reported the Incorrect key file message, and all attempts at recovery failed.  CHECK TABLE returned the same error, stopping the server, using myisamchk and restarting the server still showed the same issue.  The only way to recover was to drop the table and re-create it. Both -r and -o were tried with myisamchk.

Other information:  The server was upgraded from 5.1.17 to 5.1.19, no issues were seen before the upgrade.  The server is configured to replicate from a master, but is set up with skip-slave-start in my.cnf so does not replicate.

Other servers using 5.1.19 are also showing problems, although in all cases so far have been recoverable using either CHECK TABLE, or REPAIR TABLE. These are all slaves replicating from the same master. One of these was upgraded from 5.1.18, the other from 5.0.41.  The master is also running 5.1.19 and was upgraded from 5.0.41 but has so far not shown any issues of this sort.

One thing I forgot.  I also ran mysqlcheck on all databases, and it not only didn't check or repair the table, it didn't even list the table or try to check or repair it.  Not sure if that is significant or not.

Olaf,

Please, try to repeat with a newer version, 5.1.20, we had just released, and inform about the results.

> Please, try to repeat with a newer version, 5.1.20, we had just released, and inform about the results.

Why? Does 5.1.20 contain a fix? Are you not able to reproduce it with 5.1.20?

5.1.20 results in the same output for me.

I am experiencing a similar problem; it appears to be related to OS IO or char-encoding libs. See bottom of:
http://bugs.mysql.com/bug.php?id=31331&thanks=3&notify=711

Looks like either the table was originally created in some older (4.1.x?) version, or you were affected by the change introduced in 5.1.18 with the "improved fix" for bug #21432 (related to utf8 encoding of data). As a result, table has to be rebuilt.

In current 5.1.34 it can be just repaired with USE_FRM option:

...
Server version: 5.1.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables like 'd%';
+---------------------+
| Tables_in_test (d%) |
+---------------------+
| dates               | 
| dates2              | 
| db                  | 
+---------------------+
3 rows in set (0.01 sec)

mysql> show create table db\G
ERROR 1034 (HY000): Incorrect key file for table 'db'; try to repair it
mysql> repair table db;
+---------+--------+----------+-----------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                            |
+---------+--------+----------+-----------------------------------------------------+
| test.db | repair | Error    | Incorrect key file for table 'db'; try to repair it | 
| test.db | repair | error    | Corrupt                                             | 
+---------+--------+----------+-----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> repair table db use_frm; 
+---------+--------+----------+--------------------------------------------------------+
| Table   | Op     | Msg_type | Msg_text                                               |
+---------+--------+----------+--------------------------------------------------------+
| test.db | repair | warning  | Duplicate key for record at 876 against record at 438  | 
| test.db | repair | warning  | Duplicate key for record at 1314 against record at 438 | 
| test.db | repair | warning  | Duplicate key for record at 1752 against record at 438 | 
| test.db | repair | warning  | Duplicate key for record at 2190 against record at 438 | 
| test.db | repair | warning  | Duplicate key for record at 2628 against record at 438 | 
| test.db | repair | warning  | Number of rows changed from 0 to 5                     | 
| test.db | repair | status   | OK                                                     | 
+---------+--------+----------+--------------------------------------------------------+
7 rows in set (0.00 sec)

So, looks like in 5.1.18 and up content of user column (see below) wass considered equal, hence the messages about incorrect (primary) key.

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

mysql> select host, db, user from db;
+------+-----+------------------+
| host | db  | user             |
+------+-----+------------------+
|      |     |    ? | 
|      |     |          | 
|      |     |               | 
|      |     |                  | 
| %    | xcc | xwis             | 
+------+-----+------------------+
5 rows in set (0.00 sec)

mysql> show create table db\G
*************************** 1. row ***************************
       Table: db
Create Table: CREATE TABLE `db` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`,`User`),
  KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'
1 row in set (0.00 sec)

mysql> show table status like 'db';
+------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+---------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length    | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation | Checksum | Create_options | Comment             |
+------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+---------------------+
| db   | MyISAM |      10 | Fixed      |    5 |            438 |        4380 | 123286039799267327 |         5120 |      2190 |           NULL | 2009-04-29 21:01:51 | 2009-04-29 21:01:51 | 2009-04-29 21:02:05 | utf8_bin  |     NULL |                | Database privileges | 
+------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+---------------------+
1 row in set (0.00 sec)

mysql> select hex(host), hex(db), hex(user) from db;
+-----------+---------+----------------------------------+
| hex(host) | hex(db) | hex(user)                        |
+-----------+---------+----------------------------------+
|           |         | 202020020202020202010202020202FF | 
|           |         | 20202020202020200202020202020102 | 
|           |         | 20202020202020202020202020020202 | 
|           |         |                                  | 
| 25        | 786363  | 78776973                         | 
+-----------+---------+----------------------------------+
5 rows in set (0.00 sec)

Do you agree with my findings?

I don't have this server anymore and can't verify this.

Then, as there is a way to repair the table now, I think "Can't repeat" is more proper state for this case. 

Sorry for a delay. My fault.