Bug #66188 Cannot use CREATE TEMPORARY...INSERT with InnoDB when read_only is set
Submitted: 3 Aug 2012 20:02 Modified: 5 Oct 2012 19:05
Reporter: Ari Pringle Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.5.23, 5.5.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, RDS, read_only, temporary tables

[3 Aug 2012 20:02] Ari Pringle
Description:
I have a read replica running 5.5.23 in Amazon RDS.

When the READ_ONLY flag is ON, I am unable to create temporary InnoDB tables using the CREATE TEMPORARY...SELECT syntax. However, when creating a temporary MyISAM table, this syntax works fine.

I AM able to create the InnoDB table with CREATE TEMPORARY, and then insert data using INSERT...SELECT. This seems to be different behavior than reported in bug #62008 (also, our LOG_BIN flag is OFF, whereas #62008 appears to only be problematic when LOG_BIN is ON).

How to repeat:
--
-- Create data source on Master server
--
mysql> create database TempTestDB;
Query OK, 1 row affected (0.06 sec)
mysql> use TempTestDB
Database changed
mysql> create table TestTable(id int);
Query OK, 0 rows affected (0.13 sec)
mysql> INSERT INTO TestTable VALUES (1);
Query OK, 1 row affected (0.06 sec)

--
-- On Slave server (with read_only flag set)
--
mysql> use TempTestDB
Database changed

--
-- Error occurs when using CREATE TEMPORARY...SELECT syntax
-- with InnoDB engine. The table is created, but no data is
-- inserted.
--
mysql> CREATE TEMPORARY TABLE TempInnoDB ENGINE=InnoDB SELECT * FROM TestTable;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mysql> desc TempInnoDB;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

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

--
-- However, I am able to create the table with CREATE TEMPORARY
-- and then insert data using SELECT...INSERT
---
mysql> CREATE TEMPORARY TABLE TempInnoDB_NoData (id int) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO TempInnoDB_NoData SELECT * FROM TestTable;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

--
-- The CREATE TEMPORARY...SELECT syntax has no problems with MyISAM
--
mysql> CREATE TEMPORARY TABLE TempMyISAM ENGINE=MyISAM SELECT * FROM TestTable;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
[4 Aug 2012 7:49] Valeriy Kravchuk
Please, check if the same problem still happens with a newer version, 5.5.27.
[6 Aug 2012 19:38] Ari Pringle
I have confirmed that this same behavior occurs using 5.5.27 (installed on an  CentOS 5 machine using the x64 RPMs provided at dev.mysql.com)
[5 Sep 2012 19:05] MySQL Verification Team
Please provide both my.cnf file (master/slave) private if you wish. Thanks.
[6 Oct 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".