1--echo # 2--echo # The definer of Add/DropGeometryColumn procedures should be updated to mariadb.sys during upgrade 3--echo # 4 5--source include/mysql_upgrade_preparation.inc 6 7use mysql; 8 9create table save_proc like proc; 10insert into save_proc select * from proc; 11set @save_sql_mode= @@sql_mode; 12 13--echo # 14--echo # If the definer is root before the upgrade: 15--echo # Drop the procedures if exists and recreate with root definer 16--echo # 17DROP PROCEDURE IF EXISTS AddGeometryColumn; 18DROP PROCEDURE IF EXISTS DropGeometryColumn; 19 20DELIMITER |; 21 22CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64), 23 t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER 24begin 25 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' ADD ', geometry_column,' GEOMETRY REF_SYSTEM_ID=', t_srid); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end | 26 27CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64), 28 t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER 29begin 30 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end | 31 32DELIMITER ;| 33 34SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost'; 35SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost'; 36 37SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost'; 38SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost'; 39 40let $MYSQLD_DATADIR= `select @@datadir`; 41 42--echo # 43--echo # Run mysql_upgrade 44--echo # 45--exec $MYSQL_UPGRADE 2>&1 46--file_exists $MYSQLD_DATADIR/mysql_upgrade_info 47--remove_file $MYSQLD_DATADIR/mysql_upgrade_info 48 49--echo # 50--echo # check new definers of Add/DropGeometryColumn 51--echo # 52SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost'; 53SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost'; 54 55SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost'; 56SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost'; 57 58--echo # 59--echo # restore environment 60--echo # 61delete from proc; 62rename table proc to bad_proc; 63rename table save_proc to proc; 64drop table bad_proc; 65flush privileges; 66 67