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:
None 
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 
Description:
User with all grant privilegies can't insert into temporary tables with read_only flag. Documentation tell "read_only does not apply to TEMPORARY tables, nor does it prevent the server from inserting rows into the log tables". It's work's  on 5.1.46 and 5.6.10, but doesn't work on 5.5.29.  

How to repeat:
mysql> show variables like 'version';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| version       | 5.5.29-0ubuntu0.12.04.2 |
+---------------+-------------------------+
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> show grants for current_user;
+-------------------------------------------------------------------------------------------------------------+
| Grants for bill@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bill'@'localhost' IDENTIFIED BY PASSWORD '*5CFAA65DE2C8101B4E2C0DB8F362A603B6B57209' |
| GRANT ALL PRIVILEGES ON `bgbilling`.* TO 'bill'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create temporary table test33 like contract;
Query OK, 0 rows affected (0.04 sec)

mysql> create temporary table test44 select * from contract;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql>
[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