MySQL Bugs: #12082: Prepared statement "FOR UPDATE" "LOCK IN SHARE MODE": timeout gives server crash

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

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).

Trace of problem

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

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

Hmm... since you are using prepared statements, this more probably is a bug in them.

I am updating the synopsis.

--Heikki

Peter, thanks for your excellent bug report.

I was able to reproduce this using a recent bk pull of our 5.0 tree

Jorge,

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

(gdb) bt full

Regards,

Heikki

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

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)

sorry, "modifying some existing file" = "modifying server start options for some existing file that tests innodb"

Sorry was changing a wrong bug

Fix pushed into 5.0.12 tree

Note in 5.0.12 changelog.