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