MySQL Bugs: #15518: Reusing a stmt that has failed during prepare does not clear error

Bug #15518	Reusing a stmt that has failed during prepare does not clear error
Submitted:	6 Dec 2005 14:15	Modified:	3 Feb 2007 2:07
Reporter:	Magnus Blåudd	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.0.17	OS:
Assigned to:	Magnus Blåudd	CPU Architecture:	Any

Description:
init a statment and use it for a mysql_stmt prepare that fails. The error code returned from mysql_stmt_errno is correct.

Then use the same stmt to prepare and execute another query that suceeds. The error code returned from mysql_stmt_errno is still the same.

How to repeat:
Run below queries in new version of mysqltest....

--error 1054
select friedrich from (select 1 as otto) as t1;

select otto from (select 1 as otto) as t1;
eval select $mysql_errno as "after_successful_stmt_errno" ;

The output will be:
+ select friedrich from (select 1 as otto) as t1;
+ ERROR 42S22: Unknown column 'friedrich' in 'field list'
+ select otto from (select 1 as otto) as t1;
+ otto
+ 1
+ select 1054 as "after_successful_stmt_errno" ;
+ after_successful_stmt_errno
+ 1054

But should be:
+ select friedrich from (select 1 as otto) as t1;
+ ERROR 42S22: Unknown column 'friedrich' in 'field list'
+ select otto from (select 1 as otto) as t1;
+ otto
+ 1
+ select 0 as "after_successful_stmt_errno" ;
+ after_successful_stmt_errno
+ 0

Suggested fix:
Make stmt.last_errno be reset when mysql_stmt_prepare is called on a failed query. Or document in manual that stmt need to be closed.

It's a little tricky to find the exact place when to reset the errno variables again after a failed prepare.

But I guess a good start wouold be to produce a better error message when one tries to re-prepare a statement that has failed previous prepare.

Hello Magnus,
Sorry for a long delay in reply.

Please use the following patch, add a test case to mysql_client_test.c and submit a patch.
Thank you!

===== libmysql.c 1.247 vs edited =====
--- 1.247/libmysql/libmysql.c	2006-08-16 14:34:23 +04:00
+++ edited/libmysql.c	2006-08-16 14:28:40 +04:00
@@ -2038,6 +2038,12 @@ mysql_stmt_prepare(MYSQL_STMT *stmt, con
     DBUG_RETURN(1);
   }
 
+  /*
+    Reset the last error in any case: that would clear the statement
+    if the previous prepare failed.
+  */
+  stmt->last_errno= 0;
+  stmt->last_error[0]= '\0';
   if ((int) stmt->state > (int) MYSQL_STMT_INIT_DONE)
   {
     /* This is second prepare with another statement */
@@ -2051,8 +2057,6 @@ mysql_stmt_prepare(MYSQL_STMT *stmt, con
     */
     stmt->bind_param_done= stmt->bind_result_done= FALSE;
     stmt->param_count= stmt->field_count= 0;
-    stmt->last_errno= 0;
-    stmt->last_error[0]= '\0';
     free_root(&stmt->mem_root, MYF(MY_KEEP_PREALLOC));
 
     int4store(buff, stmt->stmt_id);

Applied patch and tested with mysqltest by removing the "hack" that closes the statement everytime it fails. 

It need some more debugging as there are some problems still, but that could be in mysqltest's usage of mysql_stmt_close?

Errors are (from /home/msvensson/mysql/my50-m-bug15518/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 52: query 'select 3' failed: 1243: Unknown prepared statement handler (3) given to mysql_stmt_close
skipped 52 bytes from file: socket (4)

Interesting. Then there must be a bug somewhere - in my patch or in the code.

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

ChangeSet@1.2352, 2006-12-01 14:58:25+01:00, msvensson@neptunus.(none) +3 -0
  Bug#15518 Reusing a stmt that has failed during prepare does not clear error
   - Always reset error when calling mysql_stmt_prepare a second time
   - Don't sned COM_STMT_CLOSE to server it conection has been lost.
   - Add test to mysql_client_test
   - Remove mysql_stmt_close in mysqltest after each query
   - Close all open statements in mysqltest if disable_ps_protocol is called.

Magnus, the patch is ok to push except that I didn't formally review mysqltest.c changes.
Please ask for mysqltest.c code review the person/team that owns it.

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

ChangeSet@1.2352, 2007-01-16 13:39:42+01:00, msvensson@neptunus.(none) +3 -0
  Bug#15518 Reusing a stmt that has failed during prepare does not clear error
   - Always reset error when calling mysql_stmt_prepare a second time
   - Set stmt->state to MYSQL_STMT_INIT_DONE before closing prepared stmt in server.
   - Add test to mysql_client_test
   - Remove mysql_stmt_close in mysqltest after each query
   - Close all open statements in mysqltest if disable_ps_protocol is called.

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

ChangeSet@1.2371, 2007-01-19 11:07:20+01:00, msvensson@pilot.mysql.com +1 -0
  Bug #15518 Reusing a stmt that has failed during prepare does not clear error
   - Additional patch removing check for mysql_errno on already closed
     mysql1

Noted in 5.0.36, 5.1.15 changelogs.

When a prepared statement failed during the prepare operation, the
error code was not cleared when it was reused, even if the subsequent
use was successful.