Bug #11890 auto_increment field incorrectly set from within stored procedure
Submitted: 12 Jul 2005 17:05 Modified: 18 Jan 2006 11:31
Reporter: John Readman Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.6 OS:Linux (Debian)
Assigned to: Assigned Account CPU Architecture:Any

[12 Jul 2005 17:05] John Readman
Description:
I have a simple logging system as part of an ETL process using stored procedures. When I attempt to log the number of rows affected by an action it causes the auto_increment field of the log table to jump.

See below for repeating the problem. In the 'real' system there are multiple tables with different numbers of rows being affected all logging to the same table. My LogID is all over the place and I have encountered Primary Key conflicts because of it.

How to repeat:
Run the following:
USE test;

DROP TABLE IF EXISTS tblLog;

CREATE TABLE tblLog (
	LogID	INT UNSIGNED	NOT NULL	AUTO_INCREMENT,
	LogText	VARCHAR(255)	NOT NULL,
	LogTime	DATETIME	NOT NULL,
	LastUpdate	TIMESTAMP	NOT NULL,
	PRIMARY KEY (LogID)
)
ENGINE = InnoDB;

DROP TABLE IF EXISTS tblT1;

CREATE TABLE tblT1 (
	T1ID	INT UNSIGNED	NOT NULL	AUTO_INCREMENT,
	T1Text	VARCHAR(255)	NOT NULL,
	LastUpdate	TIMESTAMP	NOT NULL,
	PRIMARY KEY (T1ID)
)
ENGINE = InnoDB;

INSERT INTO
	tblT1 (T1Text)
VALUES
	('Test1'), ('Test2'), ('Test3'), ('Test4'), ('Test5'), ('Test6'), ('Test7'), ('Test8'), ('Test9');

DROP TABLE IF EXISTS tblT2;

CREATE TABLE tblT2 (
	T2ID	INT UNSIGNED	NOT NULL	AUTO_INCREMENT,
	T2Text	VARCHAR(255)	NOT NULL,
	LastUpdate	TIMESTAMP	NOT NULL,
	PRIMARY KEY (T2ID)
)
ENGINE = InnoDB;

DROP PROCEDURE IF EXISTS ipLog;

DELIMITER //

CREATE PROCEDURE ipLog (pLogText VARCHAR(255))
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
     	INSERT INTO
		tblLog (
			LogText,
			LogTime)
	VALUES (
                pLogText,
		CURRENT_TIMESTAMP);
END; //

DELIMITER ;

DROP PROCEDURE IF EXISTS ipT2;

DELIMITER //

CREATE PROCEDURE ipT2 ()
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
	DECLARE vInfoText	VARCHAR(32) DEFAULT 'ipT2: ';
	DECLARE vAffected	INT UNSIGNED DEFAULT 0;

	CALL ipLog (CONCAT(vInfoText, 'Started'));

	INSERT INTO
		tblT2 (T2Text)
	SELECT
		T1Text
	FROM
		tblT1;

	SELECT ROW_COUNT() INTO vAffected;

	CALL ipLog (CONCAT(vInfoText, 'Completed. Rows Affected: ', vAffected));

END; //

DELIMITER ;

CALL ipT2 ();

SELECT * FROM tblLog;
+-------+-----------------------------------+---------------------+---------------------+
| LogID | LogText                           | LogTime             | LastUpdate          |
+-------+-----------------------------------+---------------------+---------------------+
|     1 | ipT2: Started                     | 2005-07-12 17:36:14 | 2005-07-12 17:36:14 |
|    10 | ipT2: Completed. Rows Affected: 9 | 2005-07-12 17:36:14 | 2005-07-12 17:36:14 |
+-------+-----------------------------------+---------------------+---------------------+

CALL ipT2 ();

SELECT * FROM tblLog;
+-------+-----------------------------------+---------------------+---------------------+
| LogID | LogText                           | LogTime             | LastUpdate          |
+-------+-----------------------------------+---------------------+---------------------+
|     1 | ipT2: Started                     | 2005-07-12 17:36:14 | 2005-07-12 17:36:14 |
|    10 | ipT2: Completed. Rows Affected: 9 | 2005-07-12 17:36:14 | 2005-07-12 17:36:14 |
|    11 | ipT2: Started                     | 2005-07-12 17:36:14 | 2005-07-12 17:36:14 |
|    19 | ipT2: Completed. Rows Affected: 9 | 2005-07-12 17:36:14 | 2005-07-12 17:36:14 |
+-------+-----------------------------------+---------------------+---------------------+

Instead of being 1,2,3,4 the LogID jumps - seemingly in accordance with the rows affected by the previous action.
[13 Jul 2005 0:10] Timothy Smith
I tested this with MyISAM as well, using our mysql-debug-5.0.7-linux binary.

I took the test case, changed InnoDB to MyISAM in three places.  I ran ipT2() three times, and got results:

