1#
2# The definer of Add/DropGeometryColumn procedures should be updated to mariadb.sys during upgrade
3#
4use mysql;
5create table save_proc like proc;
6insert into save_proc select * from proc;
7set @save_sql_mode= @@sql_mode;
8#
9# If the definer is root before the upgrade:
10# Drop the procedures if exists and recreate with root definer
11#
12DROP PROCEDURE IF EXISTS AddGeometryColumn;
13DROP PROCEDURE IF EXISTS DropGeometryColumn;
14CREATE DEFINER=`root`@`localhost` PROCEDURE AddGeometryColumn(catalog varchar(64), t_schema varchar(64),
15t_name varchar(64), geometry_column varchar(64), t_srid int) SQL SECURITY INVOKER
16begin
17set @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 |
18CREATE DEFINER=`root`@`localhost` PROCEDURE DropGeometryColumn(catalog varchar(64), t_schema varchar(64),
19t_name varchar(64), geometry_column varchar(64)) SQL SECURITY INVOKER
20begin
21set @qwe= concat('ALTER TABLE ', t_schema, '.', t_name, ' DROP ', geometry_column); PREPARE ls from @qwe; execute ls; deallocate prepare ls; end |
22SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost';
23count(*)
241
25SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost';
26count(*)
271
28SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
29count(*)
300
31SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
32count(*)
330
34#
35# Run mysql_upgrade
36#
37Phase 1/7: Checking and upgrading mysql database
38Processing databases
39mysql
40mysql.column_stats                                 OK
41mysql.columns_priv                                 OK
42mysql.db                                           OK
43mysql.event                                        OK
44mysql.func                                         OK
45mysql.global_priv                                  OK
46mysql.gtid_slave_pos                               OK
47mysql.help_category                                OK
48mysql.help_keyword                                 OK
49mysql.help_relation                                OK
50mysql.help_topic                                   OK
51mysql.index_stats                                  OK
52mysql.innodb_index_stats
53Error    : Unknown storage engine 'InnoDB'
54error    : Corrupt
55mysql.innodb_table_stats
56Error    : Unknown storage engine 'InnoDB'
57error    : Corrupt
58mysql.plugin                                       OK
59mysql.proc                                         OK
60mysql.procs_priv                                   OK
61mysql.proxies_priv                                 OK
62mysql.roles_mapping                                OK
63mysql.save_proc                                    OK
64mysql.servers                                      OK
65mysql.table_stats                                  OK
66mysql.tables_priv                                  OK
67mysql.time_zone                                    OK
68mysql.time_zone_leap_second                        OK
69mysql.time_zone_name                               OK
70mysql.time_zone_transition                         OK
71mysql.time_zone_transition_type                    OK
72mysql.transaction_registry
73Error    : Unknown storage engine 'InnoDB'
74error    : Corrupt
75
76Repairing tables
77mysql.innodb_index_stats
78Error    : Unknown storage engine 'InnoDB'
79error    : Corrupt
80mysql.innodb_table_stats
81Error    : Unknown storage engine 'InnoDB'
82error    : Corrupt
83mysql.transaction_registry
84Error    : Unknown storage engine 'InnoDB'
85error    : Corrupt
86Phase 2/7: Installing used storage engines... Skipped
87Phase 3/7: Fixing views
88mysql.user                                         OK
89Phase 4/7: Running 'mysql_fix_privilege_tables'
90Phase 5/7: Fixing table and database names
91Phase 6/7: Checking and upgrading tables
92Processing databases
93information_schema
94mtr
95mtr.global_suppressions                            OK
96mtr.test_suppressions                              OK
97performance_schema
98test
99Phase 7/7: Running 'FLUSH PRIVILEGES'
100OK
101#
102# check new definers of Add/DropGeometryColumn
103#
104SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'root@localhost';
105count(*)
1060
107SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'root@localhost';
108count(*)
1090
110SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='AddGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
111count(*)
1121
113SELECT count(*) FROM information_schema.ROUTINES WHERE ROUTINE_CATALOG = 'def' and ROUTINE_SCHEMA = 'mysql' and ROUTINE_NAME='DropGeometryColumn' and DEFINER = 'mariadb.sys@localhost';
114count(*)
1151
116#
117# restore environment
118#
119delete from proc;
120rename table proc to bad_proc;
121rename table save_proc to proc;
122drop table bad_proc;
123flush privileges;
124