MySQL Bugs: #29948: autoreconnect + prepared statements + kill seems unstable

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	CPU Architecture:	Any
Tags:	bfsm_2007_08_02

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?

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`" :)

testcase

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

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>

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.

use CURSOR_TYPE_READ_ONLY instead of CURSOR_TYPE_NO_CURSOR to get segfaults

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

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.

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.

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.

Pushed into 5.1.22-beta

Pushed into 5.0.48

This was pushed to 5.1.23, not 5.1.22.

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.