1 2-- source include/mysql_upgrade_preparation.inc 3 4call mtr.add_suppression("Cannot load from mysql.proc. The table is probably corrupted"); 5create database mysqltest1; 6use mysqltest1; 7create table save_global_priv as select * from mysql.global_priv; 8create table save_tables_priv as select * from mysql.tables_priv; 9create table save_proxies_priv as select * from mysql.proxies_priv; 10create table mysql.save_proc like mysql.proc; 11insert into mysql.save_proc select * from mysql.proc; 12set @save_sql_mode= @@sql_mode; 13 14use mysql; 15 16--echo # make old definition of gis procedures and user view 17 18drop view user; 19 20CREATE DEFINER='root'@'localhost' SQL SECURITY DEFINER VIEW IF NOT EXISTS user AS SELECT 21 Host, 22 User, 23 IF(JSON_VALUE(Priv, '$.plugin') IN ('mysql_native_password', 'mysql_old_password'), IFNULL(JSON_VALUE(Priv, '$.authentication_string'), ''), '') AS Password, 24 IF(JSON_VALUE(Priv, '$.access') & 1, 'Y', 'N') AS Select_priv, 25 IF(JSON_VALUE(Priv, '$.access') & 2, 'Y', 'N') AS Insert_priv, 26 IF(JSON_VALUE(Priv, '$.access') & 4, 'Y', 'N') AS Update_priv, 27 IF(JSON_VALUE(Priv, '$.access') & 8, 'Y', 'N') AS Delete_priv, 28 IF(JSON_VALUE(Priv, '$.access') & 16, 'Y', 'N') AS Create_priv, 29 IF(JSON_VALUE(Priv, '$.access') & 32, 'Y', 'N') AS Drop_priv, 30 IF(JSON_VALUE(Priv, '$.access') & 64, 'Y', 'N') AS Reload_priv, 31 IF(JSON_VALUE(Priv, '$.access') & 128, 'Y', 'N') AS Shutdown_priv, 32 IF(JSON_VALUE(Priv, '$.access') & 256, 'Y', 'N') AS Process_priv, 33 IF(JSON_VALUE(Priv, '$.access') & 512, 'Y', 'N') AS File_priv, 34 IF(JSON_VALUE(Priv, '$.access') & 1024, 'Y', 'N') AS Grant_priv, 35 IF(JSON_VALUE(Priv, '$.access') & 2048, 'Y', 'N') AS References_priv, 36 IF(JSON_VALUE(Priv, '$.access') & 4096, 'Y', 'N') AS Index_priv, 37 IF(JSON_VALUE(Priv, '$.access') & 8192, 'Y', 'N') AS Alter_priv, 38 IF(JSON_VALUE(Priv, '$.access') & 16384, 'Y', 'N') AS Show_db_priv, 39 IF(JSON_VALUE(Priv, '$.access') & 32768, 'Y', 'N') AS Super_priv, 40 IF(JSON_VALUE(Priv, '$.access') & 65536, 'Y', 'N') AS Create_tmp_table_priv, 41 IF(JSON_VALUE(Priv, '$.access') & 131072, 'Y', 'N') AS Lock_tables_priv, 42 IF(JSON_VALUE(Priv, '$.access') & 262144, 'Y', 'N') AS Execute_priv, 43 IF(JSON_VALUE(Priv, '$.access') & 524288, 'Y', 'N') AS Repl_slave_priv, 44 IF(JSON_VALUE(Priv, '$.access') & 1048576, 'Y', 'N') AS Repl_client_priv, 45 IF(JSON_VALUE(Priv, '$.access') & 2097152, 'Y', 'N') AS Create_view_priv, 46 IF(JSON_VALUE(Priv, '$.access') & 4194304, 'Y', 'N') AS Show_view_priv, 47 IF(JSON_VALUE(Priv, '$.access') & 8388608, 'Y', 'N') AS Create_routine_priv, 48 IF(JSON_VALUE(Priv, '$.access') & 16777216, 'Y', 'N') AS Alter_routine_priv, 49 IF(JSON_VALUE(Priv, '$.access') & 33554432, 'Y', 'N') AS Create_user_priv, 50 IF(JSON_VALUE(Priv, '$.access') & 67108864, 'Y', 'N') AS Event_priv, 51 IF(JSON_VALUE(Priv, '$.access') & 134217728, 'Y', 'N') AS Trigger_priv, 52 IF(JSON_VALUE(Priv, '$.access') & 268435456, 'Y', 'N') AS Create_tablespace_priv, 53 IF(JSON_VALUE(Priv, '$.access') & 536870912, 'Y', 'N') AS Delete_history_priv, 54 ELT(IFNULL(JSON_VALUE(Priv, '$.ssl_type'), 0) + 1, '', 'ANY','X509', 'SPECIFIED') AS ssl_type, 55 IFNULL(JSON_VALUE(Priv, '$.ssl_cipher'), '') AS ssl_cipher, 56 IFNULL(JSON_VALUE(Priv, '$.x509_issuer'), '') AS x509_issuer, 57 IFNULL(JSON_VALUE(Priv, '$.x509_subject'), '') AS x509_subject, 58 CAST(IFNULL(JSON_VALUE(Priv, '$.max_questions'), 0) AS UNSIGNED) AS max_questions, 59 CAST(IFNULL(JSON_VALUE(Priv, '$.max_updates'), 0) AS UNSIGNED) AS max_updates, 60 CAST(IFNULL(JSON_VALUE(Priv, '$.max_connections'), 0) AS UNSIGNED) AS max_connections, 61 CAST(IFNULL(JSON_VALUE(Priv, '$.max_user_connections'), 0) AS SIGNED) AS max_user_connections, 62 IFNULL(JSON_VALUE(Priv, '$.plugin'), '') AS plugin, 63 IFNULL(JSON_VALUE(Priv, '$.authentication_string'), '') AS authentication_string, 64 'N' AS password_expired, 65 ELT(IFNULL(JSON_VALUE(Priv, '$.is_role'), 0) + 1, 'N', 'Y') AS is_role, 66 IFNULL(JSON_VALUE(Priv, '$.default_role'), '') AS default_role, 67 CAST(IFNULL(JSON_VALUE(Priv, '$.max_statement_time'), 0.0) AS DECIMAL(12,6)) AS max_statement_time 68 FROM global_priv; 69 70 71SET sql_mode=''; 72 73DROP PROCEDURE IF EXISTS mysql.AddGeometryColumn; 74DROP PROCEDURE IF EXISTS mysql.DropGeometryColumn; 75 76delimiter |; 77 78CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64), 79 t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER 80begin 81 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 | 82 83CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64), 84 t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER 85begin 86 set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end | 87 88delimiter ;| 89set @@sql_mode= @save_sql_mode; 90 91drop user 'mariadb.sys'@'localhost'; 92 93--echo # check old definitions mysql_upgrade 94 95SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'root@localhost'; 96SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost'; 97SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'"; 98SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'"; 99 100let $MYSQLD_DATADIR= `select @@datadir`; 101 102--echo # Run mysql_upgrade 103--exec $MYSQL_UPGRADE 2>&1 104--file_exists $MYSQLD_DATADIR/mysql_upgrade_info 105--remove_file $MYSQLD_DATADIR/mysql_upgrade_info 106 107--echo # check new definitions mysql_upgrade 108 109SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'root@localhost'; 110SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' and TABLE_SCHEMA = 'mysql' and TABLE_NAME='user' and DEFINER = 'mariadb.sys@localhost'; 111SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'"; 112SELECT * FROM information_schema.TABLE_PRIVILEGES WHERE GRANTEE="'mariadb.sys'@'localhost'"; 113 114--echo # check non root 115 116CREATE USER 'not_root'@'localhost'; 117GRANT ALL PRIVILEGES ON *.* TO 'not_root'@'localhost'; 118GRANT PROXY ON ''@'%' TO 'not_root'@'localhost' WITH GRANT OPTION; 119 120connect con1,localhost,not_root,,; 121 122connection con1; 123 124DROP USER 'root'@'localhost'; 125DROP USER 'root'@'127.0.0.1'; 126DROP USER 'root'@'::1'; 127 128use mysqltest1; 129 130create table t1 (a int); 131call mysql.AddGeometryColumn("def", "mysqltest1", "t1", "g", 101); 132 133show create table t1; 134use mysql; 135 136select count(*) from user; 137 138--echo # restore environment 139 140delete from global_priv; 141delete from tables_priv; 142delete from proxies_priv; 143delete from proc; 144insert into mysql.global_priv select * from mysqltest1.save_global_priv; 145insert into mysql.tables_priv select * from mysqltest1.save_tables_priv; 146insert into mysql.proxies_priv select * from mysqltest1.save_proxies_priv; 147rename table proc to bad_proc; 148rename table save_proc to proc; 149drop table bad_proc; 150flush privileges; 151 152disconnect default; 153connect default,localhost,root,,; 154connection default; 155disconnect con1; 156 157drop database mysqltest1; 158 159--echo # End of 10.4 tests (but better do not add other tests here) 160