Bug #199 Innodb autoincrement stats los on restart
Submitted: 27 Mar 2003 7:08 Modified: 6 Oct 2017 16:40
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.0, 5.1, 5.5 OS:Any (all)
Assigned to: Sunny Bains CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[27 Mar 2003 7:08] Peter Zaitsev
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.
[27 Mar 2003 7:16] Guilhem Bichot
Just a note to say it cannot affect replication, as used autoincrement values are written to the binlog (SET INSERT_ID=).
[22 Apr 2003 10:37] Heikki Tuuri
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.
[26 Apr 2003 7:25] Michael Widenius
I have now documented this behaviour in the MySQL manual
[25 Jun 2008 12:46] Matt Grogan
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
[24 Oct 2008 14:31] Todor Dragnev
Today, our dev team have the same problem...

mysql v5.0.22 InnoDB

So we decides to switch on PostreSQL for production...
[9 Apr 2009 20:40] Thomas McGuire
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?
[16 Jun 2009 10:11] bodri bodri
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!?
[9 Feb 2012 22:56] Boyd Hemphill
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
[10 Feb 2012 5:05] Valeriy Kravchuk
Bug #64287 was marked as a duplicate of this one. 

Maybe it's time to implement what was planned back in 2003?
[1 Jun 2012 13:34] Per Lindberg
This is a very serious and nasty bug, with profound consequences. It has recently cost me an immense amount of trouble (including loss of data). Please set a target version and fix it asap!
[16 Aug 2012 22:04] Alfred Wu
Why don't you fix this easy bug for so long time? Can somebody give any feedback to this issue?
[1 May 2013 14:16] Stephen Blackstone
This "bug" fundamentally breaks basic assumptions about how AUTO_INCREMENT generally works - it is inexcusable that this behavior has been around since 2003.  

Persist the value on disk!
[12 Jun 2014 15:59] zhang simon
persist autoinc on cluster index root page trx_id

Attachment: autoinc_persistent.patch (application/octet-stream, text), 22.92 KiB.

[12 Jun 2014 23:07] Sunny Bains
Simon,

Thanks for the patch. If you haven't already signed the OCA, here is a link to get you started: https://wikis.oracle.com/display/mysq/Contributing+Code+to+MySQL

Regards,
-sunny
[13 Jun 2014 7:05] Daniël van Eeden
There is a minor typo in the patch: s/defualt/default/

+  PLUGIN_VAR_RQCMDARG,
+  "the interval of persist max auto increment value,(defualt:1).",
+  NULL, NULL, 1, 1, 10000, 0);
[26 Nov 2014 5:56] zhang simon
persist auto inc value on root page,this diff is based on 5.6.16

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: auto_inc_persistent.diff (application/octet-stream, text), 32.42 KiB.

[8 May 2015 12:19] Dmitry Stepanov
Faced with the same problem in MySQL 5.0.27 and 5.1.47 InnoDB.

In which MySQL version the problem has been solved? 5.6.16?
[10 Dec 2015 13:53] Andreas Höhne
just ran into the same problems...
in my scenario i got a archive table, that shouldn't have same id's then my original table.
if all you have to do is to get sure the auto_increment value of table1 is equal or higher than worth of table2, just use following stored procedure before your first insert.
too bad we dont even have a chance to run this code in a trigger :/

CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))
BEGIN
	DECLARE increment1 INT(11);
    DECLARE increment2 INT(11);
	set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');
    prepare stmt from @qry;
	execute stmt;
    set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');
    prepare stmt from @qry;
	execute stmt;
	IF @max1 > @max THEN 
		set @qry = concat('alter table `',table1,'` auto_increment=',@max1);
		prepare stmt from @qry;
        execute stmt;
		SELECT 'updated' as `status`;
	else
		SELECT 'no update needed' as `status`;
	END IF;
END

CALL auto_increment_fromtable2('table_to_be_altered', 'table_to_get_auto_increment_from');
[5 Feb 2016 14:15] james wang
Sorry, I meant the issue still exist in 5.7
[5 Feb 2016 15:19] james wang
5.5.5-10.1.11-MariaDB behaves the same
[3 Mar 2016 16:40] Kendall Bennett
We have just run into this same issue, and it has caused havoc on production databases for us well. The issue for us is similar to those above, where we are archiving data and do not expect primary keys to get reused in the primary database since it exists in the archive database. However if MySQL is restarted (it was recently as we did some server VM changes) then we end up with errors where we cannot archive entries due to duplicate keys in the primary DB!

This needs to be fixed and should be considered a very severe issue in MySQL. There is literally nothing we can do to resolve this in our own code.
[15 Apr 2016 1:34] James Day
We are planning to change this to keep the highest value persistently but we don't yet have a version number to announce for when this is expected to happen.

The workaround of using sequences remains possibly the best approach today. One of the many example implementations of this is available at https://openquery.com.au/blog/implementing-sequences-using-a-stored-function-and-triggers .

James Day, MySQL Senior Principal Support Engineer, Oracle
[15 Apr 2016 11:03] burak mert intepe
We have the same problem/bug with mysql 5.6.17
[30 Sep 2016 20:02] James Day
The first 8.0 development milestone release includes the work to make the autoincrement value persistent, writing changes to the redo log.

James day, MySQL Senior Principal Support engineer, Oracle
[6 Oct 2017 14:16] Bin Su
This bug has been fixed by "WL#6204 - InnoDB persistent max value for autoinc columns".
[6 Oct 2017 16:40] Daniel Price
Posted by developer:
 
Fixed in 8.0.0 by WL#6204.

Thank you for the bug report.