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
Triage: Needs Triage: D1 (Critical)

[16 Apr 2012 11:02] Tomokazu YONETANI
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
[16 Apr 2012 16:50] Sveta Smirnova
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.
[17 Apr 2012 0:31] Tomokazu YONETANI
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?
[17 Apr 2012 8:47] Sveta Smirnova
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.
[17 Apr 2012 22:32] Davi Arnaut
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.
[18 Apr 2012 10:48] Jon Olav Hauglid
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;
[18 Apr 2012 18:53] Sveta Smirnova
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.
[19 Apr 2012 6:49] Jon Olav Hauglid
Sveta, me neither.

Which leaves the problem I made the above MTR test case for.
[19 Apr 2012 6:54] Davi Arnaut
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.
[10 May 2012 9:49] zhai weixiang
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
[10 Aug 2012 1:35] Paul Dubois
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.