MySQL Bugs: #62008: read-only option does not allow inserts/updates on temporary tables

Your comment was added to the bug successfully.

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

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

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)

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

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)

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.

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.

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

verified still exists in 5.5.18-55

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)

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

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

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.

Bug#68791 insert failes into temp tables with read_only flag

was marked as a duplicate.