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 12:06	Modified:	8 May 2006 20:27
Reporter:	Axel Schwenke
Status:	Closed
Category:	Client	Severity:	S2 (Serious)
Version:	4.0.24	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	Target Version:

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)