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: | |
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 |
[27 Jul 2011 22:32]
Ivan Stanojevic
[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.