Client reports that it worked in 5.1.73

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).

This bug still exists in MariaDB 10.1.14.
One year since last activity. It seems that it is not a priority bug.
However it seems to have been fixed recently in MySQL: http://bugs.mysql.com/bug.php?id=62008

In MySQL they've implemented the second approach "don't mark transactions read-write if no real storage engine is affected".