mariadb-upgrade silently dropped EXECUTE grants on sys schema stored
functions and procedures. The sys schema install scripts reinstalled
every routine with DROP FUNCTION/PROCEDURE IF EXISTS followed by
CREATE. DROP cascades to delete the routine's rows in
mysql.procs_priv, so any EXECUTE grant a DBA had issued on e.g.
sys.table_exists or sys.quote_identifier was lost every time
mariadb-upgrade reinstalled the sys schema, even though the routine
itself came back unchanged.
Fix: replace DROP ... IF EXISTS + CREATE with CREATE OR REPLACE in
all 53 sys_schema function/procedure files and in the two templates
(templates/function.sql, templates/procedure.sql) so future routines
follow the same pattern. CREATE OR REPLACE PROCEDURE/FUNCTION goes
through sp_drop_routine_internal(), which only deletes the
mysql.proc row and never reaches sp_revoke_privileges() (that is
only called from the explicit DROP PROCEDURE/FUNCTION statement),
so mysql.procs_priv is left untouched and existing grants survive.
This mirrors the pattern already used by sys schema views
(CREATE OR REPLACE ... VIEW, since MDEV-9077), which never had this
problem.
Four of the converted files (functions/format_path.sql,
functions/ps_is_account_enabled_57.sql,
procedures/ps_setup_reset_to_default.sql,
procedures/ps_trace_thread_57.sql) are not referenced by
scripts/sys_schema/CMakeLists.txt; they were converted anyway for
consistency and have no behavioural effect.
As a side effect, a pre-existing UDF whose name collides with a sys
routine name is no longer destroyed before the reinstall fails:
DROP FUNCTION IF EXISTS resolved the UDF namespace first, silently
dropping the UDF and then failing on ER_SP_ALREADY_EXISTS anyway;
CREATE OR REPLACE fails immediately on ER_UDF_EXISTS with the UDF
intact.
scripts/maria_add_gis_sp.sql.in and the sys_config triggers were
deliberately left untouched: the GIS procedures are only
re-installed at bootstrap time (mariadb-upgrade instead patches
their definer in place via UPDATE), and triggers carry no
procs_priv rows, so neither is on the code path this bug is about.
Added mysql-test/main/mysql_upgrade_sys_routine_grants.test, which
grants EXECUTE on sys.table_exists and sys.quote_identifier,
overwrites both routine bodies with a marker to prove the upgrade
actually reinstalls them (rather than the sys schema install being
skipped), runs mariadb-upgrade, and checks both that the grants
survived and that the real routine bodies came back.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
- Innodb is not always available, which means t is not always
possible to use innodb system variables, or innodb information schema
tables.
Thus creation of objects that use Innodb information_schema is enclosed
into BEGIN NOT ATOMIC blocks with dummy SQLEXCEPTION handler.
- sys_config table uses Aria, just like other system tables.
- several tables that exist in MySQL, do not exist in MariaDB
performance_schema.replication_applier_status, mysql.slave_master_info,
mysql.slave_relay_log_info
