MySQL Bugs: #199: Innodb autoincrement stats los on restart

Bug #199	Innodb autoincrement stats los on restart
Submitted:	27 Mar 2003 7:08	Modified:	27 Jun 2011 16:18
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Verified
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	4.0, 5.1, 5.5	OS:	Any (all)
Assigned to:	Sunny Bains	Target Version:
Triage:	Needs Triage: D5 (Feature request)

Description:
Innodb autoincrement counter is lost on restart which is very confusing as 
after server restart server starts to get different auto_increment values than 
if it was not restarted.

This can possibly result in replication breakage.

How to repeat:
mysql> create table a(id int unsigned not null primary key auto_increment) type=innodb;
Query OK, 0 rows affected (4.81 sec)

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

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

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

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

mysql> delete from a where id=3;
Query OK, 1 row affected (0.58 sec)

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

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

mysql> delete from a where id=4;
Query OK, 1 row affected (0.00 sec)

<Restart MySQL server now>
mysql> select * from a;
ERROR 2006: MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (1.12 sec)

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

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

As you can see value 3 is reused.

Just a note to say it cannot affect replication, as used autoincrement values are written to the binlog (SET INSERT_ID=).

Does no break replication as the binlog contains the auto-inc column value for each new insert / replace.

This behavior is a documented feature which will be fixed in the future at the same time a fast COUNT(*) is introduced to InnoDB.

I have now documented this behaviour in the MySQL manual

This is a severe flaw. 

Scenario:
Table t1 has an auto-inc PK.
Table t2 has a FK for the PK in t1.
However the FK does not have a foreign key "constraint".
This means when a row is deleted in t1 the correspondong rows in t2 are orphaned. This is fine in a garbage collection scenario. The auto-inc "guarentees" that old rows in t2 will never be referenced because the id for the deleted row is never re-used. A garbage-collection process can at some later date cleanup the orphaned rows in t2.

However with INNODB tables an already issued id *can* be re-issued after a server restart. Therefore orphaned rows in t2 can be spuriously linked to new rows in t1.

This bug has just been discovered in a production database and is causing havoc. There is no clear to solution to this problem.

Can you explain why the status of this bug is "won't fix" and can you suggest a solution to this problem?

Thanks,
Matt

Today, our dev team have the same problem...

mysql v5.0.22 InnoDB

So we decides to switch on PostreSQL for production...

We are having the same problem in KDE with the Akonadi project, which currently uses MySQL as a backend.

We are storing PIM items (mails, contacts etc) in the database, and we've relied on the assumption of unique IDs so far.

I'm afraid this bug will cause corruption of the user's data, and it is possible to loose e.g. mail messages with it.

Why is this closed as Won't fix? Can't the autoincrement stats be stored somewhere and be restored when MySQL restarts?

in our cluster we have manual async replication which relies on the rule:

"Once an object is deleted it should not be possible to create a new object with the same id."

Using MySQL for the persistence breaks the above rule when mysqld is restarted.

I think this "feature" should be discarded or at least made optional, because there are too many use-cases and designs which can be broken by simply restarting mysqld!?

Here is a simple command line test case for this bug.  I am really ashamed of MySQL for allowing this "feature" to exist this long.  I sincerely hope it will be addressed!

MySQL 5.1.41

boyd@stream:~ 20:47:15 >mysql -sse "show table status like 'auth_user'"
auth_user	InnoDB	10	Compact	6	2730	16384	0	16384	0	7	2012-02-09 20:40:44	NULL	NULL	utf8_unicode_ci	NULL		
boyd@stream:~ 20:48:00 >mysql -sse "alter table auth_user auto_increment = 1000"
boyd@stream:~ 20:48:10 >mysql -sse "show table status like 'auth_user'"
auth_user	InnoDB	10	Compact	6	2730	16384	0	16384	0	1000	2012-02-09 20:48:10	NULL	NULL	utf8_unicode_ci	NULL		
boyd@stream:~ 20:48:13 >sudo restart mysql
mysql start/running, process 333
boyd@stream:~ 20:48:27 >mysql -sse "show table status like 'auth_user'"
auth_user	InnoDB	10	Compact	6	2730	16384	0	16384	0	7	2012-02-09 20:48:10	NULL	NULL	utf8_unicode_ci	NULL

Bug #64287 was marked as a duplicate of this one. 

Maybe it's time to implement what was planned back in 2003?