MySQL Bugs: #9478: mysql_stmt_attr_set mysql_stmt

Bug #9478	mysql_stmt_attr_set mysql_stmt_execute
Submitted:	30 Mar 2005 8:43	Modified:	3 Jun 2005 22:58
Reporter:	Berto van de Kraats	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.4-beta-nightly-20050328	OS:	Linux (linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
mysql crashes the second time a prepared statement is executed if cursor type CURSOR_TYPE_READ_ONLY is used.

How to repeat:
compile/run attached c-program

bug4.c

Attachment: bug4.c (application/octet-stream, text), 3.99 KiB.

Thank you for the bug report.

 mysql_stmt_execute(), failed
stmt error=Lost connection to MySQL server during query
2013:Lost connection to MySQL server during query

I was able to repeat the bug.

According to our cursors design, you can't have more than one cursor open for a prepared statement. If you need several cursors, you need to prepare several prepared statements. 
The fix is going to return an error in reply to the attempt to execute a statement for which one already has a cursor open.

how can one close a cursor so that the statement can be executed again without doing a new prepare action ? (our goal is to execute the same query again with different  bind values)

mysql_stmt_free_result(stmt) and/or mysql_stmt_reset(stmt) do not seem to achieve that. And also according to your reply, also a new mysql_stmt_execute(stmt) does not seem to close the old cursor.

Peter, thank you very much for your feedback. Your problem is exactly the reason (besides the crash, of course), that the bug report is "In progress". 
I'm currently working on two issues:
- the server shouldn't crash, and on attempt to re-execute a statement with an open cursor
  one should get an error message
- mysql_stmt_reset should close the pending cursor if there is one, and prepare the statement
for reexecution.
So, the answer for your question is that there is a bug in mysql_stmt_reset which I'm fixing.
I'm happy that the lack of abiliy to have more than one open cursor for a prepared statement
did not cause you much trouble, it was an original design limitation we took in order to simplify the API and the implementation.

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/24804

To be compatible with non-cursors mode, mysql_stmt_execute now automatically closes
the cursor if there is one.

Pushed into 5.0 tree currently tagged 5.0.6

Noted in 5.0.6 changelog, and in the descriptions
of the various affected C API functions.