The bug was updated successfully. The following people were notified: the MySQL developers, the bug reporter, and nobody else.
Bug #68791 | insert failes into temp tables with read_only flag | ||
---|---|---|---|
Submitted: | 27 Mar 2013 8:48 | Modified: | 1 Jul 2016 10:28 |
Reporter: | Denis Pimenov | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.5.29,5.5.x | OS: | Linux |
Assigned to: | CPU Architecture: | Any |
[27 Mar 2013 8:48]
Denis Pimenov
[27 Mar 2013 8:50]
Denis Pimenov
Working variant on 5.1.46 mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.1.46-log | +---------------+------------+ 1 row in set (0.00 sec) mysql> show grants for current_user; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for bill@localhost | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'bill'@'localhost' IDENTIFIED BY PASSWORD '*5CFAA65DE2C8101B4E2C0DB8F362A603B6B57209' WITH GRANT OPTION | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `bgbilling`.* TO 'bill'@'localhost' | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.01 sec) mysql> create temporary table test33 like contract; Query OK, 0 rows affected (0.18 sec) mysql> create temporary table test44 select * from contract; Query OK, 9999 rows affected (0.97 sec) Records: 9999 Duplicates: 0 Warnings: 0
[27 Mar 2013 8:53]
Denis Pimenov
Working varinat on 5.6.10 show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0,00 sec) mysql> show variables like 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.6.10 | +---------------+--------+ 1 row in set (0,00 sec) mysql> show grants for current_user; +--------------------------------------------------------------------------------------------------------------+ | Grants for dimon@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dimon'@'localhost' IDENTIFIED BY PASSWORD '*F2B84A8E2ABDC51109BAED451917A9412A5ABEE2' | | GRANT ALL PRIVILEGES ON `bgbilling`.* TO 'dimon'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0,00 sec) mysql> create temporary table test77 like contract; Query OK, 0 rows affected (0,05 sec) mysql> create temporary table test88 select * from contract; Query OK, 0 rows affected (0,05 sec) Records: 0 Duplicates: 0 Warnings: 0
[27 Mar 2013 9:01]
Denis Pimenov
By setting this option(read_only) i wanted to prevent my slave database from writing. It's worked on 5.1.x, but doesn't work in 5.5.x, and works again in 5.6.x.
[27 Mar 2013 9:03]
Denis Pimenov
OS - Linux
[27 Mar 2013 13:41]
MySQL Verification Team
Hello Denis Pimenov, Thank you for the report. Verified as described. Thanks, Umesh
[27 Mar 2013 13:42]
MySQL Verification Team
####### mysql> select version(); +--------------+ | version() | +--------------+ | 5.5.29-debug | +--------------+ 1 row in set (0.00 sec) mysql> create temporary table t2 select * from t1; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement mysql> select user(); +-----------------+ | user() | +-----------------+ | dimon@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> show grants for 'dimon'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for dimon@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dimon'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT ALL PRIVILEGES ON `test`.* TO 'dimon'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) ####### mysql> use test Database changed mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.30 | +-----------+ 1 row in set (0.00 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> create temporary table t2(id int not null); Query OK, 0 rows affected (0.05 sec) mysql> create temporary table t3 select * from t2; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement mysql> mysql> select user(); +-----------------+ | user() | +-----------------+ | dimon@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> show grants for 'dimon'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for dimon@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dimon'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT ALL PRIVILEGES ON `test`.* TO 'dimon'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) ###### mysql> select version(); +--------------+ | version() | +--------------+ | 5.5.31-debug | +--------------+ 1 row in set (0.00 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> create temporary table t2(id int not null); Query OK, 0 rows affected (0.03 sec) mysql> create temporary table t3 select * from t2; ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement mysql> select user(); +-----------------+ | user() | +-----------------+ | dimon@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> show grants for 'dimon'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for dimon@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dimon'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT ALL PRIVILEGES ON `test`.* TO 'dimon'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
[27 Mar 2013 13:43]
MySQL Verification Team
###### Not affected mysql> select version(); +------------------+ | version() | +------------------+ | 5.6.10-debug-log | +------------------+ 1 row in set (0.01 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create temporary table t2(id int not null); Query OK, 0 rows affected (0.10 sec) mysql> create temporary table t3 select * from t2; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select user(); +-----------------+ | user() | +-----------------+ | dimon@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> show grants for 'dimon'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for dimon@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dimon'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT ALL PRIVILEGES ON `test`.* TO 'dimon'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ ######### mysql> select version(); +-----------------+ | version() | +-----------------+ | 5.7.1-m11-debug | +-----------------+ 1 row in set (0.00 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.01 sec) mysql> create temporary table t2(id int not null); ERROR 1046 (3D000): No database selected mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create temporary table t2(id int not null); Query OK, 0 rows affected (0.00 sec) mysql> create temporary table t3 select * from t2; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select user(); +-----------------+ | user() | +-----------------+ | dimon@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> show grants for 'dimon'@'localhost'; +--------------------------------------------------------------------------------------------------------------+ | Grants for dimon@localhost | +--------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dimon'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' | | GRANT ALL PRIVILEGES ON `test`.* TO 'dimon'@'localhost' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
[1 Jul 2016 10:28]
Erlend Dahl
Fixed as duplicate of Bug#62008 read-only option does not allow inserts/updates on temporary tables