MySQL Bugs: #21641: nonpreserving of InnoDB auto_increment values can lead to unintended inheritance

Bug #21641	nonpreserving of InnoDB auto_increment values can lead to unintended inheritance
Submitted:	14 Aug 2006 23:57	Modified:	13 May 2010 16:04
Reporter:	Stephen Dewey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	4.1, 5.1, 5.5	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	auto_increment, innodb

Description:
Please note: I am aware that what I am describing is the expected behavior. I am submitting a feature request asking that users be allowed to override that behavior, or that it be modified to prevent the problems described below.

The current auto_increment behavior in the InnoDB engine seems to be sub-optimal. As it currently functions, the auto_increment value is stored until the server shuts down or resets, and then is rebuilt based on values in the table when it starts up again.

This could lead to serious security problems in some applications. For example, think of a database setup in which there is a "users" table with information about users, and an autoincremented integer primary key named user_id. There are other tables that contain various information about the users, for example their past purchases, which may have 0 or more records about any given user. Those tables use user_id as a foreign key.

Now, if a user's record in the users table is deleted, and the foreign key settings are not set to CASCADE, then the records in the other tables will exist and be orphaned. For some people this may be the desired behavior, to make repopulating the user's account easier if they ever come back to the site.

However, if a user record in the users table is deleted and the server is then reset before another user record is created, that will set the autoincrement value backwards such that the next user to register will acquire the old user's user_id number, and along with it, all of the orphaned records in the other tables. Most applications (e.g. web profiles) would then immediately display those records to the new user.

Overall, this aspect of InnoDB's auto_increment support presents serious problems for programmers who want to preserve orphaned records.

Also note that a programmer may face this problem even if they do not intend to preserve orphaned records, but have also avoided CASCADE to avoid unintentionally deleting everything. This means that when they do delete one of the records, they will have to immediately follow it up with manually deleting the record's children to avoid this behavior. This in itself could cause problems as they might not remember to do this, and it is an unexpected behavior since other engines don't change their auto_increment values on reset.

Also note that the requirement that a primary key be unique does nothing to address this problem, since these records are in other tables. The behavior would be fine if InnoDB also checked fields in OTHER tables that use the autoincremented column as a foreign key, and incorporated their highest values into its calculations when determining what the auto_increment value should be set to. Unfortunately it does not do that.

How to repeat:
1. Create any table (A) with an autoincrementing integer primary key.
2. Create another table (B) and use the primary key of the former table as a foreign key. Set the foreign key relationship to NO ACTION.
3. Insert 2 records into table A for good measure.
4. Insert 2 records into table B. The first record in table B should use the primary key of the first record in table A as a foreign key. Likewise, the second record in table B should use the primary key of the second record in table A as a foreign key.
4. Delete the most recent record in table A.
5. Restart mysqld.
6. Insert a new record into table A.
7. Note that it is now the parent of the second record in table B, having supplanted the previous parent that was deleted just prior to restarting mysqld.

Suggested fix:
There are 2 possible solutions:

1. Create a table variable that allows programmers to tell InnoDB tables to preserve their autoincrement settings through shutdowns/restarts/crashes. This would involve storing the value to disk like other engines.

2. Expand the behavior of the auto_increment determinant such that it takes into account not only the highest value existing in a record in the current table, but also the highest value used by other tables as a foreign key referring to the autoincremented column in question. This will prevent inappropriate acquisition of orphaned records.

Thank you for a reasonable feature request.

This bug is critical and is causing us all kinds of grief with database integrity. Please escalate it.

mysqladmin create INNODB_IS_BROKEN

locutus ~ # mysql INNODB_IS_BROKEN
mysql> CREATE TABLE `foo` (                
    ->   `id` int(10) unsigned NOT NULL auto_increment, 
    ->   `name` varchar(20),  
    ->   PRIMARY KEY  (`id`)                    
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO foo values (null, 'a');
mysql> INSERT INTO foo values (null, 'b');
mysql> INSERT INTO foo values (null, 'c');

mysql> SELECT * FROM foo;
+----+------+
| id | name |
+----+------+
|  1 | a    | 
|  2 | b    | 
|  3 | c    | 
+----+------+
3 rows in set (0.00 sec)

mysql> DELETE FROM foo;
Query OK, 3 rows affected (0.00 sec)

mysql> QUIT;

locutus ~ # /etc/init.d/mysql restart
 * Stopping mysql ...
 * Starting mysql (/etc/mysql/my.cnf) 
 
locutus ~ # mysql INNODB_IS_BROKEN
mysql> INSERT INTO foo values (null, 'd');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM foo;
+----+------+
| id | name |
+----+------+
|  1 | d    | 
+----+------+
1 row in set (0.00 sec)

What's the status of this feature request?

I agree that this is a critical problem that needs to be addressed. We are having all kinds of issues related to it. What is the status of this bug?

The current functionality is definitely not intuitive.  A persistent auto-increment would be much appreciated!

Closed bug#34077 as a duplicate of this.

In MySQL 5.6, this ought to become even worse, because tables can be evicted from the InnoDB data dictionary cache. We may get a too low auto-increment value even without shutdown/restart.

When a table is evicted, InnoDB will forget the current auto-increment value, and it will do SELECT MAX(auto_inc_column) next time when the table is accessed.

Duplicate of bug #199. 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