Details
Major
Description
MySQL 5.5 refman for read-only:
=== http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_read-only
Cause the slave to permit no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables.
===
However, this appears to not be true.
Upstream a bug has existed since 2011: http://bugs.mysql.com/bug.php?id=62008
I've just repeated this with MariaDB 10.0.12
mysql -u root GRANT SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES ON *.* TO slaveuser@localhost IDENTIFIED BY 'slaveuser'; SET GLOBAL read_only=1; mysql -u slaveuser -pslaveuser SELECT CURRENT_USER(); -- verify we're not anonymous SELECT @@SQL_LOG_BIN; -- verify binary logging is enabled USE test MariaDB [test]> CREATE TABLE t (i INT); ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement MariaDB [test]> CREATE TEMPORARY TABLE t (i INT); Query OK, 0 rows affected (0.20 sec) MariaDB [test]> INSERT INTO t VALUES (1); ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
===
As per the original upstream bug report, the problem only shows up when binary logging is enabled. Obviously that's merely a fact and not a valid workaround 
Furthermore, MyISAM behaviour on the above is also broken.
If a specific ENGINE=... is provided for the temporary table, and the engine is MyiSAM, then the INSERT still reports the read-only error but the table will in fact contain the new row.
From the above, I guess it's the binary log code reporting the error, and I would reckon that that's also where the bug exists.
Note: Client applications are failing on this after upgrading to MariaDB 10.0, which is why I have marked the bug as critical. While the upstream bug info is somewhat ambiguous in terms of when the problem first appeared, it definitely existed in 5.5. At some point in the past, it didn't occur, and obviously the manual indicates how it should work. So it's a regression.
Mind that any testcase needs to ensure that the binlog is enabled and the other possibilities described above are covered, so that it effectively catches the problem.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem appeared in MySQL 5.5 somewhere between 5.5.2 and 5.5.3.
MTR test case:
--source include/have_log_bin.inc GRANT SELECT,INSERT,UPDATE,DELETE,CREATE TEMPORARY TABLES ON *.* TO slaveuser@localhost; SET GLOBAL read_only=1; --connect (con1,localhost,slaveuser,,) SELECT CURRENT_USER(); SELECT @@SQL_LOG_BIN; USE test; --error ER_OPTION_PREVENTS_STATEMENT CREATE TABLE t (i INT); # ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement CREATE TEMPORARY TABLE t (i INT); # Query OK, 0 rows affected (0.20 sec) --error ER_OPTION_PREVENTS_STATEMENT INSERT INTO t VALUES (1); # ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement SELECT * FROM t; # Cleanup --disconnect con1 --connection default SET GLOBAL read_only = 0; DROP USER slaveuser@localhost;
Ok. What happens here is:
- binlog wraps every update in a read-write transaction
- MariaDB refuses to commit a read-write transaction in read-only mode.
Note that the error happens at commit time, after the actual insert has happened.
Possible fixes are:
- don't wrap MyISAM updates in a transaction
- don't mark transactions read-write if no real storage engine is affected (only binlog writes).
Both fixes change results of lots of tests and have a high risk of breaking something. Thus we can consider this only for 10.1 branch at the earliest.
Furthermore, it seems that the desired behavior is to prevent any binlog writes in the read-only mode, otherwise one cannot easily fail-over as slave binlogs will contain some random events added between master events, even if the slave is read-only. Note that a correct read-only binlog would prevent also CREATE TEMPORARY TABLE in the above test case. And it would've aborted INSERT before it is completed, not at commit time.
This has also a very high potential of breaking existing applications, so I'll move this whole but report to 10.1.
Ok - thanks for that Serg.
So is the intent to fix this for 10.1 ?
Or would you prefer to document this and leave it as-is?
Applications can of course use a different GRANT for slave access, and this can be a good work-around for now. However, if all grants are available on the slave, an application could potentially still connect with the wrong login and thus write to the slave.
To fix, somehow. CREATE TEMPORARY TABLE is allowed, but INSERT is not — that's certainly a bug.
See also MDEV-8065 / MySQL#62008 about CREATE TEMPORARY TABLE .. AS SELECT, which is probably a special case of the above problem.
Please also note that CREATE TEMPORARY TABLE .. AS SELECT with InnoDB fails even without binary logging. The table gets created, but not populated, and the error is returned.
See MDEV-8270 about it (closed as a duplicate of this bug report).
Client reports that it worked in 5.1.73