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

    XMLWordPrintable

Details

    • 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

            holyfoot Alexey Botchkov
            markalanpeter Mark Peter
            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.