Can your INT id be overflowing, i.e. reaches the max value for int and starting at 1 again? Rich
----- Original Message ----- From: "Steve Hay" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 10, 2002 4:12 AM Subject: Bug with AUTO_INCREMENT > Hi, > > Continuing my troubles with AUTO_INCREMENT (which nobody has yet > responded to), I've now ruled the Perl DBI out of the equation and > reproduced the same problem using the "mysql.exe" command-line tool. > > Hopefully somebody will be tempted to help this time now that there is > no Perl involved to put people off? > > The bug can be reproduced as follows: > > Create the following simple database containing just one BDB table: > > CREATE DATABASE test; > > CREATE TABLE foo ( > id INT NOT NULL AUTO_INCREMENT, > num INT, > str VARCHAR(10), > PRIMARY KEY (id) > ) TYPE=BDB; > > Now start two "mysql.exe" sessions running with the "-f" flag to ignore > errors. > > Set each one into "no auto-commit" mode with: > > SET AUTOCOMMIT=0; > > and then feed each of them the following two lines 50,000 times each: > > INSERT INTO foo (num, str) VALUES (1, 'a'); > COMMIT; > > On my NT4 machine I do this with the simple Perl program (I know I said > Perl wasn't involved, but the Perl DBI still isn't): > > sql.pl > ---------- > print "SET AUTOCOMMIT=0;\n"; > for (1 .. 50000) { > print "INSERT INTO foo (num, str) VALUES (1, 'a');\n"; > print "COMMIT;\n"; > } > > I pipe the output from this to the "mysql.exe" programs, and then > re-direct STDERR from that to log files, e.g.: > > Command Prompt One: > \perl5\bin\perl.exe sql.pl | \mysql\bin\mysql.exe -f -u root test 2> > session1.txt > > Command Prompt Two: > \perl5\bin\perl.exe sql.pl | \mysql\bin\mysql.exe -f -u root test 2> > session2.txt > > When I inspect the output collected in these log files I find, not > unexpectedly, that there are many "deadlock" errors (which I'm quite > comfortable with, and can handle in my real software by simply retrying > the transaction), but that there are also a few of the following error: > > Duplicate entry '1' for key 1 > > Surely this is a bug isn't it? > > The transaction that is being performed 50,000 times each by the two > "mysql.exe" sessions doesn't specify any value for the AUTO_INCREMENT > column "id" - it just leaves it up to the database to choose the "next" > value. > > This error suggests that the database has wrongly chosen the value '1', > and then (rightly) complains that it is already used! > > I would really appreciate a response on this one as I'm now at the end > of my tether trying to work out what is wrong. > > Thanks in advance, > > Steve > > > > --------------------------------------------------------------------- > 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 > --------------------------------------------------------------------- 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