Bug #10405 myisamchk damages auto_increment columns when changing character set
Submitted: 6 May 2005 10:06 Modified: 8 May 2006 18:27
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:4.0.24 OS:Linux (Linux)
Assigned to: Ingo Strüwing CPU Architecture:Any

[6 May 2005 10:06] Axel Schwenke
Description:
When changing the character set of a 4.0 MySQL server, one has to rebuild all indexes on MyISAM tables by running 'myisamchk --set-character-set' . However, after this AUTO_INCREMENT columns stop working as expected. Seems myisamchk somehow damages the AUTO_INCREMENT information in the .MYI file.

There is a workaround: after changing the character set one has to do ALTER TABLE and activate the AUTO_INCREMENT option again for all affected columns.

How to repeat:
mysql> create table foo (bar int primary key auto_increment);
mysql> insert into foo values (null); select * from foo;
+-----+
| bar |
+-----+
|   1 |
+-----+

~ $myisamchk --recover --quick --set-character-set=latin1_de /usr/local/mysql/current/var/test/foo.MYI

mysql> insert into foo values (null); select * from foo;
+-----+
| bar |
+-----+
|   1 |
|   2 |
+-----+

mysql> insert into foo values (null); select * from foo;
ERROR 1062: Duplicate entry '2' for key 1

What happened to our AUTO_INDEX column? Mysqld claims it to still be there:

mysql> desc foo;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| bar   | int(11) |      | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

However:
~ $myisamchk --set-auto-increment /usr/local/mysql/current/var/test/foo.MYI
Table: /usr/local/mysql/current/var/test/foo.MYI doesn't have an auto increment key
[6 May 2005 14:54] Hartmut Holzgraefe
Verified on linux, does affect 4.1 and 5.0, too
[7 May 2005 12:16] Axel Schwenke
I could also verify this with 5.0.6. However with 4.1 and above one has to use 'myisamchk --set-collation' to trigger this bug. The workaround is then to set the collation via ALTER TABLE.
[10 Jan 2006 16:28] Benjamin Pflugmann
Reproduced with MySQL 4.0.24 (Debian-10ubuntu2-log).

It seems
ALTER TABLE AUTO_INCREMENT=x; 
rewrites the table, so for a small to medium database an easier way is "mysqldump --opt" and reloading the dump (especially, if you know about the issue, you can skip the myisamchk part).

Nevertheless, it would be nice to see some progress on this issue.
[10 Apr 2006 11:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4701
[10 Apr 2006 16:54] Ingo Strüwing
My test script.

Attachment: bug10405-1.sh (application/x-sh, text), 2.99 KiB.

[28 Apr 2006 16:35] Ingo Strüwing
First review done by Ramil Kalimullin by email.
[6 May 2006 15:31] Ingo Strüwing
Whenever 'myisamchk' needed to recreate a table,
  the auto increment information was lost.
  
  Now the forgotten element of the table creation
  information is set correctly.

Pushed to 4.0.27, 4.1.20, 5.0.22, and 5.1.10.
[8 May 2006 18:27] Paul Dubois
Noted in 4.0.27, 4.1.20, 5.0.22, 5.1.10 changelogs.

When <command>myisamchk</command> needed to rebuild a
table, <literal>AUTO_INCREMENT</literal> information was lost.
(Bug #10405)