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 15:19]
Alok Pathak
[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.