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
