Bug #12082 Prepared statement "FOR UPDATE" "LOCK IN SHARE MODE": timeout gives server crash
Submitted: 21 Jul 2005 13:52 Modified: 12 Aug 2005 19:29
Reporter: Berto van de Kraats Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.10-beta OS:Linux (Linux)
Assigned to: Sergey Petrunya CPU Architecture:Any

[21 Jul 2005 13:52] Berto van de Kraats
Description:
If one session holds an exclusive lock on a row, and another session attempts to lock the same row in "SHARE MODE", then when the statement in the second sessions times out, the MySQL server crashes.

How to repeat:
Compile and run the attached program (which is an automatically generated trace
of our mysql database driver). The problem reproduces if the program silently
runs and has exit status 0.

The trace contains a call to mysql_stmt_execute that returns 1 because the
server crashes (search for pattern "ERROR:" in attached program).
[21 Jul 2005 13:54] Berto van de Kraats
Trace of problem

Attachment: bug12082.cpp (text/plain), 16.19 KiB.

[21 Jul 2005 17:56] Heikki Tuuri
Peter,

does mysqld print anything to the .err log?

Can you repeat the crash using the mysql interactive SQL client?

I tested a similar case with 2 weeks old 5.0 and the SQL client, but got:

heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.10-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t20 (a char(30) not null primary key, b int) type = innodb;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t20 values ('abc', 10);
Query OK, 1 row affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t20 where a = 'abc' for update;
+-----+------+
| a   | b    |
+-----+------+
| abc |   10 |
+-----+------+
1 row in set (0.00 sec)

mysql> exit
Bye

heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t20 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> exit

Regards,

Heikki
[21 Jul 2005 17:59] Heikki Tuuri
Hmm... since you are using prepared statements, this more probably is a bug in them.

I am updating the synopsis.

--Heikki
[21 Jul 2005 18:13] Jorge del Conde
Peter, thanks for your excellent bug report.

I was able to reproduce this using a recent bk pull of our 5.0 tree
[21 Jul 2005 23:47] Heikki Tuuri
Jorge,

please post the gdb backtrace of the crash, using a debug version of mysqld

(gdb) bt full

Regards,

Heikki
[30 Jul 2005 22:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27744
[30 Jul 2005 22:51] Sergey Petrunya
Notes for the reviewer: 
* The crash is easily repeatable in command line and has nothing to do with prepared statements (afaiu Heikki couldn't repeat it because he exited the first client after 
running the "... LOCK IN SHARE MODE" query)
* I decided not to add a test case to test suite because we'll either need to restart the server with low innodb_lock_timeout (which means adding one more .test file or modifying some existing file), or make a test suite run 30 seconds slower (with innodb_lock_timeout value)
[30 Jul 2005 22:52] Sergey Petrunya
sorry, "modifying some existing file" = "modifying server start options for some existing file that tests innodb"
[2 Aug 2005 8:40] Sergey Petrunya
Sorry was changing a wrong bug
[8 Aug 2005 11:00] Sergey Petrunya
Fix pushed into 5.0.12 tree
[12 Aug 2005 19:29] Paul DuBois
Note in 5.0.12 changelog.