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!
Subject
Views
Written By
Posted
5207
September 13, 2006 10:47PM
3485
September 14, 2006 10:45AM
3210
September 14, 2006 07:10PM
3025
September 14, 2006 10:31PM
2966
September 15, 2006 01:06AM
2845
September 19, 2006 12:55AM
3251
September 19, 2006 02:05AM
Re: Duplicate Entry for an Auto-increment column
10276
February 12, 2007 07:56AM
3361
February 24, 2007 01:05PM
3328
October 02, 2008 11:06AM
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.