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.

Page generated in 0.097 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.