MySQL Bugs: #10405: myisamchk damages auto_increment columns when changing character set

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

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

Verified on linux, does affect 4.1 and 5.0, too

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.

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.

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

My test script.

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

First review done by Ramil Kalimullin by email.

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.

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)