MySQL Bugs: #73119: create temporary table ... select fails on read-only slave

Bug #73119	create temporary table ... select fails on read-only slave
Submitted:	25 Jun 2014 20:38	Modified:	26 Jun 2014 7:10
Reporter:	Guillaume Giroux	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S3 (Non-critical)
Version:	5.5.36, 5.5.38	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
On a slave set read_only=ON, I get the following with a regular user (no super privileges, but with the create temporary tables privilege) :

mysql> create temporary table foobar select 1 from SOME_REAL_TABLE;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

The documentation for the read_only variable says "read_only does not apply to TEMPORARY tables", so I believe this might be a bug.

How to repeat:
On a read-only slave, with a non super user, with the create temporary priv:

mysql> create temporary table foobar select 1 from SOME_REAL_TABLE;

Hello Guillaume,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

// 5.5.38

mysql> use test
Database changed
mysql>
mysql> CREATE DATABASE bug73119;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> GRANT USAGE,CREATE TEMPORARY TABLES, INSERT, UPDATE, SELECT ON bug73119.* TO 'ushastry'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> use bug73119
Database changed
mysql> create table t1(id int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_bug73119 |
+--------------------+
| t1                 |
+--------------------+
1 row in set (0.00 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@cluster-repo mysql-5.5.38]# bin/mysql -u ushastry -p123 bug73119

..
.
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.38-debug-log |
+------------------+
1 row in set (0.00 sec)
mysql> SHOW GRANTS FOR current_user();
+-----------------------------------------------------------------------------------------------------------------+
| Grants for ushastry@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ushastry'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, CREATE TEMPORARY TABLES ON `bug73119`.* TO 'ushastry'@'localhost'                 |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> show tables;
+--------------------+
| Tables_in_bug73119 |
+--------------------+
| t1                 |
+--------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TEMPORARY TABLE t1myisam (id int) ENGINE=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TEMPORARY TABLE t1innodb (id int) ENGINE=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create temporary table foobar select 1 from t1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql>

mysql> select * from foobar;
Empty set (0.00 sec)

mysql> create temporary table foobar select 1 from t1;
ERROR 1050 (42S01): Table 'foobar' already exists
mysql>

// 5.6.20 - Not affected

mysql> CREATE DATABASE bug73119;
Query OK, 1 row affected (0.00 sec)

mysql> use bug73119;
Database changed
mysql> GRANT USAGE,CREATE TEMPORARY TABLES, INSERT, UPDATE, SELECT ON bug73119.* TO 'ushastry'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.00 sec)

mysql> use bug73119
Database changed
mysql>  create table t1(id int not null);
Query OK, 0 rows affected (0.42 sec)

mysql>  show tables;
+--------------------+
| Tables_in_bug73119 |
+--------------------+
| t1                 |
+--------------------+
1 row in set (0.00 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> \q
Bye
[root@cluster-repo mysql-advanced-5.6.20]# bin/mysql -u ushastry -p123 -S /tmp/mysql_wb.sock
..
..
mysql>  use bug73119;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW GRANTS FOR current_user();
+-----------------------------------------------------------------------------------------------------------------+
| Grants for ushastry@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ushastry'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, CREATE TEMPORARY TABLES ON `bug73119`.* TO 'ushastry'@'localhost'                 |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show tables;
+--------------------+
| Tables_in_bug73119 |
+--------------------+
| t1                 |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE t1myisam (id int) ENGINE=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql>  CREATE TEMPORARY TABLE t1innodb (id int) ENGINE=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql>  CREATE TEMPORARY TABLE t1innodb1 (id int) ENGINE=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> create temporary table foobar select 1 from t1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from foobar;
Empty set (0.00 sec)