MySQL Forums :: InnoDB :: Duplicate Entry for an Auto-increment column

Hi All!

I have the same problem with triggers and it repeats every time! I minimized the example as much as I could.

CREATE TABLE Logs( id INT PRIMARY KEY auto_increment ) engine=INNODB;
CREATE TABLE Table1( id int PRIMARY KEY auto_increment, data TEXT ) engine=INNODB;
CREATE TABLE Table2( id int PRIMARY KEY auto_increment, data TEXT ) engine=INNODB;

DELIMITER $$

CREATE TRIGGER log_Table1 AFTER INSERT ON Table1 FOR EACH ROW
BEGIN
INSERT INTO Logs() VALUES();
END $$

CREATE TRIGGER log_Table2 AFTER INSERT ON Table2 FOR EACH ROW
BEGIN
INSERT INTO Logs() VALUES();
END $$

DELIMITER ;

Now insert into tables Table1 and Table2 __at the same time from different connections__:

INSERT INTO Table1 ( data ) SELECT Id FROM some_table_with_much_rows;

INSERT INTO Table2 ( data ) SELECT Id FROM some_table_with_much_rows;

I always get the error from one connection:
Error: Duplicate entry <no> for key 1

<no> is id from table Logs.
I am using Mysql ver "5.0.24a-Debian_4-log" from a Debian package.

I can't lock the table 'Logs' inside the triggers.
I get (0A000): LOCK is not allowed in stored procedures

I am trying to implement a data modification log with triggers. This is absolutely necessary. Are there any workarounds?


Thank you!