Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.14
-
10.1.15
Description
mysql_install_db creates the GIS stored procedures with the following definer values:
MariaDB [(none)]> select db, name, definer from mysql.proc;
|
+------+--------------------+---------+
|
| db | name | definer |
|
+------+--------------------+---------+
|
| test | AddGeometryColumn | @ |
|
| test | DropGeometryColumn | @ |
|
+------+--------------------+---------+
|
2 rows in set (0.00 sec)
|
SHOW CREATE PROCEDURE interprets this definer in the following way:
MariaDB [(none)]> SHOW CREATE PROCEDURE test.AddGeometryColumn;
|
+-------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
|
+-------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|
| AddGeometryColumn | | CREATE DEFINER=`` PROCEDURE `AddGeometryColumn`(catalog varchar(64), t_schema varchar(64),
|
t_name varchar(64), geometry_column varchar(64), t_srid int)
|
begin
|
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 | latin1 | latin1_swedish_ci | latin1_swedish_ci |
|
+-------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
|
1 row in set (0.00 sec)
|
But if I try to run this statement, MariaDB actually rejects this definer value:
MariaDB [(none)]> create database db1;
|
Query OK, 1 row affected (0.00 sec)
|
 |
MariaDB [(none)]> use db1;
|
Database changed
|
MariaDB [db1]> delimiter //
|
MariaDB [db1]> CREATE DEFINER=`` PROCEDURE `AddGeometryColumn`(catalog varchar(64), t_schema varchar(64),
|
-> t_name varchar(64), geometry_column varchar(64), t_srid int)
|
-> begin
|
-> 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 //
|
ERROR 1959 (OP000): Invalid role specification ``.
|
Since mysqldump uses SHOW CREATE PROCEDURE to back up procedures, this also breaks things when trying to restore a backup created with mysqldump:
> mysql -u root < master1.sql
|
ERROR 1959 (OP000) at line 1212: Invalid role specification ``.
|