This patch adds support for SYS_REFCURSOR (a weakly typed cursor)
for both sql_mode=ORACLE and sql_mode=DEFAULT.
Works as a regular stored routine variable, parameter and return value:
- can be passed as an IN parameter to stored functions and procedures
- can be passed as an INOUT and OUT parameter to stored procedures
- can be returned from a stored function
Note, strongly typed REF CURSOR will be added separately.
Note, to maintain dependencies easier, some parts of sql_class.h
and item.h were moved to new header files:
- select_results.h:
class select_result_sink
class select_result
class select_result_interceptor
- sp_cursor.h:
class sp_cursor_statistics
class sp_cursor
- sp_rcontext_handler.h
class Sp_rcontext_handler and its descendants
The implementation consists of the following parts:
- A new class sp_cursor_array deriving from Dynamic_array
- A new class Statement_rcontext which contains data shared
between sub-statements of a compound statement.
It has a member m_statement_cursors of the sp_cursor_array data type,
as well as open cursor counter. THD inherits from Statement_rcontext.
- A new data type handler Type_handler_sys_refcursor in plugins/type_cursor/
It is designed to store uint16 references -
positions of the cursor in THD::m_statement_cursors.
- Type_handler_sys_refcursor suppresses some derived numeric features.
When a SYS_REFCURSOR variable is used as an integer an error is raised.
- A new abstract class sp_instr_fetch_cursor. It's needed to share
the common code between "OPEN cur" (for static cursors) and
"OPER cur FOR stmt" (for SYS_REFCURSORs).
- New sp_instr classes:
* sp_instr_copen_by_ref - OPEN sys_ref_curor FOR stmt;
* sp_instr_cfetch_by_ref - FETCH sys_ref_cursor INTO targets;
* sp_instr_cclose_by_ref - CLOSE sys_ref_cursor;
* sp_instr_destruct_variable - to destruct SYS_REFCURSOR variables when
the execution goes out of the BEGIN..END block
where SYS_REFCURSOR variables are declared.
- New methods in LEX:
* sp_open_cursor_for_stmt - handles "OPEN sys_ref_cursor FOR stmt".
* sp_add_instr_fetch_cursor - "FETCH cur INTO targets" for both
static cursors and SYS_REFCURSORs.
* sp_close - handles "CLOSE cur" both for static cursors and SYS_REFCURSORs.
- Changes in cursor functions to handle both static cursors and SYS_REFCURSORs:
* Item_func_cursor_isopen
* Item_func_cursor_found
* Item_func_cursor_notfound
* Item_func_cursor_rowcount
- A new system variable @@max_open_cursors - to limit the number
of cursors (static and SYS_REFCURSORs) opened at the same time.
Its allowed range is [0-65536], with 50 by default.
- A new virtual method Type_handler::can_return_bool() telling
if calling item->val_bool() is allowed for Items of this data type,
or if otherwise the "Illegal parameter for operation" error should be raised
at fix_fields() time.
- New methods in Sp_rcontext_handler:
* get_cursor()
* get_cursor_by_ref()
- A new class Sp_rcontext_handler_statement to handle top level statement
wide cursors which are shared by all substatements.
- A new virtual method expr_event_handler() in classes Item and Field.
It's needed to close (and make available for a new OPEN)
unused THD::m_statement_cursors elements which do not have any references
any more. It can happen in various moments in time, e.g.
* after evaluation parameters of an SQL routine
* after assigning a cursor expression into a SYS_REFCURSOR variable
* when leaving a BEGIN..END block with SYS_REFCURSOR variables
* after setting OUT/INOUT routine actual parameters from formal
parameters.
One change is that if the port is not supplied or out of bound, the
old behaviour is to print 3306. The new behaviour is to not print
it (if not supplied) or the out of bound value.
- FLUSH GLOBAL STATUS now resets most global_status_vars.
At this stage, this is mainly to be used for testing.
- FLUSH SESSION STATUS added as an alias for FLUSH STATUS.
- FLUSH STATUS does not require any privilege (before required RELOAD).
- FLUSH GLOBAL STATUS requires RELOAD privilege.
- All global status reset moved to FLUSH GLOBAL STATUS.
- Replication semisync status variables are now reset by
FLUSH GLOBAL STATUS.
- In test cases, the only changes are:
- Replace FLUSH STATUS with FLUSH GLOBAL STATUS
- Replace FLUSH STATUS with FLUSH STATUS; FLUSH GLOBAL STATUS.
This was only done in a few tests where the test was using SHOW STATUS
for both local and global variables.
- Uptime_since_flush_status is now always provided, independent if
ENABLED_PROFILING is enabled when compiling MariaDB.
- @@global.Uptime_since_flush_status is reset on FLUSH GLOBAL STATUS
and @@session.Uptime_since_flush_status is reset on FLUSH SESSION STATUS.
- When connected, @@session.Uptime_since_flush_status is set to 0.
the value of 200 isn't enough for some tests anymore, this causes
some random threads to become not instrumented and any table operations
there are not reflected in the perfschema. If, say, a DROP TABLE
doesn't change perfschema state, perfschema tables might show
ghost tables that no longer exist in the server
mariadb-install-db --auth-root-authentication-method=normal created 4
root accounts by default, but only two of these had PROXY privilege
granted.
mariadb-install-db (default option
--auth-root-authentication-method=socket) as non-root user also didn't
grant PROXY priv to the created nonroot@localhost user.
To fix this, in mysql_system_tables_data.sql, we re-use tmp_user_nopasswd
as this contains the list of all root users.
REPLACE INTO tmp_proxies_priv SELECT @current_hostname, IFNULL(@auth_root_socket, 'root')
creates the $user@$current_host but will not error if @auth_root_socket
is null. Note @current_hostname lines are filtered out with
--cross-bootstrap in mariadb-install-db so it was needed to include this
expression for consistency.
Like the existing mysql_system_tables.sql is used to create teh
$user@localhost proxies_priv.
Test cases roles.acl_statistics, perfschema,privilege_table_io depends on the number of proxy users.
After:
--auth-root-authentication-method=normal:
MariaDB [mysql]> select * from global_priv;
+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| Host | User | Priv |
+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| localhost | mariadb.sys | {"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0} |
| localhost | root | {"access":18446744073709551615} |
| bark | root | {"access":18446744073709551615} |
| 127.0.0.1 | root | {"access":18446744073709551615} |
| ::1 | root | {"access":18446744073709551615} |
| localhost | | {} |
| bark | | {} |
+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.001 sec)
MariaDB [mysql]> select * from proxies_priv;
+-----------+------+--------------+--------------+------------+---------+---------------------+
| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
+-----------+------+--------------+--------------+------------+---------+---------------------+
| localhost | root | | | 1 | | 2023-07-10 12:12:24 |
| 127.0.0.1 | root | | | 1 | | 2023-07-10 12:12:24 |
| ::1 | root | | | 1 | | 2023-07-10 12:12:24 |
| bark | root | | | 1 | | 2023-07-10 12:12:24 |
+-----------+------+--------------+--------------+------------+---------+---------------------+
--auth-root-authentication-method=socket:
MariaDB [mysql]> select * from proxies_priv;
+-----------+------+--------------+--------------+------------+---------+---------------------+
| Host | User | Proxied_host | Proxied_user | With_grant | Grantor | Timestamp |
+-----------+------+--------------+--------------+------------+---------+---------------------+
| localhost | root | | | 1 | | 2023-07-10 12:11:55 |
| localhost | dan | | | 1 | | 2023-07-10 12:11:55 |
| bark | dan | | | 1 | | 2023-07-10 12:11:55 |
+-----------+------+--------------+--------------+------------+---------+---------------------+
3 rows in set (0.017 sec)
MariaDB [mysql]> select * from global_priv;
+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Host | User | Priv |
+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| localhost | mariadb.sys | {"access":0,"plugin":"mysql_native_password","authentication_string":"","account_locked":true,"password_last_changed":0} |
| localhost | root | {"access":18446744073709551615,"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]} |
| localhost | dan | {"access":18446744073709551615,"plugin":"mysql_native_password","authentication_string":"invalid","auth_or":[{},{"plugin":"unix_socket"}]} |
| localhost | | {} |
| bark | | {} |
+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.000 sec)
MariaDB [mysql]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for dan@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `dan`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'dan'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1. Add explicit indication that the output is produced by
SHOW EXPLAIN/ANALYZE FORMAT=JSON command.
2. Remove useless "r_total_time_ms" field from SHOW ANALYZE FORMAT=JSON
output when there is no timed statistics gathered.
3. Add "r_query_time_in_progress_ms" to the output of SHOW ANALYZE FORMAT=JSON.
- DISCARD/IMPORT TABLESPACE are the only tablespace commands left
- TABLESPACE arguments for CREATE TABLE and ALTER ... ADD PARTITION are
ignored.
- Tablespace names are not shown anymore in .frm and not shown in
information schema
Other things
- Removed end spaces from sql/CMakeList.txt
Also fixes:
MDEV-21487: Implement option for mysql_upgrade that allows root@localhost to be replaced
MDEV-21486: Implement option for mysql_install_db that allows root@localhost to be replaced
Add user mariadb.sys to be definer of user view
(and has right on underlying table global_priv for
required operation over global_priv
(SELECT,UPDATE,DELETE))
Also changed definer of gis functions in case of creation,
but they work with any definer so upgrade script do not try
to push this change.
This was done to get more information about where time is spent.
Now we can get proper timing for time spent in commit, rollback,
binlog write etc.
Following stages was added:
- Commit
- Commit_implicit
- Rollback
- Rollback implicit
- Binlog write
- Init for update
- This is used instead of "Init" for insert, update and delete.
- Staring cleanup
Following stages where changed:
- "Unlocking tables" stage reset stage to previous stage at end
- "binlog write" stage resets stage to previous stage at end
- "end" -> "end of update loop"
- "cleaning up" -> "Reset for next command"
- Added stage_searching_rows_for_update when searching for rows
to be deleted.
Other things:
- Renamed all stages to start with big letter (before there was no
consitency)
- Increased performance_schema_max_stage_classes from 150 to 160.
- Most of the test changes in performance schema comes from renaming of
stages.
- Removed duplicate output of variables and inital state in a lot of
performance schema tests.
This was done to make it easier to change a default value for a
performance variable without affecting all tests.
- Added start_server_variables.test to check configuration
- Removed some duplicate "closing tables" stages
- Updated position for "stage_init_update" and "stage_updating" for
delete, insert and update to be just before update loop (for more
exact timing).
- Don't set "Checking permissions" twice in a row.
- Remove stage_end stage from creating views (not done for create table
either).
- Updated default performance history size from 10 to 20 because of new
stages
- Ensure that ps_enabled is correct (to be used in a later patch)
- privilege_table_io.test didn't properly reset roles_mapping
- Fixed memory allocation problem with CHECK CONSTRAINT, found when
running --valgrind main.check_constraint
including the big commit
commit 305130361bf72726de220f3d2b2787395e10be61
Author: Marc Alff <marc.alff@oracle.com>
Date: Tue Feb 10 11:31:32 2015 +0100
WL#8354 BACKPORT DIGEST IMPROVEMENTS TO MYSQL 5.6
(with the following commits) and related changes in sql/
* update results
* don't force HA_CREATE_DELAY_KEY_WRITE on all temp tables,
(bad for CREATE ... LIKE) instead imply it in myisam/aria
* restore HA_ERR_TABLE_DEF_CHANGED in archive
* increase the default number of rwlock classes in P_S to fit all our rwlocks
and small collateral changes
mysql-test/lib/My/Test.pm:
somehow with "print" we get truncated writes sometimes
mysql-test/suite/perfschema/r/digest_table_full.result:
md5 hashes of statement digests differ, because yacc token codes are different in mariadb
mysql-test/suite/perfschema/r/dml_handler.result:
host table is not ported over yet
mysql-test/suite/perfschema/r/information_schema.result:
host table is not ported over yet
mysql-test/suite/perfschema/r/nesting.result:
this differs, because we don't rewrite general log queries, and multi-statement
packets are logged as a one entry. this result file is identical to what mysql-5.6.5
produces with the --log-raw option.
mysql-test/suite/perfschema/r/relaylog.result:
MariaDB modifies the binlog index file directly, while MySQL 5.6 has a feature "crash-safe binlog index" and modifies a special "crash-safe" shadow copy of the index file and then moves it over. That's why this test shows "NONE" index file writes in MySQL and "MANY" in MariaDB.
mysql-test/suite/perfschema/r/server_init.result:
MariaDB initializes the "manager" resources from the "manager" thread, and starts this thread only when --flush-time is not 0. MySQL 5.6 initializes "manager" resources unconditionally on server startup.
mysql-test/suite/perfschema/r/stage_mdl_global.result:
this differs, because MariaDB disables query cache when query_cache_size=0. MySQL does not
do that, and this causes useless mutex locks and waits.
mysql-test/suite/perfschema/r/statement_digest.result:
md5 hashes of statement digests differ, because yacc token codes are different in mariadb
mysql-test/suite/perfschema/r/statement_digest_consumers.result:
md5 hashes of statement digests differ, because yacc token codes are different in mariadb
mysql-test/suite/perfschema/r/statement_digest_long_query.result:
md5 hashes of statement digests differ, because yacc token codes are different in mariadb
mysql-test/suite/rpl/r/rpl_mixed_drop_create_temp_table.result:
will be updated to match 5.6 when alfranio.correia@oracle.com-20110512172919-c1b5kmum4h52g0ni and anders.song@greatopensource.com-20110105052107-zoab0bsf5a6xxk2y are merged
mysql-test/suite/rpl/r/rpl_non_direct_mixed_mixing_engines.result:
will be updated to match 5.6 when anders.song@greatopensource.com-20110105052107-zoab0bsf5a6xxk2y is merged