mysql> select * from tblLog;
+-------+-----------------------------------+---------------------+-------------
| LogID | LogText                           | LogTime             | LastUpdate
+-------+-----------------------------------+---------------------+-------------
|     1 | ipT2: Started                     | 2005-07-13 02:02:30 | 2005-07-13 0
|    10 | ipT2: Completed. Rows Affected: 9 | 2005-07-13 02:02:30 | 2005-07-13 0
|    11 | ipT2: Started                     | 2005-07-13 02:06:03 | 2005-07-13 0
|    19 | ipT2: Completed. Rows Affected: 9 | 2005-07-13 02:06:03 | 2005-07-13 0
|    20 | ipT2: Started                     | 2005-07-13 02:06:23 | 2005-07-13 0
|    28 | ipT2: Completed. Rows Affected: 9 | 2005-07-13 02:06:23 | 2005-07-13 0
+-------+-----------------------------------+---------------------+-------------
6 rows in set (0.00 sec)

mysql> show table status;
+--------+--------+---------+------------+------+----------------+-------------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length |
+--------+--------+---------+------------+------+----------------+-------------+
| tblLog | MyISAM |      10 | Dynamic    |    6 |             46 |         276 |
| tblT1  | MyISAM |      10 | Dynamic    |    9 |             20 |         180 |
| tblT2  | MyISAM |      10 | Dynamic    |   27 |             20 |         540 |
+--------+--------+---------+------------+------+----------------+-------------+
[25 Oct 2005 19:00] Sokolov Sergey
This bag work on MyISAM Tables;

If in tblLog inserting ID exist, error "DUPLICATE ENTRY '10' FOR KEY 1"

USE test;

DROP TABLE IF EXISTS tblLog;

CREATE TABLE tblLog (
  LogID  INT UNSIGNED  NOT NULL  AUTO_INCREMENT,
  LogText  VARCHAR(255)  NOT NULL,
  LogTime  DATETIME  NOT NULL,
  LastUpdate  TIMESTAMP  NOT NULL,
  PRIMARY KEY (LogID)
)
ENGINE = MyISAM;

DROP TABLE IF EXISTS tblT1;

CREATE TABLE tblT1 (
  T1ID  INT UNSIGNED  NOT NULL  AUTO_INCREMENT,
  T1Text  VARCHAR(255)  NOT NULL,
  LastUpdate  TIMESTAMP  NOT NULL,
  PRIMARY KEY (T1ID)
)
ENGINE = MyISAM;

INSERT INTO
  tblT1 (T1Text)
VALUES
  ('Test1'), ('Test2'), ('Test3'), ('Test4'), ('Test5'), ('Test6'), ('Test7'),
('Test8'), ('Test9');

DROP TABLE IF EXISTS tblT2;

CREATE TABLE tblT2 (
  T2ID  INT UNSIGNED  NOT NULL  AUTO_INCREMENT,
  T2Text  VARCHAR(255)  NOT NULL,
  LastUpdate  TIMESTAMP  NOT NULL,
  PRIMARY KEY (T2ID)
)
ENGINE = MyISAM;

INSERT INTO
       tblLog (  LogID, LogText, LogTime)
VALUES ( 10, 'test', CURRENT_TIMESTAMP);

DROP PROCEDURE IF EXISTS ipLog;

DELIMITER //

CREATE PROCEDURE ipLog (pLogText VARCHAR(255))
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
       INSERT INTO
    tblLog (
      LogText,
      LogTime)
  VALUES (
                pLogText,
    CURRENT_TIMESTAMP);
END; //

DELIMITER ;

DROP PROCEDURE IF EXISTS ipT2;

DELIMITER //

CREATE PROCEDURE ipT2 ()
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
  DECLARE vInfoText  VARCHAR(32) DEFAULT 'ipT2: ';
  DECLARE vAffected  INT UNSIGNED DEFAULT 0;

--  CALL ipLog (CONCAT(vInfoText, 'Started'));

  INSERT INTO
    tblT2 (T2Text)
  SELECT
    T1Text
  FROM
    tblT1;

  SELECT ROW_COUNT() INTO vAffected;

  CALL ipLog (CONCAT(vInfoText, 'Completed. Rows Affected: ', vAffected));

END; //

DELIMITER ;

CALL ipT2 ();
[23 Nov 2005 17:47] Paul Freeman
I'm having the same problem to, on Linux 2.6.13, MySQL 5.0.15 source, with InnoDB engine
[20 Dec 2005 9:43] John Readman
Is this related to bug #14304, fixed in 5.0.17?
[16 Jan 2006 10:12] Jay Bertrand
Hi, this bug is corrected in the 5.0.18-Debian_4-log version (currently in the unstable repository). Regards.
[18 Jan 2006 11:10] Konstantin Osipov
This is a duplicate of Bug#14304 (fixed).
[18 Jan 2006 11:31] John Readman
Well, to be accurate, that was a duplicate of mine ;o)