MySQL Bugs: #64992: Temporary table in a database on a read-only instance can be overwritten

Bug #64992	Temporary table in a database on a read-only instance can be overwritten
Submitted:	16 Apr 2012 11:02	Modified:	10 Aug 2012 1:35
Reporter:	Tomokazu YONETANI	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5.21, 5.0.97, 5.1.63, 5.524, 5.6.6	OS:	Linux (SL 6.1(x86_64))
Assigned to:		CPU Architecture:	Any

Description:
You can create a temporary table in a database on a read-only instance(@@read_only returns 1) as a non-administrative user (but granted ALL on that database)
You can't add or modify any data to it.

You can then _overwrite_ it by another CREATE TABLE statement (without TEMPORARY keyword between it) with a different DDL.  The statement seems to succeed (this is already odd, as you can't usually create a regular table on a read-only instance, even if you have CREATE privilege), but DESC command returns the original definition rather than the new one.

If you re-connect to the same database with the same user, the overwritten table has been created with the new definition.

By the way, what's the point of allowing the creation of a temporary table on a read_only instance if you can't change it at all?

How to repeat:
Connect as a non-privileged user to a MySQL server running in read-only mode, and issue the following set of SQLs:

mysql> create temporary table t(id bigint unsigned not null primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> desc t;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t(id)values(0);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mysql> create table t(foo varchar(12), bar varchar(24));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> quit
Bye

Re-connect to the same database as the same user, and issue DESC command:
mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| foo   | varchar(12) | YES  |     | NULL    |       |
| bar   | varchar(24) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into t(foo,bar)values('1','2');
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_only: "When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. " This definition says nothing about DDL statements.

Hi, thank you for pointing out that I chose a wrong category or terminology.
Since it doesn't seem like you read the description because of that, here's a shorter version; if this still doesn't make sense, well, I'll give up.

Normally, a user without SUPER privilege cannot create a regular table if mysqld is running in read-only mode.  BUT he can create a temporary table on the same database, is this correct?  AND he can define a regular table with the same name as the temporary table within the same session, and that regular table remains on that database, is this correct?

Thank you for the feedback.

You are right: I granted SUPER privilege for my test user occasionally.

But INSERT into temporary table should not fail.

Anyway verified as described.

Temporary tables are removed when the session disconnects, so you are basically trying to insert into a base table. Also, read_only does apply to DDL statements.

Like Davi says, INSERT should fail after re-connecting since the temporary 
table is then gone. The real problem here is CREATE TABLE after first
creating a temporary table.

# Make non-SUPER user
GRANT SELECT ON *.* TO test@localhost;
SET GLOBAL read_only= 1;
--connect(con1, localhost, test)

# This should fail
--error ER_OPTION_PREVENTS_STATEMENT
CREATE TABLE t1(a INT, b INT);

# This should work
CREATE TEMPORARY TABLE t1(a INT);

# This should still fail, but doesn't
CREATE TABLE t1(a INT, b INT);

# Cleanup
--connection default
SET GLOBAL read_only= 0;
--disconnect con1
DROP USER test@localhost;
DROP TABLE t1;

Jon Olav,

please see this:

mysql> create temporary table t(id bigint unsigned not null primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> desc t;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | NO   | PRI | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into t(id)values(0);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot
execute this statement

This insert supposed to go into temporary table and should not fail. Even if there is existent persistent table.

I can not repeat this issue.

Sveta, me neither.

Which leaves the problem I made the above MTR test case for.

Looks like the remaining issue is a duplicate of Bug#62008 (binary log enabled and transactional temp table). There are also bug reports about temp tables shadowing base tables.

deny_updates_if_read_only_option() was used to check if a query was allowed to excute. if we first create a  temporary table and then create a real table, the later operation will be allowed.

i guess there must be some problem with the following code in deny_updates_if_read_only_option() 

  const my_bool update_real_tables=
    some_non_temp_table_to_be_updated(thd, all_tables) &&
    !(create_temp_tables || drop_temp_tables);

Generally some_non_temp_table_to_be_updated() should return true and create_temp_tables equals zero while we are creating a real table.

but actully the return value of some_non_temp_table_to_be_updated()  is 0, because the same table name was found in  temporary table list.

function some_non_temp_table_to_be_updated:

static bool some_non_temp_table_to_be_updated(THD *thd, TABLE_LIST *tables)
{
  for (TABLE_LIST *table= tables; table; table= table->next_global)
  {
    DBUG_ASSERT(table->db && table->table_name);
    if (table->updating && !find_temporary_table(thd, table))
      return 1;
  }
  return 0;
}

so i think the value of update_real_tables should be calculated  like this :

  const my_bool update_real_tables=
    (lex->sql_command == SQLCOM_CREATE_TABLE || some_non_temp_table_to_be_updated(thd, all_tables)) &&
    !(create_temp_tables || drop_temp_tables);

I don't know my guess is correct , but this small change really works

Noted in 5.7.0 changelog.

If read_only is enabled, it is still permitted to create TEMPORARY
tables. But in this case, a non-TEMPORARY table with the same name
could also be created, which should not be permitted.