Bug #29948 autoreconnect + prepared statements + kill seems unstable
Submitted: 21 Jul 2007 18:26 Modified: 9 Oct 2007 0:38
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.0.46 OS:Any
Assigned to: Evgeny Potemkin
Tags: bfsm_2007_08_02

[21 Jul 2007 18:26] Shane Bester
Description:
Hi, I think this is a bug in both the client and server code.

When C code has this auto reconnect option enabled, and you have another process issueing KILL <id> prepared statements have strange errors.

Mostly, you get the expected errors:

thread [000] mysql_stmt_execute failed: Server shutdown in progress (1053)
thread [000] mysql_stmt_execute failed: Lost connection to MySQL server during query (2013)

But on occasion these happen:

thread [004] mysql_stmt_execute failed: Unknown prepared statement handler (1) given to mysql_stmt_execute (1243)

thread [001] mysql_stmt_execute failed: No data supplied for parameters in prepared statement (2031)

I'm quite sure a crash can happen on some scenarios.

How to repeat:
will attach example code later.

Suggested fix:
Is the statement 'unprepared' on the client when auto-reconnect happens?
[21 Jul 2007 18:39] Shane Bester
Interesting thing about that last error message is that it is completely bogus! There are 0 parameters in the query.  Simply, it's "SELECT * FROM `t1`" :)
[1 Aug 2007 13:23] Shane Bester
testcase

Attachment: bug29948.c (text/plain), 10.32 KiB.

[1 Aug 2007 13:40] Shane Bester
an update, and testcase:

I had got this in my 5.0.46BK and 5.0.48BK build:
mysql_stmt_execute failed: Unknown prepared statement handler (46) given to mysql_stmt_execute (1243)

Problem is that if mysql_stmt_prepare fails, I don't execute mysql_stmt_execute, so I'm not sure how this error is getting thrown.

sbester@www:~> gcc bug29948.c -g -o bug29948 -L/home/sbester/server/5.0/mysql-5.0.48-linux-i686/lib -I/home/sbester/server/5.0/mysql-5.0.48-linux-i686/include   -lmysqlclient_r -lz -lpthread

<snip>
mysql_stmt_prepare failed: Lost connection to MySQL server during query (2013)
mysql_stmt_execute failed: Unknown prepared statement handler (332) given to mysql_stmt_execute (1243)
query failed 'kill 99' : 1094 (Unknown thread id: 99)
kill failed
</snip>
[2 Aug 2007 0:34] Shane Bester
I think the cli_read_binary_rows crash occurs only when CURSOR_TYPE_READ_ONLY is used, instead of the default CURSOR_TYPE_NO_CURSOR.  Uploading a new testcase shortly.
[2 Aug 2007 0:52] Shane Bester
use CURSOR_TYPE_READ_ONLY instead of CURSOR_TYPE_NO_CURSOR to get segfaults

Attachment: bug29948_crash.c (text/plain), 10.20 KiB.

[14 Aug 2007 13:56] 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/commits/32509

ChangeSet@1.2498, 2007-08-14 17:28:51+04:00, evgen@moonbone.local +2 -0
  Bug#29948: Unchecked NULL pointer caused server crash.
  
  The cli_read_binary_rows function is used to fetch data from the server
  after a prepared statement execution. It accepts a statement handler and gets
  the connection handler from it. But when the auto-reconnect option is set
  the connection handler is reset to NULL after reconnection because the
  prepared statement is lost and the handler became useless. This case
  wasn't checked in the cli_read_binary_rows function and caused server crash.
  
  Now the cli_read_binary_rows function checks the connection handler to be
  not NULL and returns an error if it is.
[23 Aug 2007 17:46] 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/commits/32986

ChangeSet@1.2571, 2007-08-23 23:20:09+05:00, gshchepa@gleb.loc +2 -0
  Bug#29948: Unchecked NULL pointer caused server crash.
  Recommit to 5.1.22.
  
  The cli_read_binary_rows function is used to fetch data from the server
  after a prepared statement execution. It accepts a statement handler and gets
  the connection handler from it. But when the auto-reconnect option is set
  the connection handler is reset to NULL after reconnection because the
  prepared statement is lost and the handler became useless. This case
  wasn't checked in the cli_read_binary_rows function and caused server crash.
  
  Now the cli_read_binary_rows function checks the connection handler to be
  not NULL and returns an error if it is.
[23 Aug 2007 18:05] 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/commits/32987

ChangeSet@1.2571, 2007-08-23 23:38:43+05:00, gshchepa@gleb.loc +2 -0
  Bug#29948: Unchecked NULL pointer caused server crash.
  Recommit to 5.1.22.
  
  The cli_read_binary_rows function is used to fetch data from the server
  after a prepared statement execution. It accepts a statement handler and gets
  the connection handler from it. But when the auto-reconnect option is set
  the connection handler is reset to NULL after reconnection because the
  prepared statement is lost and the handler became useless. This case
  wasn't checked in the cli_read_binary_rows function and caused server crash.
  
  Now the cli_read_binary_rows function checks the connection handler to be
  not NULL and returns an error if it is.
[24 Aug 2007 7:20] Bugs System
Pushed into 5.1.22-beta
[24 Aug 2007 7:22] Bugs System
Pushed into 5.0.48
[31 Aug 2007 17:04] Paul Dubois
This was pushed to 5.1.23, not 5.1.22.
[9 Oct 2007 0:38] Paul Dubois
Noted in 5.0.48, 5.1.23 changelogs.

With auto-reconnect enabled, row fetching for a prepared statement
could crash after reconnect occurred because loss of the the
statement handler was not accounted for.