Bug #92391 read_only=1, non super user Create temporary as select from temp fails
Submitted: 12 Sep 2018 15:55 Modified: 13 Sep 2018 6:52
Reporter: Chris Jack Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.6.40 5.7.6, 5.6.41 OS:Red Hat
Assigned to: CPU Architecture:Any

[12 Sep 2018 15:55] Chris Jack
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> |
[13 Sep 2018 6:52] MySQL Verification Team
Hello Chris Jack,

Thank you for the report.
Observed that 5.6.41 is affected(but 5.7.23 is not affected).

Thanks,
Umesh