Bug #74979 Server query cache vs protocol issue
Submitted: 24 Nov 2014 12:19 Modified: 7 Jul 9:08
Reporter: Filipe Silva Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S2 (Serious)
Version:5.7.4, 5.6.21, 5.5.40, 5.1.73 OS:Any
Assigned to:
Triage: Needs Triage: D2 (Serious)

[24 Nov 2014 12:19] Filipe Silva
Description:
Hi,

Combining query cache, server side prepared statements and cursor fetching causes an error when two successive calls of the same query are made while requesting for a cursor in the first call and not requesting it in the second call.

This behavior was first observed when using Connector/J as client(reported as Bug#74739 and others at least since 2006) but it should happen in any case where this feature is used through MySQL protocol. 

The problem was confirmed to occur either using the same connection/transaction or different connections/transactions.

I'm adding a protocol stack trace for more details.

How to repeat:
Preparations:
- setup query cache in server.
- query to execute "SELECT id FROM tbl".

Step one:
- Prepare statement "SELECT id FROM tbl" -> returns Stmt_ID := 1;
- Execute Stmt_ID 1, requesting a READ-ONLY cursor -> returns metadata and signals that cursor exists.
- Fetch n rows from Stmt_ID 1 cursor -> returns data until n rows.
- Close Stmt_ID 1 -> OK.

Step two:
- Prepare statement "SELECT id FROM tbl" -> returns Stmt_ID := 2;
- Execute Stmt_ID 2, requesting NO_CURSOR -> returns metadata and *signals that cursor exists*.
- Fetch n rows from Stmt_ID 2 cursor -> returns error stating that Stmt_ID 2 doesn't have an open cursor.

Suggested fix:
Comply with execution request from second call, returning immediately the data and not a cursor.

The alternative of returning a valid cursor after the second call could be viable, however, in this case, the server would be overriding a direct instruction from the client. It doesn't look right.
[19 Dec 2014 22:18] Sveta Smirnova
Thank you for the report.

Please provide Java code, demonstrating the problem.
[20 Dec 2014 3:08] Yves Gillet
test cursor

Attachment: MysqlJdbcTest.java (application/octet-stream, text), 2.62 KiB.

[20 Dec 2014 3:08] Yves Gillet
Hi 

you can use the test I provided for bug #74739.
To use it, ensure the query cache is enabled on server then run the test, the thread will be stuck.
Regards
Yves Gillet.
[15 Jan 2015 20:23] Sveta Smirnova
Thank you for the feedback.

Verified as described in last comment: since I enabled query cache all threads stack.
[7 Jul 9:08] Erlend Dahl
MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/