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

When a slave server is running in read_only mode, and when connected as a user that does not have the SUPER privilege, it is not possible to create temporary tables using data from innodb real tables.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.2.23, 10.3.18, 10.4
    • Fix Version/s: 10.2, 10.3, 10.4
    • Labels:
      None
    • Environment:
      CentOS Linux release 7.6.1810 (Core)
      Linux 3.10.0-957.27.2.el7.x86_64 #1 SMP Mon Jul 29 17:46:05 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

      Description

      When a slave server is running in read_only mode, and when connected as a user that does not have the SUPER privilege, it is not possible to create temporary tables using data from InnoDB real tables.

      However, when the data is from MyISAM real tables, it works.

      ######################################
      # master
      ######################################
       
      MariaDB [mysql]> CREATE DATABASE bug_test;
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [mysql]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON bug_test.* TO 'user_test'@'localhost';
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [bug_test]> CREATE TABLE foo (id INT) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [bug_test]> INSERT INTO foo VALUES (1),(2),(3);
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [bug_test]> SELECT * FROM foo;
      +------+
      | id   |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.00 sec)
       
      MariaDB [bug_test]> CREATE TABLE foo2 (id INT) ENGINE=MyISAM;
      Query OK, 0 rows affected (0.02 sec)
       
      MariaDB [bug_test]> INSERT INTO foo2 VALUES (1),(2),(3);
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [bug_test]> SELECT * FROM foo2;
      +------+
      | id   |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.00 sec)
       
      ######################################
      # slave
      ######################################
      MariaDB [(none)]> show grants;
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Grants for user_test@localhost                                                                                                                                                     |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | GRANT PROCESS ON *.* TO 'user_test'@'localhost' IDENTIFIED BY PASSWORD '*HASHFORPASSHWORD'                                                                 |
      | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON `bug_test`.* TO 'user_test'@'localhost'          |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
       
      MariaDB [bug_test]> show tables;
      +--------------------+
      | Tables_in_bug_test |
      +--------------------+
      | foo                |
      | foo2               |
      +--------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [bug_test]> CREATE TEMPORARY TABLE bar SELECT * FROM foo;
      ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
       
      MariaDB [bug_test]> desc bar;
      +-------+---------+------+-----+---------+-------+
      | Field | Type    | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | id    | int(11) | YES  |     | NULL    |       |
      +-------+---------+------+-----+---------+-------+
       
      MariaDB [bug_test]> INSERT INTO bar SELECT * FROM foo;
      Query OK, 3 rows affected (0.001 sec)
      Records: 3  Duplicates: 0  Warnings
       
      MariaDB [bug_test]> SELECT * FROM bar;
      +------+
      | id   |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.001 sec)
       
      MariaDB [bug_test]> CREATE TEMPORARY TABLE bar2 SELECT * FROM foo2;
      Query OK, 3 rows affected (0.002 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [bug_test]> SELECT * FROM bar2;
      +------+
      | id   |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.001 se
      
      

      As you can see, MariaDB returned an error when it was tried to create a temporary table using data from a InnoDB real table.

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            brandfbb Breno Brand Fernandes
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated: