Locking problem

Hello,

I discovered a repeatable bug leading to corrupted table. I've submitted=20
the file "locking_problem.zip" to=20
ftp://support.mysql.com/pub/mysql/secret. It contains five files:

* fail_it.sql: script to be run to repeat the bug (How-To-Repeat:). Run it=
=20
in mysql client, but it works from PHP (4.2.1) as well. It must be run=20
under root privileges, because it will create a new database with=20
name "foo" and after executing there will be one table "prace", which will=
=20
be corrupted (on my computer in 100% of cases). Description is below.
* my.ini: mysql parameters
* show_status.out: result of SHOW STATUS after about 15 executions of the=
=20
script
* show_variables.out: result of SHOW VARIABLES;
* check_table.out: result of CHECK TABLE foo.prace;

I'm running MySQL 3.23.51-log on Windows98 (ver. 4.10.2222). My machine is=
=20
AMD Duron, 256MB SDRAM. I use mysqld-opt from standard Windows binary=20
distribution. Mysqld parameters are in "my.ini" file. I've tested the=20
script from mysql client (mysql.exe) and from PHP 4.2.1 with the same=20
result. Result of CHECK TABLE query is in "check_table.out". I've=20
encountered this problem in my application also on version 3.23.47.

Description of what is done in the fail_it script:
1. Create database "foo" and use it,
2. Create a table with name "prace" and fill it with data,
3. Creates another table "tmp_prace" with the same structure as "prace",
4. Lock both tables "prace" and "tmp_prace" for writing,
5. Copy all data from "prace" into "tmp_prace",
6. Do lot of updates, each increasing value of one column in one row,
7. Drop table "prace",
8. Rename table "tmp_prace" to "prace",
9. Unlock tables

When I swap steps 8 and 9, table is not corrupted. If there were not so=20
many updates in step 6 the probability of getting bad table decreases.

So run the script and you'll see...

Viliam Durina


=0A____________________________________=0Ahttp://www.pobox.s k/ - spolahliva=
a bezpecna prevadzka =0A

------------------------------------------------------------ ---------
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 bugs-thread12539@lists.mysql.com
To unsubscribe, e-mail