MySQL Bugs: #68437: Result set return previous result after one time out exception

Bug #68437	Result set return previous result after one time out exception
Submitted:	20 Feb 2013 2:18	Modified:	27 Feb 2014 7:05
Reporter:	Myra Wei	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.14	OS:	Linux (2.6.18-128.el5)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
We have the JDBC connections to mysql server with parameter socketTimeout=30000 set.

In our system, one table was locked already for some reason. And at this time, we have a write operation to this table, but obviously it would be timed out.

Then this connection is used for other query , it will also be blocked here since the lock is not released. Before this query timed out, the lock was released. This query returned, but when we try to read the Resultset, we get error: ResultSet is from UPDATE. No Data.

If we are still using this connection for another similar query, and the Resultset is always presented as previous query result instead of the latest one.

How to repeat:
Mysql Server version: 5.1.31
JDBC connection: mysql-connector-java-3.1.12

1. Lock the database with "flush tables with read lock" withe one local mysql connection (conn1).

2. get another JDBC connection(conn2) to the server (with parameter socketTimeout=30000 and ).

3. Use conn2 to insert one record to one table, this operation will time out due to the database lock.

4. Use conn2 to query "show table status from lps like 'user%';", and before this query return, conn1 release the lock. This time we will get error: ResultSet is from UPDATE. No Data.

5. Use conn2 to do another query "show table status from lps like 'call%'", the Resultset is from the previous "show table status from lps like 'user%'".

I redo the test on mysql 5.5.23, and will got the same result.

And from my testing, if one read operation timed out, the connection will be closed automatically, there will be no such issue. 

But if one write operation timed out, the connection will not be closed automatically, we can find the issue happen. And if the lock is released before the same connection used for second query, there will be no issue. Only during the second query on same connection the lock is released, this issue happen.

Thank you for the report.

Do you free result after first timeout? If not, this is not a bug.

Thanks a lot for your reply.

The first operation is one insert operation; we did close the statement. and we also close the resultset and statement on each subsequent read operation.

And please note that, the "flush tables with read lock" was executed from local mysql before the testing code run, and the "unlock tables" was executed after "showActiveTables------------"

Hi, do you have any further comments about this issue?

Thanks a lot!
Myra

Thank you for the feedback.

I can repeat error "ResultSet is from UPDATE. No Data." with version 3.1.14, but can not repeat wrong results of other queries.

I also can not repeat this bug with current version 5.1.22.

I still mark this report as "Verified", but I strongly recommend you to upgrade to version 5.1.22, because 3.1 series are obsolete.

Thanks a lot for your reply. I downloaded the 5.1.23 Connector, and there is no such issue.

With the 3.1.12 version, about the wrong query result, it only occurs when the latter query is similar to the previous query. 

Like in my test case: all queries are "show table status like ", and we only retrieve the table name from result set. If the later query changed to "show variables", and we tried to retrieve the variable name, there will be no issue.

Thank you Myra, Sveta!
I close this report as "Can't repeat" because the error reported doesn't appear with c/J 5.1 we currently maintain, and we don't support c/J 3.1 version.