MySQL Bugs: #118372: mtr --cursor-protocol does not work for a real test

Bug #118372	mtr --cursor-protocol does not work for a real test
Submitted:	5 Jun 15:24	Modified:	6 Jun 3:51
Reporter:	Kaiwang CHen (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Tools: MTR / mysql-test-run	Severity:	S7 (Test Cases)
Version:	8.0, 8.4, 9.2	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
There is a cursor mode for prepared binary protocol, which automatically creates
a temporary table to save the query result for client fetches. It is popular in
certain apps. A C client app turns on cursor mode by:

  unsigned long type = CURSOR_TYPE_READ_ONLY;
  mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (void *)&type);

Although "mtr --cursor-protocol" is supposed to test cursor mode, but it does not
work for any real test which usually contains some DDLs.

How to repeat:
Given psc.test:

CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1);

SELECT 1 FROM t1;

DROP TABLE t1;

We have an error running "mtr --cursor-protocol psc"

mysqltest: At line 1: Query 'CREATE TABLE t1 (c1 INT)' failed.
ERROR 1210 (HY000): Incorrect arguments to with cursor

This error stops almost any real test.

Suggested fix:
Actually the cursor mode could be enabled only for DML statements, others
get ER_WRONG_ARGUMENTS with cursor. However, to really open a cursor,
the statement must return data. See sql_cmd->may_use_cursor(),
which limits to SELECT. If there is an opened cursor, the server
will respond with SERVER_STATUS_CURSOR_EXISTS in server_status.

So it is reasonable to only enable cursor mode for SELECT in mysqltest. A user
app gets the error for non-DML statements. A real cursor execution can be
observed by Created_tmp_tables.

Fix --cursor-protocol by only setting CURSOR_TYPE_READ_ONLY for SELECTs. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug_118372_psc.patch (application/octet-stream, text), 4.87 KiB.

Hello Kaiwang,

Thank you for the report and contribution.

regards,
Umesh