Bug #62657 A failure on one stmt causes another stmt to fail
Submitted: 7 Oct 2011 18:22 Modified: 18 Apr 2012 23:26
Reporter: John Water Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.9 OS:Microsoft Windows (windows 7)
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: ODBC, ODBC driver, regression, Stmt failure

[7 Oct 2011 18:22] John Water
Description:
When a SQL error occurred on one statement, another active statement will also fail.  Here is the pseudo code:

    char *	select =
	"select specific_name from information_schema.routines "
	" where routine_type = 'FUNCTION' and LOWER ( specific_name ) like 'my_func%'";

    if !exists my_func_1 {
	create function my_func_1;
    }
    if !exists my_func_2 {
	create function my_func_2;
    }
    commit;

    SQLAllocHandle( SQL_HANDLE_STMT, dbc, $stmt1 );
    SQLPrepare( stmt_1, select, SQL_NTS );
    SQLBindCol( stmt_1, 1, SQL_C_CHAR, name, ... );
    SQLExecute( stmt_1 );
    while( TRUE ) {
        ret = SQLFetch( stmt_1 );
        if ret == SQL_SUCCEED {
            // execute drop procedure to cause an error
	    SQLAllocHandle( SQL_HANDLE_STMT, dbc, $stmt2 );
	    SQLPrepare( stmt_2, "drop procedure my_func_1", SQL_NTS );
	    SQLExecute( stmt_2 );
	    SQLFreeHandle( stmt_2 );
        } else {
            break;
        }
    }
    SQLFreeHandle( stmt_1 );
    
Calling SQLFetch in the second time will return SQL_ERROR, when the MySQL ODBC driver 5.1.9 for Windows is used.  If you run the same pseudo code against MySQL using the ODBC driver 5.1.6, you can fetch out all two fucntions.

    

How to repeat:
A repro will be attached: the zip file contains
     odbcbug.exe - a 64-bit executable for Windows
     odbcbug.c   - the source code used to build odbcbug.exe
     output.5.1.6 - an output file generated by odbcbug.exe with the MySQL ODBC driver 5.1.6
     output.5.1.9 - an output file generated by odbcbug.exe with the MySQL ODBC driver 5.1.9
Here is the command line to run the repro:
     odbcbug.exe "dsn=...;uid=...;pwd=..."
[7 Oct 2011 18:44] Lawrenty Novitsky
John, thank you for your report.
One quick function - what about 5.1.7-8 drivers? they do not show similar behavior?  I mean is it introduced with 5.1.9 or with earlier version?
[10 Oct 2011 18:52] Lawrenty Novitsky
Verified as described. If before all rows of the result of one query fetched, and issue another query on the same connection and it fails, next fetch of the 1st query results will fail.
[10 Oct 2011 20:30] Rafal Somla
REFINED PROBLEM DESCRIPTION
===========================

a) Before and during fetching rows, function SQLFetch() checks for connection errors with a call to function handle_connection_error(stmt) (line 1503 in results.c). But function handle_connection_error(stmt) also reports non-connection errors, like these caused by another statement executed in the same connection.

b) Because of error checks inside SQLFetch() this function will fail as soon as error is detected, even if there are more rows of the result set available. One can expect that SQLFetch() returns all available rows before reporting an error.

SUGGESTED SOLUTION
==================

a) Narrow error checks inside SQLFetch() (actually my_SQLExtendedFetch()) to detect only connection related errors.

b) Do error checks inside SQLFetch() only when there is no more data in the result set, i.e., when mysql_fetch_row() returns NULL.
[12 Oct 2011 12:43] Lawrenty Novitsky
The testcase for the bug has been pushed in rev#1016. my_results:t_bug62657
[13 Oct 2011 12:37] Lawrenty Novitsky
The Patch

Attachment: bug62657.diff (application/octet-stream, text), 834 bytes.

[26 Oct 2011 15:24] Lawrenty Novitsky
Patch v2

Attachment: bug62657v2.diff (application/octet-stream, text), 6.46 KiB.

[18 Apr 2012 23:26] John Russell
Added to changelog for 5.1.10, 3.51.30: 
 
 A failure on one statement causes another statement to fail.