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 19:39	Modified:	29 Apr 20:44
Reporter:	Olaf van der Spek (Basic Quality Contributor)
Status:	Can't repeat
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	Any
Assigned to:		Target Version:
Tags:	qc, corruption

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.