List:General Discussion« Previous MessageNext Message »
From:Steve Hay Date:July 10 2002 8:12am
Subject:Bug with AUTO_INCREMENT
View as plain text  
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


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