| 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: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6.21/5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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.
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;