Bug #74797 create temporary table . select ..union.. select fails on read-only MySQL Server
Submitted: 11 Nov 2014 15:19 Modified: 11 Nov 2014 16:42
Reporter: Alok Pathak Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.21/5.7 OS:Any
Assigned to: CPU Architecture:Any

[11 Nov 2014 15:19] Alok Pathak
Description:
MySQL documentation says that read_only does not apply to TEMPORARY tables.  I got the following error while creating a temporary table with select union select on read_only mysql server.

The user I am using doesn't have SUPER privileges.

show grants;
+-----------------------------------------------+
| Grants for testbug@%                              |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'testbug'@'%'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'testbug'@'%' |
+-----------------------------------------------+
2 rows in set (0.00 sec)

Server version: 5.6.21 MySQL Community Server (GPL)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> use test;
Database changed

mysql> create temporary table t1 
    ->     SELECT 1 as a
    ->     UNION
    ->     SELECT 2 as a;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create temporary table t2 
    ->  SELECT 3 as a
    ->  UNION
    ->  SELECT 4 as a;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Above 2 queries executed successfully without any error.

mysql> create temporary table t3 
    ->    SELECT * FROM t1
    ->     UNION
    ->    SELECT * FROM t2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

When I try to execute above query I get the error, above statement has created t3 table but didn't insert any row.

When I executed same query again it says t3 table already exists.

mysql> create temporary table t3     
	SELECT * FROM t1     
	UNION    
	SELECT * FROM t2;
ERROR 1050 (42S01): Table 't3' already exists

mysql> select * from t3;
Empty set (0.00 sec)

How to repeat:
Run below given statements on read only mysql server with user not having super privileges. You should get error "ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement" on third statement.

 create temporary table t1 
    SELECT 1 as a
    UNION
    SELECT 2 as a;

create temporary table t2 
 SELECT 3 as a
 UNION
 SELECT 4 as a;

create temporary table t3 
   SELECT * FROM t1
    UNION
   SELECT * FROM t2;
[11 Nov 2014 16:33] MySQL Verification Team
c:\dbs>5.6\bin\mysql -uuser1 -puser1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.22 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use r1
Database changed
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> create temporary table t1
    ->     SELECT 1 as a
    ->     UNION
    ->     SELECT 2 as a;
Query OK, 2 rows affected (0.42 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> create temporary table t2
    ->  SELECT 3 as a
    ->  UNION
    ->  SELECT 4 as a;
Query OK, 2 rows affected (0.44 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> create temporary table t3
    ->    SELECT * FROM t1
    ->     UNION
    ->    SELECT * FROM t2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> select * from t3;
Empty set (0.00 sec)

mysql>
[11 Nov 2014 16:42] MySQL Verification Team
Thank you for the bug report.

C:\dbs>57

C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 --prompt="mysql 5.7 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database r1;
Query OK, 1 row affected (0.00 sec)

mysql 5.7 > grant all on r1.* to 'user1'@'localhost' identified by 'user1';
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > exit
Bye

C:\dbs>net stop mysqld57
The MySQLD57 service is stopping...
The MySQLD57 service was stopped successfully.

C:\dbs>5.7\bin\mysqld --read_only --standalone --console
2014-11-11T17:37:23.657747Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp se
rver option (see documentation for more details).
2014-11-11T17:37:23.673347Z 0 [Note] 5.7\bin\mysqld (mysqld 5.7.6-m16) starting as process 780 ...
2014-11-11T17:37:23.673347Z 0 [Note] Plugin 'FEDERATED' is disabled.
2014-11-11T17:37:23.673347Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2014-11-11T17:37:23.673347Z 0 [Note] InnoDB: Uses event mutexes
2014-11-11T17:37:23.673347Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2014-11-11T17:37:23.673347Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-11-11T17:37:23.673347Z 0 [Note] InnoDB: Number of pools: 1
2014-11-11T17:37:23.673347Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2014-11-11T17:37:23.688947Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2014-11-11T17:37:23.688947Z 0 [Note] InnoDB: Completed initialization of buffer pool
2014-11-11T17:37:23.720147Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2014-11-11T17:37:23.891747Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2014-11-11T17:37:23.891747Z 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2014-11-11T17:37:24.063347Z 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2014-11-11T17:37:24.063347Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2014-11-11T17:37:24.063347Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2014-11-11T17:37:24.063347Z 0 [Note] InnoDB: Waiting for purge to start
2014-11-11T17:37:24.125748Z 0 [Note] InnoDB: 5.7.6 started; log sequence number 1416757
2014-11-11T17:37:24.125748Z 0 [Warning] Failed to setup SSL
2014-11-11T17:37:24.125748Z 0 [Warning] SSL error: SSL context is not usable without certificate and private key
2014-11-11T17:37:24.125748Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2014-11-11T17:37:24.125748Z 0 [Note] IPv6 is available.
2014-11-11T17:37:24.125748Z 0 [Note]   - '::' resolves to '::';
2014-11-11T17:37:24.125748Z 0 [Note] Server socket created on IP: '::'.
2014-11-11T17:37:24.203748Z 0 [Note] Event Scheduler: Loaded 0 events
2014-11-11T17:37:24.203748Z 0 [Note] 5.7\bin\mysqld: ready for connections.
Version: '5.7.6-m16'  socket: ''  port: 3306  Source distribution

c:\dbs>5.7\bin\mysql -uuser1 -puser1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.6-m16 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use r1
Database changed
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.03 sec)

mysql>  create temporary table t1
    ->     SELECT 1 as a
    ->     UNION
    ->     SELECT 2 as a;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> create temporary table t2
    ->  SELECT 3 as a
    ->  UNION
    ->  SELECT 4 as a;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> create temporary table t3
    ->    SELECT * FROM t1
    ->     UNION
    ->    SELECT * FROM t2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mysql>  select * from t3;
Empty set (0.00 sec)
[3 Dec 2017 16:05] Mordechai Danielov
This is pretty old, but still happening. Any plans to fix it?
[14 Sep 2021 11:06] MySQL Verification Team
I can repeat this on 5.6.50, but not on 5.7 or 8.0.