Skip navigation links

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


Advanced Search

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
Duplicate Entry for an Auto-increment column 3840 East Heretic 09/13/2006 10:47PM
Re: Duplicate Entry for an Auto-increment column 2951 Luis 09/14/2006 10:45AM
Re: Duplicate Entry for an Auto-increment column 2522 KimSeong Loh 09/14/2006 07:10PM
Re: Duplicate Entry for an Auto-increment column 2435 East Heretic 09/14/2006 10:31PM
Re: Duplicate Entry for an Auto-increment column 2389 Luis 09/15/2006 01:06AM
Re: Duplicate Entry for an Auto-increment column 2269 East Heretic 09/19/2006 12:55AM
Re: Duplicate Entry for an Auto-increment column 2618 Luis 09/19/2006 02:05AM
Re: Duplicate Entry for an Auto-increment column 7739 Jan Roos 02/12/2007 07:56AM
Re: Duplicate Entry for an Auto-increment column 2804 Shane Bester 02/24/2007 01:05PM
Re: Duplicate Entry for an Auto-increment column 2551 Gniontas Gniontas 10/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.