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?
>Rich
>
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
>
>
>>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 <mysql-thread114171@stripped>
>>To unsubscribe, e-mail <mysql-unsubscribe-rfox=sbsii.com@stripped>
>>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
>
>
>


-- 
Klein bottle for rent - enquire within.



Thread
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