Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
Linux, CentOS 7
-
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
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.
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.
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.
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();