MySQL Bugs: #62271: With FLAG_NO_CACHE, SQLFetch doesn't report errors correctly

Bug #62271	With FLAG_NO_CACHE, SQLFetch doesn't report errors correctly
Submitted:	26 Aug 2011 19:32	Modified:	26 Sep 2011 3:31
Reporter:	Craig Holmquist	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1.8	OS:	Windows (XP SP3)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
When using FLAG_NO_CACHE, SQLFetch just returns SQL_NO_DATA when there is an error such as a lost connection.  This means the caller has no way to distinguish an error during the fetch from the end of the resultset.

How to repeat:
The attached testfetch.py script requires pyodbc.  Update the connection string as necessary.

1. Run testfetch.sql to create the test database
2. Run testfetch.py
3. testfetch.py will wait five seconds between fetches (this is just to simulate a long operation).  After it has retrieved one or two rows, forcibly close the connection (ie, kill the server or use a program like TCPView).

Expected result:
SQLFetch returns an error, which causes pyodbc to throw an exception

Actual result:
SQLFetch returns SQL_NO_DATA and the script execution proceeds as though all rows were fetched

Suggested fix:
I believe this is happening because my_SQLExtendedFetch doesn't check for an error if mysql_fetch_row returns NULL; it just assumes there are no more rows available.  This assumption is fine without FLAG_NO_CACHE because in that case mysql_fetch_row only returns NULL if there are no more rows.

SQL to create test database

Attachment: testfetch.sql (text/plain), 425 bytes.

Python script to test bug

Attachment: testfetch.py (text/plain), 420 bytes.

Craig,

Thanks for reporting the bug.
Can you also provide the ODBC trace corresponding to the error?
ODBC tracing can be enabled through ODBC Data Source Administrator -> Tracing -> Start Trace Now.

ODBC trace

Attachment: SQL.LOG (text/plain), 34.41 KiB.

Craig,

Thanks for the ODBC trace. 
As far as I can see, you reported the incorrect version of Connector/ODBC for this bug (5.1.8 instead of 3.51):

testfetch       1180-1188	EXIT  SQLGetInfoW  with return code 0 (SQL_SUCCESS)
		HDBC                009B1830
		UWORD                       77 <SQL_DRIVER_ODBC_VER>
		PTR                 0x009B1CD8 [      10] "03.51"
		SWORD                       40 
		SWORD *             0x0021FC70 (10)

My test case worked well for the version 5.1.8.
Checking 3.51.28.

I tried with Connector/ODBC 3.51.28 and the result was the same:

the driver reports the error, but it is able to get one more pre-buffered row even after the connection is killed:

[1] Fetched 50000 characters
[2] Fetched 50000 characters
[3] Fetched 50000 characters
[4] Fetched 50000 characters
KILL THE CONNECTION
[5] Fetched 50000 characters
 Error: [MySQL][ODBC 5.1 Driver][mysqld-5.5.14-log]Lost connection to MySQL server during query

As you see, the error was reported correctly and the status is SQL_ERROR (-1).

Can you try running the C test case uploaded in my next post.
Please edit the connection string (line 48) and let me know what the program prints.

C test case

Attachment: bug62271.c (text/plain), 3.82 KiB.

If you look at the SQL.LOG file I uploaded this string appears several times:

DIAG [01004] [MySQL][ODBC 5.1 Driver][mysqld-5.0.87-community-nt]

bug62271.c results for me:

[1] Fetched 50000 characters
[2] Fetched 50000 characters
[3] Fetched 50000 characters
[4] Fetched 50000 characters
KILL THE CONNECTION
[5] Fetched 50000 characters
[6] Fetched 50000 characters
[7] Fetched 50000 characters
NO DATA .. .. ..

To elaborate on my earlier comment, the "3.51" mention in SQL.LOG is SQL_DRIVER_ODBC_VER, which is the supported ODBC version, NOT the driver version.

Craig,

Thanks for your comments. Sorry for the mistake with the driver version. 

I conducted few more tests and found that the problem has been fixed in the latest revisions of MySQL Connector/ODBC sources.
That is why my test results were different from yours. 

You can try the non-release win32 test build, which I used to verify the current bug (zip archive had to be split in order to satisfy the requirement form maximum file size - 500K per file). This build is only provided for testing purposes and is not supposed to be used in production systems.

Another option would be to build the driver from the sources that are available for public access here:

https://launchpad.net/myodbc

test build part 1

Attachment: myodbc5-win32-r984.dll.zip.001 (application/octet-stream, text), 488.28 KiB.

test build part 2

Attachment: myodbc5-win32-r984.dll.zip.002 (application/octet-stream, text), 391.33 KiB.

Thank you for providing the updated driver.  With the r984 build it seems to have the correct behavior:

[1] Fetched 50000 characters
[2] Fetched 50000 characters
[3] Fetched 50000 characters
[4] Fetched 50000 characters
KILL THE CONNECTION
[5] Fetched 50000 characters
[6] Fetched 50000 characters
[7] Fetched 50000 characters
 Error: [MySQL][ODBC 5.1 Driver][mysqld-5.0.87-community-nt]Lost connection to MySQL server during query

and my original test script also correctly indicates an error:

10 rows to fetch
Row #1 fetched
Row #2 fetched
Row #3 fetched
Traceback (most recent call last):
  File "C:\Projects\testfetch.py", line 15, in <module>

    for r in c:
pyodbc.Error: ('08S01', '[08S01] [MySQL][ODBC 5.1 Driver][mysqld-5.0.87-community-nt]Lost connection to MySQL server during query (2013) (SQLFetch)')

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
The source revision number: 984.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html