MySQL Bugs: #25262: Auto Increment lost when changing Engine type

Bug #25262	Auto Increment lost when changing Engine type
Submitted:	24 Dec 2006 3:08	Modified:	4 May 2007 20:02
Reporter:	Yael Goldberg
Status:	Closed
Category:	Server: General	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 4.1.14	OS:	Linux (Linux Red Hat)
Assigned to:	Magnus Blaudd	Target Version:

Description:
Here is a very basic example of how the auto increment is lost when changing the engine
type:

create table foo
(id int(8) primary key auto_increment)
engine = innodb;

insert into foo values (null);
insert into foo values (null);

alter table foo auto_increment = 50;

alter table foo engine = myisam;

insert into foo values (null);

select max(id) from foo;

This bug happens when converting table either to InnoDB or MyISAM. I also tried this on
MySQL version 5.0.27. I did not try other storage engines.

In my scenario this is a problem because I am getting a primary key error.  The reason for
this is because I have a daily InnoDB table where I insert data for the current day.  At
night I have a cron that inserts the daily data into a merge table with underlying monthly
tables.  These monthly tables are MyISAM.  Because of another MySQL problem (there is an
open ticket), our monthly MyISAM tables were constantly getting corrupt when this cron ran
and inserted the data from the InnoDB table into the merge table.  Our temporary solution
was to alter the daily InnoDB table to MyISAM before running the cron, (and also inserting
directly into the underlying table), and then alter the daily table back to InnoDB after
running the cron.  However, after the cron moves the data from the daily table to the
monthly table, it deletes almost all of the rows from the daily table, so the max(id) in
the table is not equal to auto-increment-1.  Then, when I alter the table back to Innodb
the auto-increment is reset to max(id) instead of the saved auto-increment.  When new data
is inserted into the table the next day - it takes an old primary key.  Then, when the
cron runs the following night it fails on duplicate key error - since that primary key
already exists in the monthly table.

How to repeat:
Here is a very basic example of how the auto increment is lost when changing the engine
type:

create table foo
(id int(8) primary key auto_increment)
engine = innodb;

insert into foo values (null);
insert into foo values (null);

alter table foo auto_increment = 50;

alter table foo engine = myisam;

insert into foo values (null);

select max(id) from foo;

This bug happens when converting table either to InnoDB or MyISAM. I also tried this on
MySQL version 5.0.27. I did not try other storage engines.

Suggested fix:
The auto-increment should be backed up when converting a table to another storage engine. 
After the convert runs, MySQL should automatically run the - 

alter table XXX auto_increment = X;

Thank you for a bug report. Verified just as described with 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

droWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-debug Source distribution

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

mysql> drop table foo;
Query OK, 0 rows affected (0.01 sec)

mysql> create table foo
    -> (id int(8) primary key auto_increment)
    -> engine = innodb;

Query OK, 0 rows affected (0.05 sec)

mysql> insert into foo values (null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into foo values (null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> alter table foo auto_increment = 50;
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table foo engine = myisam;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into foo values (null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

This is either a bug or a request for explicit description of this "feature" in
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html.

I supposed expected output is
mysql> select * from foo;
1
2
50

The autoincrement value is not propagated(it's 0) to ha_myisam::create when the myisam
table is created.

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/20891

ChangeSet@1.2407, 2007-03-01 13:43:04+01:00, msvensson@pilot.blaudden +3 -0
  Bug#25262 Auto Increment lost when changing Engine type
   - Try to copy the autoincrement value when altering the table

Pushed into 5.1.18-beta

Pushed into 5.0.42

Some engines support setting the current autoincrement value with a "ALTER TABLE t
AUTO_INCREMENT=<new_autoinc_value>". When a table in an engine that supports "the above"
is altered to another engine which supports "the above" the autoincrement value is
copied.

The fact that setting of auto_increment on a table that does not support it does not
produce and error should maybe be registered as a new bug - although the manual says that
only MYISAM(from 5.0), MEMORY(from 5.0) and INNODB(from 5.0.3) does.

Noted in 5.0.42, 5.1.18 changelogs.

For storage engines that allow the current auto-increment value to be
set, using ALTER TABLE ... ENGINE to convert a table from one such
storage engine to another caused loss of the current value. (For
storage engines that do not support setting the value, it cannot be
retained anyway when changing the storage engine.)