MySQL Bugs: #49032: auto_increment field does not initialize to last value in InnoDB Storage Engine

Bug #49032	auto_increment field does not initialize to last value in InnoDB Storage Engine
Submitted:	24 Nov 2009 12:33	Modified:	21 Jul 2010 15:55
Reporter:	Ignacio Enrique	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.37, 5.1.40, 5.1.41, 5.4.3	OS:	Any (Windows, Mac OS X)
Assigned to:	Calvin Sun	CPU Architecture:	Any
Tags:	auto_increment, duplicate, innodb, restart

Description:
When windows mysql service restart the next auto_increment value for tables with InnoDB storage engine goes to 1.

This occurs also if server variable innodb_autoinc_lock_mode is set to 0. The described procedure of the user´s manual of this variable ( searching the last value and setting it to this value + 1 in the memory area for auto_increments in InnoDB storaghe engine) does not occurs.

This is a bug that not permit us going to version 5.1.41 or 5.4.3. It forces us to stay in version 5.0.88 that works OK.

How to repeat:
To reproduce it execute this script

testinnodb
DROP TABLE IF EXISTS `testinnodb`;
CREATE TABLE `testinnodb` (
  `ID_testinnodb` double NOT NULL AUTO_INCREMENT,
  `ID_USR` double DEFAULT NULL,
  `DAT_testinnodb` datetime NOT NULL,
  `TIM_testinnodb` varchar(8) NOT NULL,
  `COD_SYSTEM` varchar(1) NOT NULL,
  `COD_MODULE` varchar(1) NOT NULL,
  `COD_ACTION` varchar(1) NOT NULL,
  `COD_OPTION` varchar(1) NOT NULL,
  `COD_DATA` varchar(2) NOT NULL,
  `DES_DATA` varchar(150) NOT NULL,
  `DES_IP` varchar(15) DEFAULT NULL,
  `ID_PERSON` double DEFAULT NULL,
  `ID_CENTRE` double DEFAULT NULL,
  PRIMARY KEY (`ID_testinnodb`),
  KEY `XIF3testinnodb` (`ID_CENTRE`),
  KEY `R_USR_TRACK` (`ID_USR`),
  KEY `R_PERSON_testinnodb` (`ID_PERSON`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `testinnodb`
--

/*!40000 ALTER TABLE `testinnodb` DISABLE KEYS */;
INSERT INTO `testinnodb` (`ID_testinnodb`,`ID_USR`,`DAT_testinnodb`,`TIM_testinnodb`,`COD_SYSTEM`,`COD_MODULE`,`COD_ACTION`,`COD_OPTION`,`COD_DATA`,`DES_DATA`,`DES_IP`,`ID_PERSON`,`ID_CENTRE`) VALUES 
 (1,1,'2009-11-23 00:00:00','12:00:21','B','M','A','M','MM','patatas','127.0.0.1',NULL,NULL),
 (2,1,'2009-11-23 00:00:00','12:00:28','B','M','A','M','MM','patatas','127.0.0.1',NULL,NULL),
 (4,1,'2009-11-23 00:00:00','12:00:28','B','M','A','M','MM','patatas','127.0.0.1',NULL,NULL);
/*!40000 ALTER TABLE `testinnodb` ENABLE KEYS */;

then stop windows mysql service

then start windows mysql service

then try to execute this sentence:

insert into testinnodb ( id_usr, dat_testinnodb, tim_testinnodb, cod_system, cod_module, cod_action, cod_option, cod_data, des_data, des_ip)
values(1, '2009-11-23', '12:00:28',
 'B', 'M', 'A', 'M', 'MM','patatas','127.0.0.1')

it reports and error of  duplicate key in mysql 5.1.40 and followers also if innodb_autoinc_lock_mode=0

it works ok in versíon 5.0.88 and below

Suggested fix:
Mysql must execute the steps described in http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
in parragraph 13.6.4.3.1 when innodb_autoinc_lock_mode is set to 0 as described.

Today this not occurs

Thank you for the bug report. Verified just as described with recent 5.1.42-debug from bzr:

77-52-12-228:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like 'innodb_auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 8     |
| innodb_autoinc_lock_mode    | 0     |
+-----------------------------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE `testinnodb` (   `ID_testinnodb` double NOT NULL AUTO_INCREMENT,   `ID_USR` double DEFAULT NULL,   `DAT_testinnodb` datetime NOT NULL,   `TIM_testinnodb` varchar(8) NOT NULL,   `COD_SYSTEM` varchar(1) NOT NULL,   `COD_MODULE` varchar(1) NOT NULL,   `COD_ACTION` varchar(1) NOT NULL,   `COD_OPTION` varchar(1) NOT NULL,   `COD_DATA` varchar(2) NOT NULL,   `DES_DATA` varchar(150) NOT NULL,   `DES_IP` varchar(15) DEFAULT NULL,   `ID_PERSON` double DEFAULT NULL,   `ID_CENTRE` double DEFAULT NULL,   PRIMARY KEY (`ID_testinnodb`),   KEY `XIF3testinnodb` (`ID_CENTRE`),   KEY `R_USR_TRACK` (`ID_USR`),   KEY `R_PERSON_testinnodb` (`ID_PERSON`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `testinnodb`
    -> (`ID_testinnodb`,`ID_USR`,`DAT_testinnodb`,`TIM_testinnodb`,`COD_SYSTEM`,`COD_MODULE`,`COD_ACTION`,`COD_OPTION`,`COD_DATA`,`DES_DATA`,`DES_IP`,`ID_PERSON`,`ID_CENTRE`)
    -> VALUES 
    ->  (1,1,'2009-11-23
    '> 00:00:00','12:00:21','B','M','A','M','MM','patatas','127.0.0.1',NULL,NULL),
    ->  (2,1,'2009-11-23
    '> 00:00:00','12:00:28','B','M','A','M','MM','patatas','127.0.0.1',NULL,NULL),
    ->  (4,1,'2009-11-23
    '> 00:00:00','12:00:28','B','M','A','M','MM','patatas','127.0.0.1',NULL,NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table testinnodb\G
*************************** 1. row ***************************
       Table: testinnodb
Create Table: CREATE TABLE `testinnodb` (
  `ID_testinnodb` double NOT NULL AUTO_INCREMENT,
  `ID_USR` double DEFAULT NULL,
  `DAT_testinnodb` datetime NOT NULL,
  `TIM_testinnodb` varchar(8) NOT NULL,
  `COD_SYSTEM` varchar(1) NOT NULL,
  `COD_MODULE` varchar(1) NOT NULL,
  `COD_ACTION` varchar(1) NOT NULL,
  `COD_OPTION` varchar(1) NOT NULL,
  `COD_DATA` varchar(2) NOT NULL,
  `DES_DATA` varchar(150) NOT NULL,
  `DES_IP` varchar(15) DEFAULT NULL,
  `ID_PERSON` double DEFAULT NULL,
  `ID_CENTRE` double DEFAULT NULL,
  PRIMARY KEY (`ID_testinnodb`),
  KEY `XIF3testinnodb` (`ID_CENTRE`),
  KEY `R_USR_TRACK` (`ID_USR`),
  KEY `R_PERSON_testinnodb` (`ID_PERSON`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> exit
Bye
77-52-12-228:5.1 openxs$ bin/mysqladmin -uroot shutdown

...

77-52-12-228:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create table testinnodb\G
*************************** 1. row ***************************
       Table: testinnodb
Create Table: CREATE TABLE `testinnodb` (
  `ID_testinnodb` double NOT NULL AUTO_INCREMENT,
  `ID_USR` double DEFAULT NULL,
  `DAT_testinnodb` datetime NOT NULL,
  `TIM_testinnodb` varchar(8) NOT NULL,
  `COD_SYSTEM` varchar(1) NOT NULL,
  `COD_MODULE` varchar(1) NOT NULL,
  `COD_ACTION` varchar(1) NOT NULL,
  `COD_OPTION` varchar(1) NOT NULL,
  `COD_DATA` varchar(2) NOT NULL,
  `DES_DATA` varchar(150) NOT NULL,
  `DES_IP` varchar(15) DEFAULT NULL,
  `ID_PERSON` double DEFAULT NULL,
  `ID_CENTRE` double DEFAULT NULL,
  PRIMARY KEY (`ID_testinnodb`),
  KEY `XIF3testinnodb` (`ID_CENTRE`),
  KEY `R_USR_TRACK` (`ID_USR`),
  KEY `R_PERSON_testinnodb` (`ID_PERSON`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into testinnodb ( id_usr, dat_testinnodb, tim_testinnodb, cod_system, cod_module,
    -> cod_action, cod_option, cod_data, des_data, des_ip)
    -> values(1, '2009-11-23', '12:00:28',
    ->  'B', 'M', 'A', 'M', 'MM','patatas','127.0.0.1')
    -> ;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> show global variables like 'innodb_auto%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 8     |
| innodb_autoinc_lock_mode    | 0     |
+-----------------------------+-------+
2 rows in set (0.00 sec)

This behavior is, indeed, different from 5.0 and documented one.

A simpler test case works as expected:

DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT AUTO_INCREMENT KEY, txt CHAR(5));
INSERT INTO t (id, txt) VALUES (1,'a'),(3,'b');
-- restart server
INSERT INTO t (txt) VALUES ('c');

what are the conditions for reproducing this bug?

the auto_increment must be a double type to reproduce it.

To Peter Laursen 

The create table must be for innoDB engine to reproduce de bug you mast add the parameters ENGINE=InnoDB DEFAULT CHARSET=latin1 to the create table statement.

Hi,

The bug is due to this line in row/row0sel.c:

  value = mach_read_int_type(data, len, unsigned_type);

The fix is to use the appropriate function to read the value of
the AUTOINC column for float and double column types.

Regards,
-sunny

@Ignacio: InnoDB Engine is default in my server configuration so I won't need to specify InnoDB. Shane and Sunny have made it clear that this happens with auto_increment of floating point types (FLOAT, DOUBLE) only and not INTEGERS. I think you should consider using an INT/BIGINT for the auto_increment column as WHERE-conditions against it may fail. Read:
http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html

@Peter 
Thanks Peter. Unfortunately we have to use DOUBLE because size restrictions and a unexpected conversión to BCD from BIGINT fields reading MySQL from BORLAND DELPHI V7 drivers. But we dont use Double in aritmetic operations only in row identification and without decimals.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/92036

3218 Satya B	2009-11-30
      Applying InnoDB snapshot 5.1-ss6242, part 7. Fixes BUG#49032
      
      1. BUG#49032 - auto_increment field does not initialize to last value 
                     in InnoDB Storage Engine
      
      2. Fix whitespace issues and fix tests and make read float/double arg const
      
      Detailed revision comments:
      
      r6231 | sunny | 2009-11-25 10:26:27 +0200 (Wed, 25 Nov 2009) | 7 lines
      branches/5.1: Fix BUG#49032 - auto_increment field does not initialize to last value in InnoDB Storage Engine.
      
      We use the appropriate function to read the column value for non-integer
      autoinc column types, namely float and double.
      
      rb://208. Approved by Marko.
      
      r6232 | sunny | 2009-11-25 10:27:39 +0200 (Wed, 25 Nov 2009) | 2 lines
      branches/5.1: This is an interim fix, fix white space errors.
      
      r6233 | sunny | 2009-11-25 10:28:35 +0200 (Wed, 25 Nov 2009) | 2 lines
      branches/5.1: This is an interim fix, fix tests and make read float/double arg const.
      
      
      r6234 | sunny | 2009-11-25 10:29:03 +0200 (Wed, 25 Nov 2009) | 2 lines
      branches/5.1: This is an interim fix, fix whitepsace issues.

patch queued to 5.1-bugteam storage/innobase only and not for the plugin.

Please set back to verified and assign calvin after documenting.

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:satya.bn@sun.com-20091130094138-81lvmexqn6osk62d) (merge vers: 5.1.42) (pib:13)

Noted in 5.1.42 changelog.

InnoDB did not reset table AUTO_INCREMENT values to the last used 
values after a server restart. 

Setting to Verified and assigning to Calvin per earlier comment.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:satya.bn@sun.com-20091202114649-zt975apdali0jy3c) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

We don't think this was fixed in the InnoDB plugin. The plugin is the only InnoDB in 5.1.44. When will that be fixed?

Mark - do not understand what you mean "The plugin is the only InnoDB in
5.1.44". Just checked the 5.1.44 source (mysql-5.1.44.tar.gz), both innobase and innodb_plugin are there. The fix is in internal repo right now, and will be released under 1.0.7.

Thanks,
Calvin

Calvin -- for some reason I thought that the built-in was done, even though the source was there. I don't know where I go that idea.

Mark, afaik, the built-in InnoDB will be in 5.1 forever. But starting from 5.4, the plugin replaces the original built-in. Thanks!

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)

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)

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

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)

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

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

Changed version tags in changelog to 5.1.46 and 5.5.4.