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

[6 Dec 2005 14:15] Magnus Blåudd
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.
[3 Aug 2006 8:57] Magnus Blåudd
It's a little tricky to find the exact place when to reset the errno variables again after a failed prepare.
[3 Aug 2006 8:58] Magnus Blåudd
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.
[16 Aug 2006 10:37] Konstantin Osipov
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);
[22 Aug 2006 9:00] Magnus Blåudd
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)
[24 Aug 2006 9:05] Konstantin Osipov
Interesting. Then there must be a bug somewhere - in my patch or in the code.
[1 Dec 2006 13:58] 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/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.
[1 Dec 2006 15:06] Konstantin Osipov
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.
[16 Jan 2007 12:39] 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/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.
[19 Jan 2007 10:07] 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/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
[3 Feb 2007 2:07] Paul DuBois
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.