MySQL Bugs: #74979: Server query cache vs protocol issue

Bug #74979	Server query cache vs protocol issue
Submitted:	24 Nov 2014 12:19	Modified:	7 Jul 2017 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:		CPU Architecture:	Any

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.

Thank you for the report.

Please provide Java code, demonstrating the problem.

test cursor

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

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.

Thank you for the feedback.

Verified as described in last comment: since I enabled query cache all threads stack.

MySQL will no longer invest in the query cache, see:

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