Hi,
I'm having trouble trying to INSERT rows into a table which has an
AUTO_INCREMENT PRIMARY KEY column plus a UNIQUE constraint on two other
columns.
For each INSERT I specify NULL for the AUTO_INCREMENT column to let the
database assign a value automatically, but I frequently get the error:
Duplicate entry '1' for key 1
Bizarrely, if I remove the UNIQUE constraint on the other two columns
then it all behaves itself!!!
Is there some sort of bug here? I can't see anything wrong with what
I'm doing -- it is the database itself which chooses what value to
INSERT into the AUTO_INCREMENT column, but keeps erroneously choosing
the value '1' and then complaining that the value is already used!!!
I've reduced my database and software down to the bare minimum that
illustrates the problem. Here is the database definition:
------------------------------
CREATE DATABASE test;
CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT,
num INT NOT NULL,
str VARCHAR(10) NOT NULL,
PRIMARY KEY (id),
UNIQUE (str, num)
) TYPE=BDB;
------------------------------
If I run two instances of the following Perl program on the above
database then one of them quickly exits with the error "Duplicate entry
'1' for key 1"; I'm sure it shouldn't do this:
------------------------------
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect(
'dbi:mysql:test', 'root', '',
{AutoCommit => 0, PrintError => 0, RaiseError => 1}
);
for (my $i = 1; $i <= 10000; $i++) {
my $id;
eval {
my $sth1 = $dbh->prepare("SELECT id FROM foo WHERE num = 0");
$sth1->execute();
my $rows1 = $sth1->fetchall_arrayref();
$sth1->finish();
die "Unexpectedly found something!\n" if @$rows1;
my $str = $$ . ':' . time();
$dbh->do("INSERT INTO foo (id, num, str) " .
"VALUES (NULL, $i, '$str')");
my $sth2 = $dbh->prepare("SELECT LAST_INSERT_ID()");
$sth2->execute();
my $rows2 = $sth2->fetchall_arrayref();
$sth2->finish();
die "Unexpectedly found nothing!\n" unless @$rows2;
die "Unexpectedly too much!\n" if @$rows2 > 1;
$id = $rows2->[0][0];
$dbh->commit();
};
if ($@) {
if ($DBI::err == 1213) {
print "$i: $$: Deadlock! Retrying ...\n";
$dbh->rollback();
redo;
}
else {
print "$i: $$: Error! ($@) Exiting ...\n";
last;
}
}
else {
print "$i: $$: Success! INSERTed ID $id\n";
}
}
$dbh->disconnect();
------------------------------
All this program does is the following transaction 10000 times over
(retrying a transaction if it fails due to deadlock):
SELECT the 'id' column WHERE the 'num' column is zero (which should
always find nothing)
INSERT a new row, assigning the 'id' column value automatically
SELECT the LAST_INSERT_ID()
COMMIT the changes
The first SELECT statement serves no purpose in this test program (a
SELECT at this point is required in the real software that I'm writing),
but if I remove it then I don't get the error any more!!!
What is going on?
Any help would be greatly appreciated.
- Steve
---
OS: Windows NT4 SP6
MySQL: 3.23.51 Max
Perl: 5.6.1
DBI: 1.28
DBD::mysql: 2.1017
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
