Bug #80026 "The statement (1) has no open cursor" pops sometimes with prepared+query_cache

• Submitted: 18 Jan 2016 9:29
• Modified: 31 May 2016 14:41
• Reporter: Andrii Nikitin
• Status: Closed
• Category: MySQL Server: Query Cache
• Severity: S2 (Serious)
• Version: 5.6.27
• OS: Any
• CPU Architecture: Any

[18 Jan 2016 9:29] Andrii Nikitin

Description:
Error 1421 "The statement (1) has no open cursor" is shown sometimes unexpectedly for prepared statement on complex conditions:
- query cache is enabled
- CURSOR_TYPE_READ_ONLY sometimes is used with the same SQL command

If the problem doesn't occur - normally a regular insert into the table will trigger it for further SELECTs

How to repeat:
1. make sure query cache is enabled
2. execute SQL commands:
use test;
create table t(a datetime);
insert into t values(now());
3. download test program a.cpp , change connection details
4. compile a.cpp , e.g. :
g++ a.cpp libmysqlclient.a -ldl  -lpthread
5. run the program, observe the error for second execution:
>./a.out                                                                        mysql_stmt_prepare(), SELECT successful
test successfully, cursor type=1
mysql_stmt_prepare(), SELECT successful
mysql_stmt_store_result() failed
The statement (1) has no open cursor.

Recreating table may temporarily solve the problem, but inserting another row should trigger it back.

Suggested fix:
Not sure, either document if client program is incorrect or make sure that no error is reported.

[18 Jan 2016 9:31] Andrii Nikitin

testcase

Attachment: a.cpp (text/x-c++src), 3.60 KiB.

[31 May 2016 14:41] Paul DuBois

Posted by developer:
 
Noted in 5.6.32, 5.7.14 changelogs.

With the query cache enabled, executing a prepared statement with
CURSOR_TYPE_READ_ONLY and then again with CURSOR_TYPE_NO_CURSOR
caused the server to return an error.