MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Steve Hay Date:July 10 2002 12:56pm
Subject:Re: Bug with AUTO_INCREMENT
View as plain text  
Richard Fox wrote:

>Can your INT id be overflowing, i.e. reaches the max value for int and
>starting at 1 again?
I don't think it is because (a) the last value that was successfully 
INSERT'ed - usually around 40,000-odd - is well short of the maximum 
INT, and (b) it doesn't always fall over after some particular number of 
iterations - it falls over at different, apparently random, times each 
time I try the test.

- Steve

>----- Original Message -----
>From: "Steve Hay" <Steve.Hay@stripped>
>To: <mysql@stripped>
>Sent: Wednesday, July 10, 2002 4:12 AM
>Subject: Bug with AUTO_INCREMENT
>>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 TABLE foo (
>>       num INT,
>>       str VARCHAR(10),
>>       PRIMARY KEY (id)
>>    ) TYPE=BDB;
>>Now start two "mysql.exe" sessions running with the "-f" flag to ignore
>>Set each one into "no auto-commit" mode with:
>>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):
>>    ----------
>>    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 | \mysql\bin\mysql.exe -f -u root test 2>
>>    Command Prompt Two:
>>    \perl5\bin\perl.exe | \mysql\bin\mysql.exe -f -u root test 2>
>>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"
>>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,
>>Before posting, please check:
>>   (the manual)
>>           (the list archive)
>>To request this thread, e-mail <mysql-thread114171@stripped>
>>To unsubscribe, e-mail <>
>>Trouble unsubscribing? Try:

Klein bottle for rent - enquire within.

Bug with AUTO_INCREMENTSteve Hay10 Jul
  • Re: Bug with AUTO_INCREMENTRichard Fox10 Jul
  • Re: Bug with AUTO_INCREMENTSteve Hay10 Jul
    • Re: Bug with AUTO_INCREMENTThomas Spahni10 Jul
  • Re: Bug with AUTO_INCREMENTVictoria Reznichenko12 Jul
  • Re: Bug with AUTO_INCREMENTSteve Hay12 Jul
Re: Bug with AUTO_INCREMENTSteve Hay10 Jul
Re: Bug with AUTO_INCREMENTMark Matthews10 Jul
Re: Bug with AUTO_INCREMENTSteve Hay12 Jul