MySQL Forums
Forum List  »  InnoDB

Re: Duplicate Entry for an Auto-increment column
Posted by: Jan Roos
Date: February 12, 2007 07:56AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Duplicate Entry for an Auto-increment column
8652
February 12, 2007 07:56AM


Sorry, you can't reply to this topic. It has been closed.

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.