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

Bug #25262	Auto Increment lost when changing Engine type
Submitted:	24 Dec 2006 2:08	Modified:	4 May 2007 18:02
Reporter:	Yael Goldberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.34-BK, 4.1.14	OS:	Linux (Linux Red Hat)
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

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.)