Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10119

mysql_install_db creates GIS procedures with invalid definer

Details

    • 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 ``.

      Attachments

        Activity

          People

            holyfoot Alexey Botchkov
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.