[MDEV-14185] CREATE TEMPORARY TABLE AS SELECT causes error 1290 with read_only and InnoDB Created: 2017-10-28  Updated: 2018-04-10  Resolved: 2018-04-10

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary, Storage Engine - InnoDB
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.60

Type: Bug Priority: Critical
Reporter: Mark Peter Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

Linux, CentOS 7

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



 Comments   
Comment by Mark Peter [ 2017-10-29 ]

Another note that might be helpful. To generate the error, the CREATE TEMPORARY TABLE command must select data from an actual table.

E.g., this works without error: CREATE TEMPORARY TABLE t1 AS SELECT SYSDATE();

Comment by Elena Stepanova [ 2017-11-07 ]

Thanks for the report and test case.

--source include/have_innodb.inc
 
 
 
SET GLOBAL read_only = on;
 
CREATE DATABASE mydb;
 
use mydb;
 
CREATE TABLE mytable (id INTEGER) ENGINE=InnoDB;
 
INSERT INTO mytable (id) VALUES (1);
 
 
CREATE USER 'nosuper'@'localhost';
 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES, LOCK TABLES ON mydb.* TO 'nosuper'@'localhost';
 
 
 
--connect (con1,localhost,nosuper,,mydb)
 
 
CREATE TEMPORARY TABLE t1 ENGINE=InnoDB AS SELECT id FROM mytable;
 
 
 
# Cleanup
 
--disconnect con1
 
--connection default
 
DROP DATABASE mydb;
 
DROP USER nosuper@localhost;
 
SET GLOBAL read_only= DEFAULT;

Reproducible on all of 5.5-10.3 and on MySQL 5.5. Not reproducible on MySQL 5.6, 5.7.

Comment by Vesa Pentti (Inactive) [ 2018-01-16 ]

It seems to me there is no actual fix in MySQL 5.7 server core, but it works because InnoDB in there doesn't mark the transaction started in the table creation of the create .. select statement.

Comment by Vesa Pentti (Inactive) [ 2018-01-16 ]

The code in the table creation flow has changed in relevant parts between MariaDB 5.5 and 10.1, so the proposed fix would be different in details, between 5.5 and 10.1+, and they would not merge.

Comment by Vesa Pentti (Inactive) [ 2018-01-16 ]

commit d4366c4a2c9b41901b11ca180d3ddbf79e992490

Generated at Thu Feb 08 08:11:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.