We support materialized cursors (at cursor OPEN time all rows from the SELECT are retrieved and stored into a temp table). This obeys the requirement that FETCH always returns the row as it was at OPEN time, but is too slow for SAP R/3 (too much disk read/writes at OPEN). An alternative has been developed by Konstantin Osipov and Innobase: non-materialized, consistent-read cursors: at OPEN time nothing notable happens, and at FETCH time the engine (InnoDB) uses its versioning to show the row as it was at the moment of OPEN. This alternative is currently disabled in release builds and MySQL AB developers' builds; to enable it one needs to patch sql/ a bit. It is so not tested at all in the tests included in our server code (mysql-test) (it has been tested by the SAP team). This runs the risk of seeing the feature silently degrade (bugs introduced and not caught by testing), which will require debugging when we later want to test this feature with Falcon and Maria (Falcon and Maria will need this feature, because R/3 will need it). The proposed task is to enable this feature in all builds (release builds too), and document it. Enabling, has risks: - receiving bug reports and having to fix them - giving InnoDB a competitive edge as it is currently the only engine to have support for it (i.e. to have a create_cursor_read_view in its handlerton). To enable it, we need: - to allow the user to choose, through the prepared statement API, between a materialized cursor and a possibly (if engine allows) non-materialized one; one proposal is to have mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, CURSOR_OPTIMIZE_FOR_FIRST_ROW); /* triggers non-materialized cursors */ CURSOR_OPTIMIZE_FOR_ALL_ROWS /* triggers materialized cursor, is the default */ (but these names are to be discussed, could also be CURSOR_ALWAYS_MATERIALIZE vs CURSOR_TRY_NOT_MATERIALIZE, or something else). To make it easy for a customer to easily switch his application between materialized and non-materialized without recompiling his application (examples: to test non-materialized cursors, also to be used by consultants to debug performance or to debug wrong results), there could be a global+session variable @@default_cursor_type (default: materialized), then the existing type CURSOR_TYPE_READ_ONLY could pick the value of @@session.default_cursor_type, while the two other types (CURSOR_OPTIMIZE*) would force a type. - to create a test for this feature (Guilhem has an ugly test which he would need to clean up). One may is to modify mysql_client_test, but Guilhem would prefer to add "open_cursor", "fetch_cursor", "close_cursor" commands to mysqltest and have a test in mysql-test/t, that would allow reusing of the capabilities of mysqltest (comparison of output to expected result, expectable errors, multiple connections, all things doable in mysql_client_test but with much more work); Konstantin seems to favor the mysqltest way too. The test scenarios should first be the requirements from SAP listed in the HLS of WL#1107 "Server side cursors". The tests should be done in READ COMMITTED (default level of R/3) and in REPEATABLE READ; for example for the test labelled "H" there, it could read in a .test file: connection con1; drop table if exists t1; create table t1 (a int, val int); insert into t1 (a) values (1,"one"), (2,"two"); set session.default_cursor_type=CURSOR_OPTIMIZE_FOR_FIRST_ROW; open_cursor c select * from t1; update t1 set val="three" where a=2; delete from t1 where a=1; fetch_cursor c; fetch_cursor c; open_cursor c2 select * from t1; fetch_cursor c2; fetch_cursor c2; We can have a wrapper around this test, which just sets session.default_cursor_type and includes the real test, this way we can easily compare results of materialized and non-materialized. In the wrapper we can also set the transaction isolation level as it is a relevant parameter. Other subtasks: - class Sensitive_cursor is in fact an insensitive non-materialized cursor (as Guilhem has tested), rename this class. - tests should have a way to make sure that their cursor is not materialized (otherwise, if by mistake someone makes a change to the server code and causes all cursors to be materialized, the tests will not notice it and it will appear just as a performance issue later). One way is to maintain counts of materialized/non-materialized cursors in SHOW STATUS, this can also be used by our Services team to troubleshoot performance. Another way is to add a property to the statement object in the MySQL API, which would tell if the cursor is materialized or not.
If we go with mysqltest's extensions, it could look like this: OPEN_CURSOR <cursor_name> <SELECT> check that this is a SELECT (or assume this is one). Set up a variable for this statement, to say "use ps and don't fetch". Then run_query_stmt() will be called. Inside run_query_stmt(), execute the stmt but skip all code which retrieves the result from the server (like mysql_stmt_store_result() etc); instead, save "stmt" (cur_con->stmt) into a new member of "struct connection" name for example "list_of_cursors": "list_of_cursors" is a list of pairs cursor_name/stmt. Don't free this stmt, rather set cur_con->stmt=NULL (to force a new stmt to be created for next query). FETCH_CURSOR <cursor_name>: error if no such cursor in cur_con's list. Otherwise fetch a row (mysql_stmt_fetch()) and append it to the result string. CLOSE_CURSOR <cursor_name>: error if no such cursor in cur_con's list. Otherwise mysql_stmt_close(), free this statement etc. Note: beware of BUG#15518 if the statement has an error.