MySQL Bugs: #38839: auto increment does not work properly with InnoDB after update

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	CPU Architecture:	Any
Tags:	auto increment, auto_increment, regression

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).

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?

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.

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.

Duplicate of bug #16324, although I agree current behavior is not correct and inconsistent if compare to MyISAM.

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)

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

Bug #42102 was marked as duplicate of this one.

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

Noted in 5.1.31, 6.0.10 changelog.

InnoDB could fail to generate AUTO_INCREMENT values after an UPDATE
statement for the table.

Reopened. Original test case still fails.

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

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

See also bug #54165

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)

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)

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)

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.