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:	15 Aug 2006 1:57	Modified:	28 Aug 2006 13:08
Reporter:	Stephen Dewey
Status:	Verified
Category:	Server: InnoDB	Severity:	S4 (Feature request)
Version:	4.1.20	OS:	Linux (Linux (Red Hat))
Assigned to:	Heikki Tuuri	Target Version:
Tags:	innodb, auto_increment
Triage:	Triaged: D5 (Feature request)

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.