Bug #38839 auto increment does not work properly with InnoDB after update
Submitted: 18 Aug 2008 3:08 Modified: 14 May 2010 5:16
Reporter: Zardosht Kasheff (OCA) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.1.25-rc, 5.1.26-rc OS:Any
Assigned to: Sunny Bains
Tags: auto increment, auto_increment, regression
Triage: Triaged: D5 (Feature request)

[18 Aug 2008 3:08] Zardosht Kasheff
Description:
The following statements do not work in InnoDB:

create table t1 (a int not null auto_increment primary key, val int);
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
insert into t1 (val) values (1);

What happens is that the insert fails with:
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

This works just fine in MyISAM. InnoDB is not updating the auto increment key.

How to repeat:
Run statements above.

Suggested fix:
The last insert statement should be trying to insert (3,1), not (2,1).
[18 Aug 2008 3:08] Zardosht Kasheff
The problem seems to be that InnoDB cannot extract the auto increment value in update_row to check if maintenence is required. That is, this clause does not execute:

	if (error == DB_SUCCESS
	    && table->next_number_field
	    && new_row == table->record[0]
	    && thd_sql_command(user_thd) == SQLCOM_INSERT
	    && (trx->duplicates & (TRX_DUP_IGNORE | TRX_DUP_REPLACE))
		== TRX_DUP_IGNORE)  {

		longlong	auto_inc;

		auto_inc = table->next_number_field->val_int();

		if (auto_inc != 0) {
			auto_inc += prebuilt->table->autoinc_increment;

			error = innobase_set_max_autoinc(auto_inc);
		}
	}

I am also trying to do "table->next_number_field->val_int();" in our storage engine, but am getting an assertion failure. How does one extract the auto inc value in update_row?
[18 Aug 2008 4:41] Valerii Kravchuk
Why do you think that it is a bug? I do not see the description of the behaviour you want at http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html.

I do confirm the difference in behaviour of InnoDB vs. MyISAM, but I'd consider this report a documentation or a feature request.
[18 Aug 2008 12:16] Zardosht Kasheff
I think there is a bug because InnoDB's behavior is inconsistent. After playing around some more, perhaps the example I provided is not good.

I have been playing around with InnoDB to see how auto increment behaves when values are manually inserted for the auto increment field, and it seems inconsistent. Here is an example of the inconsistency. I replace the update with a manual delete and insert. Suppose I did the following in InnoDB:
mysql> create table inn (a int auto_increment, b int, primary key (a))engine=InnoDB;
mysql> insert into inn values (NULL, 1);                                        mysql> delete from inn where a = 1;
mysql> insert into inn values (3,1);
mysql> insert into inn values (NULL,8);
mysql> select * From inn;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
| 4 |    8 |
+---+------+

Now compare it to this:
mysql> create table inn (a int auto_increment, b int, primary key (a))engine=InnoDB;
mysql> insert into inn values (NULL, 1);
mysql> delete from inn where a = 1;
mysql> insert into inn values (2,1);
mysql> insert into inn values (NULL,8);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

In one instance, the auto increment field was updated after the manual insert (of (3,1)), and in the other, it was not updated causing an eventual duplicate entry error. This inconsistency seems like a bug.
[18 Aug 2008 13:40] Sveta Smirnova
Duplicate of bug #16324, although I agree current behavior is not correct and inconsistent if compare to MyISAM.
[18 Aug 2008 19:53] Valerii Kravchuk
Verified just as described in your last comment:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.26-rc-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table inn;
Query OK, 0 rows affected (0.03 sec)

mysql> create table inn (a int auto_increment, b int, primary key (a))engine=Inn
oDB;
Query OK, 0 rows affected (0.17 sec)

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

mysql> delete from inn where a = 1;
Query OK, 1 row affected (0.06 sec)

mysql> insert into inn values (2,1);
Query OK, 1 row affected (0.08 sec)

mysql> insert into inn values (NULL,8);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

Note also that recent 5.0.66a is more consistent:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table inn (a int auto_increment, b int, primary key (a))engine=Inn
oDB;
Query OK, 0 rows affected (2.25 sec)

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

mysql> delete from inn where a = 1;
Query OK, 1 row affected (0.09 sec)

mysql> insert into inn values (2,1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into inn values (NULL,8);
Query OK, 1 row affected (0.01 sec)

mysql> select * from inn;
+---+------+
| a | b    |
+---+------+
| 2 |    1 |
| 3 |    8 |
+---+------+
2 rows in set (0.03 sec)
[20 Aug 2008 9:48] Sunny Bains
Zardosht,

Why do you think ha_innobase::update_row() is being called for the
INSERT statements ? If you want 5.0 behavior then please set the
AUTOINC locking mode as:

     innodb_autoinc_lock_mode = 0 (a.k.a traditional)

The real problem is that  prebuilt->last_value is not being reset
to 0 at then end of the statement (INSERT INTO inn VALUES(2,0)).

Thank you for the test case :-)

Regards,
-sunny
[14 Jan 2009 10:08] Sveta Smirnova
Bug #42102 was marked as duplicate of this one.
[12 Mar 2009 1:08] Timothy Smith
Fixed in 5.1.31 and 6.0.10.  Comments:

branches/5.1: Fix for MySQL Bug#38839. Reset the statement level last
value field in prebuilt. This field tracks the last value in an autoincrement
interval. We use this value to check whether we need to update a table's
AUTOINC counter, if the value written to a table is less than this value
then we avoid updating the table's AUTOINC value in order to reduce
mutex contention. If it's not reset (e.g., after a DELETE statement) then
there is the possibility of missing updates to the table's AUTOINC counter
resulting in a subsequent duplicate row error message under certain 
conditions (see the test case for details).

Bug #38839 - auto increment does not work properly with InnoDB after update
[12 Mar 2009 21:38] Paul Dubois
Noted in 5.1.31, 6.0.10 changelog.

InnoDB could fail to generate AUTO_INCREMENT values after an UPDATE
statement for the table.
[24 Apr 2009 21:31] Sergei Golubchik
Reopened. Original test case still fails.
[25 Apr 2009 22:15] Sunny Bains
In an internal discussion on the original bug report, Heikki had this to say:

"
It is intentional that an UPDATE does not change the counter, a DELETE + INSERT is needed to change the counter value. MyISAM is different in this.
"

Regards,
-sunny
[5 May 2010 15:10] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:06] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[2 Jun 2010 10:13] Sveta Smirnova
See also bug #54165
[17 Jun 2010 12:15] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:02] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:43] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[9 May 2012 23:37] John Russell
Added this InnoDB-specific note to the tutorial topic in the doc:

For InnoDB tables, be careful if you modify the column containing the
auto-increment value in the middle of a sequence of INSERT
statements. For example, if you use an UPDATE statement to put a new,
larger value in the auto-increment column, a subsequent INSERT could
encounter a "Duplicate entry" error. The test whether an
auto-increment value is already present occurs if you do a DELETE
followed by more INSERT statements, or when you COMMIT the
transaction, but not after an UPDATE statement.