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" <[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
>>
>
>
>
--
Klein bottle for rent - enquire within.
---------------------------------------------------------------------
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
