| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.6 | OS: | Linux (Debian) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[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)
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.