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

Invalid definer for a procedure

    XMLWordPrintable

Details

    Description

      This is quite cryptic. I create a procedure from a script running as mariadb UNIX user (under systemd service, no human factor), identified by UNIX socket. I expect that the definer (owner) of the procedure is mariadb@localhost. This is the case 99.9999% of time. But at least two times when I called this procedure I saw the error The user specified as a definer ('root'@'%') does not exist. It worth mention that user root does not exist in mysql.user at all. As a workaround I restart the systemd service and get properly defined procedure.

      GRANT ALL PRIVILEGES ON *.* TO 'mariadb'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION
      

      Procedure:

      DELIMITER $$
       
      DROP PROCEDURE IF EXISTS mywatch_kill $$
      CREATE PROCEDURE mywatch_kill (IN i BIGINT)
        COMMENT 'Kill a query found in information_schema.processlist by ID'
      BEGIN
       
        DECLARE n BIGINT;
       
        SELECT id INTO n
          FROM information_schema.processlist
          WHERE info IS NOT NULL
          AND host <> '' -- means non-system user
          AND id = i;
       
        IF (n IS NOT NULL) THEN
          KILL n;
        END IF;
       
      END $$
       
      DELIMITER ;
      

      MariaDB [mysql]> show create procedure mywatch_kill\G
      *************************** 1. row ***************************
                 Procedure: mywatch_kill
                  sql_mode: NO_ENGINE_SUBSTITUTION
          Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `mywatch_kill`(IN i BIGINT)
          COMMENT 'Kill a query found in information_schema.processlist by ID'
      BEGIN
       
          DECLARE n BIGINT;
       
          SELECT id INTO n
            FROM information_schema.processlist
            WHERE info IS NOT NULL
            AND host <> '' 
            AND id = i;
       
          IF (n IS NOT NULL) THEN
            CALL mysql.rds_kill(n);
          END IF;
       
        END
      character_set_client: utf8
      collation_connection: utf8_general_ci
        Database Collation: latin1_swedish_ci
      1 row in set (0.00 sec)
      
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              ip1981 Igor Pashev
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.