MySQL Bugs: #39878: On a net_write_timeout condition, fetching data With ODBC Returns No Error

Bug #39878	On a net_write_timeout condition, fetching data With ODBC Returns No Error
Submitted:	6 Oct 2008 11:38	Modified:	11 Feb 2011 11:00
Reporter:	Alain Charroux	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.26, 5.1	OS:	Windows
Assigned to:	Rafal Somla	CPU Architecture:	Any

Description:
using MyODBC to fetch data, when a net_write_timeout condition occurs,
fetch data simply returns the standard end of data status, without any indication of an error happened !

How to repeat:
Write a simple code that connect, use SQLExecuteDirect API to send a simple select, prepare your bindings to get data, start to fetch data.
Then after a few rows, do a sleep with a little more of the current value of net_write_timeout setup of your MySQL server (decrease it so it won't take too much time :)). Your next fetch will return SQL_NO_DATA, no error condition, even if there is zillions of rows still to retrieve.

This happens with a lot of versions of MyODBC driver, on Windows and Linux.

Obviously, sleep a long time between two fetches is just for demonstration purpose. There is some situations where between, two fetches, a complex computations occurs that may take a long time.

Hi Alain and thanks for your report.

Can you please take time and attach self-sufficient test case demonstrating this behaviour.

Source file to reproduce the issue and one output showing the err

Attachment: reproducingBug#39878.zip (application/x-zip-compressed, text), 5.59 KiB.

Hi
I have sent you a source file to reproduce the issue and one output.
The source file is a little ugly and contains many generic stuff and tools that I use to reproduce some issues with many other DBMS. Please go directly to the main and you will see what I actually use to reproduce the issue!!
Basically
1 I setup the net_write_timeout option to a low value in order to have a quick test
I create a table from scratch, fill it with 16386 rows.
Then I emit a SELECT *, prepare everything to get values and start to fetch data.
At row 8000, I do a sleep of 30 s just to simulate a complex and long operation (we are working in datamining algorithms and some computations may be long)
and then restart to fetch.
I receive then a NO_MORE_DATA BEFORE the 16384 rows !!!! and absolutely no error condition

IMPORTANT thing that I have forgotten is to force the 'Don't Cache result' option in the MyODBC driver !!!! Don't forget it !!!

I think also that the actual # of rows to put in table depends on server and client setup. There is probably a buffer filled somewhere that avoid to do true fetches all the time and size of this buffer may depend on actual configuration

the issue can be reproduced with Linux.

Alain, we are working on this, but in private conversation as there are some problems connector encounters from server side... Otherwise, your report is correct.

Quoting Jess: "The problem is that mysql_fetch_row() does not indicate a failure. Calling mysql_errno() is not reliable as 'net.last_errno' is not cleared by mysql_fetch_row() and may exist from a prior MySQL API call."

Jess, please reassign to someone if you will not work on this problem.

So this might be impossible to fix...

Similar thing reported in Bug#45978. Escalating to connectors team.

Changing the assignment to Lawrin.

Is there any movement on this?

Verified with Connector/ODBC 3.51.27 and 5.1.8

Hi
I am not familiar with this case management tool. Just to be sure, what is the meaning of 'verified' ? Is it fixed (and soonly closed) or have you 'just' (I say 'just' because as a developper I know how it can be painful to reproduce this kind of things) checked that the big is still active ?
Thx

Hi Alain,

The meaning of verified is that the bug is still active.
We have to deal with a big number of bug reports and check if new versions fix the old problems.

Our developers will be informed about of the tests results in order to start with the patch for this particular problem.

I was going to ask why I was not invited to its birthday party...

program to reproduce the problem

Attachment: bug39879.cpp (text/plain), 4.99 KiB.

again but with better formatting :P

Attachment: bug39879.cpp (text/plain), 5.02 KiB.

To run bug39878.cpp, register ODBC driver with Windows, run a MySQL server instance and edit the conn_str in the file accordingly (driver name, port number etc).

I could not repeat the issue using the attached bug39879.cpp program.

I was using c/ODBC 3.51.28 and a community MySQL server 5.1.53.

The client application fails to fetch all rows from the server only if the result was not buffered by the ODBC driver. In other words, when calling mysql_use_result() instead of mysql_store_result().

ODBC driver calls mysql_use_result() with forward-only cursors when caching is disabled.

So, the connection string in Rafal's test case should look similar to this:

"driver={MySQL ODBC 5.1 driver};user=*****;password=*****;port=3306;NO_CACHE=1;FORWARD_CURSOR=1";

I can reproduce the problem with no_cache option. For 3.51 driver I use connections string:

"....;OPTION=3145728"

I can reproduce it as follows:

1. CREATE TABLE t (a int) and insert 16K rows into it;
2. SET net_write_timeout=1;
3. Start statement "SELECT a FROM t";
4. wait > 1.5 sec;
5. Fetch rows from the SELECT statement (using SQLFetch())

SQLFetch() reports error after 9219 rows. Subsequent execution of SQL statement fails with "MySQL server has gone away" error (although server is running fine).

Actually SQLFetch() returns SQL_NO_DATA, i.e., it behaves as if there are no errors but the end of the result set has been reached.

REFINED PROBLEM DESCRIPTION
---------------------------
When a client fetches rows of a result set slowly and "don't cache result" option is enabled for the MyODBC driver, then server can kill connection due to a write timeout settable with net_write_timeout parameter. In this situation SQLFetch() function should return SQL_ERROR but it returns SQL_NO_DATA instead and client does not know that connection is gone.

The fact that connection is lost can be detected by reading connection attribute SQL_ATTR_CONNECTION_DEAD.

Note: on the libmysql level, in the above situation mysql_fetch_row() returns NULL (which normally indicates "no more rows") and mysql_errno() returns CR_SERVER_LOST.

PROPOSED SOLUTION
-----------------
SQLFetch() should check for lost connection after a call to mysql_fetch_row() and report error. To account for the fact that mysql_fetch_row() does not reset mysql_errno(), one can check for mysql_errno() before calling mysql_fetch_row():

if (mysql_errno(&stmt->dbc->mysql))
{
  // report error: can't fetch rows if connection in error state
  return SQL_ERROR;
}

// here we know that mysql_errno() is 0

row= mysql_fetch_row(stmt->result);

if (!row)
{
 int error= mysql_errno(&stmt->dbc->mysql);

 if (!error)
   return SQL_NO_DATA;

 // report error: connection error when fetching rows

 return SQL_ERROR; 
}

Note: It should be checked that such errors are detected by all ODBC functions which scan result stes (cursors?).

Implementation of the proposed fix.

Attachment: bug39878.diff (, text), 5.19 KiB.

Will there be a win32 MSI for the fixed version available for download?

Pushed to lp:myodbc/3.51, revid:rafal.somla@oracle.com-20110204165731-8nfoq9cq2r2ybswd

Pushed to lp:myodbc/5.1, revid:rafal.somla@oracle.com-20110204175707-3xsdlfjj63v3ksaf

An entry has been added to the 5.1.9 and 3.51.28 changelogs:

When using MySQL Connector/ODBC to fetch data, if a net_write_timeout condition occurred, the operation returned the standard "end of data" status, rather than an error.

there is no 5.1.9 release on dev.mysql.com ...

Shane, it is pushed but has not been released yet.