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

CREATE TEMPORARY TABLE AS SELECT causes error 1290 with read_only and InnoDB

    Details

    • Sprint:
      10.2.12, 5.5.59, 10.0.34

      Description

      Trying to create a temporary table, using "CREATE TEMPORARY TABLE <table_name> AS SELECT ..." generates error 1290 under the following conditions:

      read_only = 1;
      default_tmp_storage_engine = InnoDB;
      

      User has privileges for the schema, but no SUPER privilege.

      Test case:

      -- Log in as user with all privileges, including SUPER
      SET GLOBAL read_only = on;
      CREATE DATABASE mydb;
      CONNECT mydb;
      CREATE TABLE mytable (id INTEGER);
      INSERT INTO mytable (id) VALUES (1);
       
      -- Create user without SUPER privilege
      CREATE USER 'nosuper' IDENTIFIED BY 'nosuper';
      GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES, LOCK TABLES ON mydb.* TO 'nosuper'@'localhost';
      FLUSH PRIVILEGES;
       
      -- Now log into mydb as 'nosuper'
      SET default_tmp_storage_engine = INNODB;
      CREATE TEMPORARY TABLE t1 AS SELECT id FROM mytable;
      -- Error 1290
       
      --If you do this instead, it works
      CREATE TEMPORARY TABLE t1 (id INTEGER);
      INSERT INTO t1 SELECT id FROM mytable;
       
      --And both methods work if default_tmp_storage_engine = MyISAM
      

        Attachments

          Activity

            People

            • Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              markalanpeter Mark Peter
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: