MySQL Bugs: #4544: read_only also affects temporary tables

Bug #4544	read_only also affects temporary tables
Submitted:	14 Jul 2004 12:12	Modified:	23 Nov 2005 16:07
Reporter:	Anders Henke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0, 4.1, 5.0	OS:	Linux (Linux)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
Our replication slaves are configured with SET GLOBAL READ_ONLY=ON to ensure that not any user can write to a replication slave, but on the other hand many applications make extensive use of  temporary tables.

However, MySQL chokes on such a server with a misleading error message (ERROR 1223: Can't execute the query because you have a conflicting read lock).

As soon as the global variable READ_ONLY is set to OFF, the query runs without any problems.
As temporary tables aren't affecting the SQL server's data, the current behaviour is a too-strict implementation and close to a "real" bug.

How to repeat:
admin-mysql> set global read_only=on;

user-mysql>  create temporary table tmptable select [...];
ERROR 1223: Can't execute the query because you have a conflicting read lock

admin-mysql> set global read_only=off;

user-mysql>  create temporary table tmptable select [...];
Query OK, 3931 rows affected (0.03 sec)
Records: 3931  Duplicates: 0  Warnings: 0

Suggested fix:
As temporary tables only affect the current MySQL session, it shouldn't have any negative impact to allow a user (with the create_tmp_table-privilege) on a readonly-Host still to run commands for creating and dropping temporary tables and also inserting, modifying and deleting data in such temporary tables.

For the very careful ones, one might change READ_ONLY from being a binary flag to a three-state-flag (e.g. ON, OFF and RELAXED).

Readjusted to non-critical bug:

I just noticed that 'grant select on db.* to $user' also grants a fresh user Create_tmp_table_priv and Lock_tables_priv, so if adding a 'readonly'-user implies that this user is automatically given temporary table and table locking rights, a readonly-server should also allow its users to make use of those rights and allow its users to make use of temporary tables.

MySQL doesn't grant  Create_tmp_table_priv and Lock_tables_priv automatically if you grant a select_priv only. What happens is that your privilege tables are of the old structure, and do not support Create_tmp_table_priv and Lock_tables_priv privileges. When MySQL deals wil such old tables it falls back to old behaviour - e.g. Select_priv was used to give rights to use LOCK TABLES.

Hi Anders.
Well, here's the truth:
- you are 100% right requesting this feature; creating temp tables can be just  a way to achieve a particular read (especially before MySQL 4.1 where the support for subselects makes it less crucial). So forbidding temp tables prevents quite a few reads.
- but it's not sure this change is implementable without shaking the current code upside-down too much. I am testing a patch and will let you know.
About that bad message which you got, it's true; we already fixed in 4.1.

Guilhem

working on pushing a patch for this bug in 5.0, as it has been patch pending for more than one year.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/31170

Starting from MySQL 5.0.16, read_only=1 will allow CREATE TEMPORARY TABLE and all statements modifying no non-temporary tables.
For example, if there is a stored procedure, which has some substatements which modify temporary tables and some others which modify non-temporary tables, the CALL statement will be allowed, then the procedure will fail on the first statement modifying a non-temporary tables.
ChangeSet@1.1932.312.1, 2005-10-17 10:52:34+02:00, guilhem@mysql.com
  Fix for BUG#4544 "read_only also affects temporary tables":
  the READ_ONLY global variable now allows statements which are to update only temporary tables
  (note: if a statement, after parse stage, looks like it will update a non-temp table, it will be rejected,
  even if at execution it would have turned out that 0 rows would be updated; for example
  UPDATE my_non_tem_table SET a=1 WHERE 1 = 0; will be rejected).

Noted in 5.0.16 changelog.
Modified descriptions for --read-only and
read_only in the manual.