Bug #62008 read-only option does not allow inserts/updates on temporary tables
Submitted: 27 Jul 2011 22:32 Modified: 27 May 2016 14:55
Reporter: Ivan Stanojevic Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.5.14, 5.5.16 OS:Linux (CentOS 5.6)
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D3 (Medium)

[27 Jul 2011 22:32] Ivan Stanojevic
Description:
Running 5.5.14 in read only we can create temporary tables but can not insert or update records in the table. When we try we get Error 1290 : The MySQL server is running with the --read-only option so it cannot execute this statement.

In prior versions it was possible to perform these operations and the 5.5 manual indicates this should continue to be true.

How to repeat:
1. Install 5.5.14 and run with the read-only option
2. Create a temporary table
3. Insert a record

Suggested fix:
Allow data manipulation on temporary tables when the server is running with read-only
[28 Jul 2011 3:42] Valeriy Kravchuk
Is it a slave server also? I can not repeat this problem with a stand-alone read-only server on Mac OS X:

macbook-pro:mysql-5.5.14-osx10.5-x86_64 openxs$ bin/mysql -uroot testWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | ON     |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)

mysql> create temporary table temp(c1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into temp values(1);
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table mytem(c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into mytem values(1);
Query OK, 1 row affected (0.00 sec)
[28 Jul 2011 4:42] Ivan Stanojevic
Hi Valeriy,

In your test you used the user root which I assume you left the SUPER privilege on for. Try the same queries without SUPER (don't forget to flush privileges). Users with SUPER can always write to a read only server. Here was my results with your query and a user without SUPER:

mysql -u root test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.14-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> create temporary table temp(c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into temp values(1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
[28 Jul 2011 5:31] Valeriy Kravchuk
Sorry, my fault to use -uroot (just a habit...). I do not see the problem with other user just created anyway:

macbook-pro:mysql-5.5.14-osx10.5-x86_64 openxs$ bin/mysql -ut test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, 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> show grants;
+---------------------------------------+
| Grants for t@localhost                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 't'@'localhost' |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| t@localhost    |
+----------------+
1 row in set (0.00 sec)

mysql> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | ON     |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)

mysql> create temporary table temp(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp values(1);
Query OK, 1 row affected (0.00 sec)
[28 Jul 2011 6:34] Ivan Stanojevic
Okay I found it I think. Try your same statements with these settings in your my.cnf file:

[mysqld]
log-bin=mysql-bin
read-only

It seems the log-bin setting is necessary to duplicate the error.
[31 Jul 2011 16:08] Valeriy Kravchuk
You are right:

macbook-pro:5.5 openxs$ bin/mysql -ut test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.16-debug-log Source distribution

Copyright (c) 2000, 2011, 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> show grants;
+---------------------------------------+
| Grants for t@localhost                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 't'@'localhost' |
+---------------------------------------+
1 row in set (0.02 sec)

mysql> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | ON     |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)

mysql> create temporary table temp(c1 int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into temp values(1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

While on 5.1 we have:

macbook-pro:5.1 openxs$ bin/mysql -ut test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.59-debug-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> show grants;
+---------------------------------------+
| Grants for t@localhost                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 't'@'localhost' |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'read%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_buffer_size     | 131072 |
| read_only            | ON     |
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
3 rows in set (0.00 sec)

mysql> create temporary table temp(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp values(1);
Query OK, 1 row affected (0.00 sec)

Looks like GRANT ALL ON *.* TO t@localhost allows this user to insert into temporary tables, but this is a workaround that can not be used in general case.

Yes, I know about http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_create-temporary-tabl..., and looks like CREATE TEMPORARY TABLE privilege is NOT needed to create temporary table neither on 5.1, nor on 5.5. This is another potential bug.
[12 Dec 2011 15:24] Eric Hernandez
I am having a similar problem with MySQL 5.1.54

I think the problem is limited to InnoDB tables only.  It works fine with MyISAM.

InnoDB Test Case:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+

+----------------------------------------------------------- --------------------+
| Grants for dolphin@localhost |
+----------------------------------------------------------- --------------------+
| GRANT SELECT, INSERT, CREATE TEMPORARY TABLES ON *.* TO 'dolphin'@'localhost' |
+----------------------------------------------------------- --------------------+

InnoDB Test Case

| City | CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |

CREATE TEMPORARY TABLE foo like City;
Query OK, 0 rows affected (0.09 sec)

INSERT INTO foo select * from City;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

Repeat the same test on MyISAM and it works fine

MyISAM test case:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+

| City | CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |

CREATE TEMPORARY TABLE foo like City;
Query OK, 0 rows affected (0.01 sec)

INSERT INTO foo select * from City;
Query OK, 4079 rows affected (0.01 sec)
Records: 4079 Duplicates: 0 Warnings: 0
[4 Jan 2012 19:24] David Lyons
verified still exists in 5.5.18-55
[7 Jul 2012 1:51] Jesper wisborg Krogh
It only happens with binlog_format = STATEMENT or MIXED. So a workaround is to use the row-based format:

[mysqld]
read_only
log_bin       = ...
binlog_format = ROW

5.5.21_readonly: test> SHOW GRANTS;
+---------------------------------------+
| Grants for t@localhost                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 't'@'localhost' |
+---------------------------------------+
1 row in set (0.02 sec)

5.5.21_readonly: test> CREATE TEMPORARY TABLE t1 (id int);
Query OK, 0 rows affected (0.07 sec)

5.5.21_readonly: test> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.09 sec)
[7 Jul 2012 2:26] Jesper wisborg Krogh
Reproducible in 5.1.63 as well:

5.1.63_readonly: test> SELECT * FROM information_schema.SESSION_VARIABLES WHERE VARIABLE_NAME IN ('binlog_format', 'log_bin', 'read_only');
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| READ_ONLY     | ON             |
| LOG_BIN       | ON             |
| BINLOG_FORMAT | MIXED          |
+---------------+----------------+
3 rows in set (0.01 sec)

5.1.63_readonly: test> SHOW GRANTS;
+---------------------------------------+
| Grants for t@localhost                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 't'@'localhost' |
+---------------------------------------+
1 row in set (0.00 sec)

5.1.63_readonly: test> CREATE TEMPORARY TABLE t1 (id int);
Query OK, 0 rows affected (0.07 sec)

5.1.63_readonly: test> INSERT INTO t1 VALUES (1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
[7 Jul 2012 2:53] Jesper wisborg Krogh
Reproducible with MyISAM as well:

5.5.25a_readonly: test> CREATE TEMPORARY TABLE t2 (id int) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

5.5.25a_readonly: test> INSERT INTO t2 VALUES (1);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
[27 May 2016 14:55] David Moss
Posted by developer:
 
Thanks for your feedback, this has been fixed in upcoming versions and the following was added to the changelog:
When using statement-based or mixed binary logging format with --read-only=ON, it was not possible to modify temporary tables.
[1 Jul 2016 10:28] Erlend Dahl
Bug#68791 insert failes into temp tables with read_only flag

was marked as a duplicate.