MySQL Bugs: #69031: Connection losses are not detected by queries with "forward-only" result sets

Bug #69031	Connection losses are not detected by queries with "forward-only" result sets
Submitted:	22 Apr 2013 12:58	Modified:	27 Oct 2014 17:08
Reporter:	Dario Andreani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / C++	Severity:	S3 (Non-critical)
Version:	1.1.3	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When the result set type is set to TYPE_FORWARD_ONLY, Statement::executeQuery() returns almost immediately, even for huge result sets, which is the intended behavior. If the connection with the server fails before all the data has been sent, ResultSet::next() simply returns false, like if no other row was available.

In other words, when ResultSet::next() returns false, there appears to be no way for the application to find out if the data was really finished or if some problem occurred.

Theoretically, it is possible to check the connection using Connection::isClosed() every time ResultSet::next() returns false, but it looks more like a workaround, because isClosed() is not exactly lightweight (it attempts a ping to the server). Besides, it's not clear if there may be other conditions which are currently undetected and which don't cause the connection to fall, rendering the workaround ineffective.

How to repeat:
sql::Driver* driver = get_driver_instance();
sql::Connection* pCon(NULL);
sql::Statement* pStat(NULL);
sql::ResultSet* pRes(NULL);
try
{
    pCon = driver->connect(<server>,<username>,<password>);
    pStat = pCon->createStatement();
    pStat->setResultSetType(sql::ResultSet::TYPE_FORWARD_ONLY);
    pRes = pStat->executeQuery(<query with lots of results>);
    /* 
     * cause the connection to the server to fall when the program is inside this while loop
     * for example by closing the sql server or with a specific tool (like Sysinternals TCPView)
     */
    while (pRes->next())
    {
        <get and process data from the row>
    }
    /* 
     * next() returns false and the application has no hint of what happened
     */
}
catch (sql::SQLException &e)
{
    <handle the exception>
}
<cleanup>

Suggested fix:
ResultSet::next() should throw an exception; alternatively any problem occurred should be retrievable with some other method.

Thank you Dario for your report.
I can see the problem here.

I encountered upon the same bug with JDBC API on Windows7 64-bit machine (both client and server)
Versions:
Database version 5.1.68
Driver version 5.1.26

Posted by developer:
 
Committed in rev#984.
Added checks in MySQL_ResultSet::next() and MySQL_Prepared_ResultSet::next() to throw exception when connection is lost.

Noted in 1.1.5 changelog.

With the result set type set to TYPE_FORWARD_ONLY,
Statement::executeQuery() returns almost immediately, but
MySQL_ResultSet::next() and MySQL_Prepared_ResultSet::next() returned
false if the connection was lost rather than throwing an exception,
making it impossible to distinguish loss of connection from normal
end of the result set. MySQL_ResultSet::next() and
MySQL_Prepared_ResultSet::next() now throw an exception when the
connection is lost.