Description:
Creating a temporary table with a user with basic select/create temp access on a readonly slave fails if CREATE... SELECT from temporary table.
If doing so with a super user, the same behavior is not exhibited and the query is executed without issue.
How to repeat:
mysql> CREATE TEMPORARY TABLE t1 AS SELECT 1 AS tab UNION ALL SELECT 2;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TEMPORARY TABLE `tmp1` ( `label` varchar(30) NOT NULL DEFAULT '', `tab` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
-- same result with union/union all
mysql> CREATE TEMPORARY TABLE t1 AS SELECT 1 AS tab UNION ALL SELECT 2 as tab from tmp1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
-- Strange that the table is still created
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TEMPORARY TABLE `t1` (
`tab` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
| 1 |
+-------------+
mysql> show grants;
... GRANT SELECT, PROCESS, SHOW DATABASES, CREATE TEMPORARY TABLES, SHOW VIEW ON *.* TO 'cjack_test'@localhost IDENTIFIED BY PASSWORD <secret> |
Description: Creating a temporary table with a user with basic select/create temp access on a readonly slave fails if CREATE... SELECT from temporary table. If doing so with a super user, the same behavior is not exhibited and the query is executed without issue. How to repeat: mysql> CREATE TEMPORARY TABLE t1 AS SELECT 1 AS tab UNION ALL SELECT 2; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> CREATE TEMPORARY TABLE `tmp1` ( `label` varchar(30) NOT NULL DEFAULT '', `tab` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.01 sec) mysql> drop table t1; Query OK, 0 rows affected (0.01 sec) -- same result with union/union all mysql> CREATE TEMPORARY TABLE t1 AS SELECT 1 AS tab UNION ALL SELECT 2 as tab from tmp1; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement -- Strange that the table is still created mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TEMPORARY TABLE `t1` ( `tab` varchar(30) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select @@read_only; +-------------+ | @@read_only | +-------------+ | 1 | +-------------+ mysql> show grants; ... GRANT SELECT, PROCESS, SHOW DATABASES, CREATE TEMPORARY TABLES, SHOW VIEW ON *.* TO 'cjack_test'@localhost IDENTIFIED BY PASSWORD <secret> |