Bug #39830 Table autoinc value not updated on first insert

• Submitted: 3 Oct 2008 7:24
• Modified: 18 Jun 2010 13:00
• Reporter: Sunny Bains
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0, 5.1, 5.1-plugin
• OS: Any
• Assigned to: Sunny Bains
• CPU Architecture: Any

[3 Oct 2008 7:24] Sunny Bains

Description:
Autoinc system is uninitialized at startup and therefore if the user inserts an explicit value into an autoinc column as the first DML statement that value is not copied to the table's global autoinc value because the autoinc sub-system hasn't been initialized yet.

How to repeat:
This test was on 5.0 server instance, 5.1 should exhibit similar behavior.

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (18446744073709551603);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+----------------------+
| c1                   |
+----------------------+
|                    1 | <- should be 18446744073709551604
| 18446744073709551603 |
+----------------------+
2 rows in set (0.00 sec)

Suggested fix:
5.0: Will not fix! Hasn't been reported from the field so my guess is that
     it is a minor issue (if at all). Also, it will be a change in behavior
     and risky.

5.1: Initialize the table max autoinc value during ha_innobase::open(). This
     solution is possible because of the new AUTOINC code in 5.1.

[12 Mar 2009 21:23] Paul DuBois

Noted in 5.1.31, 6.0.10 changelogs.

InnoDB could fail to generate AUTO_INCREMENT values if rows
previously had been inserted containing literal values for the
AUTO_INCREMENT column.

[5 May 2010 15:15] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 16:51] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:12] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:40] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:08] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 23:20] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:15] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:31] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:15] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:01] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:42] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)