# InnoDB Read-Only Slave and Temporary Tables

**URL:** <https://forums.percona.com/t/innodb-read-only-slave-and-temporary-tables/1721>\
**Category:** Other MySQL® Questions\
**Created:** [December 9, 2011, 2:51pm UTC](https://forums.percona.com/t/innodb-read-only-slave-and-temporary-tables/1721 "2011-12-09T14:51:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![erich](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@erich](https://forums.percona.com/u/erich)\
**Post date:** [December 9, 2011, 2:51pm UTC](https://forums.percona.com/t/innodb-read-only-slave-and-temporary-tables/1721/1 "2011-12-09T14:51:36Z")

</div>

Hi,  
I am having a problem with Creating temporary tables on InnoDB on a slave that is read-only.

Server version: 5.1.54-rel12.5-log Percona Server with XtraDB (GPL), Release 12.5, Revision 188

±--------------±------+  
| Variable\_name | Value |  
±--------------±------+  
| read\_only | ON |  
±--------------±------+

±---------------------------------------------------------- --------------------+  
| Grants for dolphin@localhost |  
±---------------------------------------------------------- --------------------+  
| GRANT SELECT, INSERT, CREATE TEMPORARY TABLES ON _._ TO ‘dolphin’@‘localhost’ |  
±---------------------------------------------------------- --------------------+

InnoDB Test Case

| City | CREATE TABLE `City` (  
`ID` int(11) NOT NULL AUTO\_INCREMENT,  
`Name` char(35) NOT NULL DEFAULT ‘’,  
`CountryCode` char(3) NOT NULL DEFAULT ‘’,  
`District` char(20) NOT NULL DEFAULT ‘’,  
`Population` int(11) NOT NULL DEFAULT ‘0’,  
PRIMARY KEY (`ID`),  
KEY `CountryCode` (`CountryCode`),  
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)  
) ENGINE=InnoDB AUTO\_INCREMENT=4080 DEFAULT CHARSET=latin1 |

CREATE TEMPORARY TABLE foo like City;  
Query OK, 0 rows affected (0.09 sec)

INSERT INTO foo select \* from City;  
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

* * *

MyISAM test case

| City | CREATE TABLE `City` (  
`ID` int(11) NOT NULL AUTO\_INCREMENT,  
`Name` char(35) NOT NULL DEFAULT ‘’,  
`CountryCode` char(3) NOT NULL DEFAULT ‘’,  
`District` char(20) NOT NULL DEFAULT ‘’,  
`Population` int(11) NOT NULL DEFAULT ‘0’,  
PRIMARY KEY (`ID`)  
) ENGINE=MyISAM AUTO\_INCREMENT=4080 DEFAULT CHARSET=latin1 |

CREATE TEMPORARY TABLE foo like City;  
Query OK, 0 rows affected (0.01 sec)

INSERT INTO foo select \* from City;  
Query OK, 4079 rows affected (0.01 sec)  
Records: 4079 Duplicates: 0 Warnings: 0

* * *

On both InnoDB and MyISAM this works

CREATE TEMPORARY TABLE foo AS SELECT \* FROM City;  
Query OK, 4079 rows affected (0.00 sec)  
Records: 4079 Duplicates: 0 Warnings: 0

This problem is stopping my upgrade from MySQL 5.0.58 on which this works.

Is this a bug or intentional?

Thanks,  
-Eric

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [December 12, 2011, 8:18am UTC](https://forums.percona.com/t/innodb-read-only-slave-and-temporary-tables/1721/2 "2011-12-12T08:18:54Z")

</div>

Looks like it might be a bug, but I haven’t seen it before. I’d check the bugs database first.

---

<div class="post-metadata">

**Author:** ![erich](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@erich](https://forums.percona.com/u/erich)\
**Post date:** [December 12, 2011, 9:57am UTC](https://forums.percona.com/t/innodb-read-only-slave-and-temporary-tables/1721/3 "2011-12-12T09:57:54Z")

</div>

I believe this could be the same bug 62008.  
134 days and still ticking.

I updated the bug with my findings.

Thanks,

[http://bugs.mysql.com/bug.php?id=62008&thanks=3&noti](http://bugs.mysql.com/bug.php?id=62008&thanks=3&noti) fy=195

---

<div class="post-metadata">

**Author:** ![mcubedpe](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@mcubedpe](https://forums.percona.com/u/mcubedpe)\
**Post date:** [June 14, 2013, 8:14pm UTC](https://forums.percona.com/t/innodb-read-only-slave-and-temporary-tables/1721/4 "2013-06-14T20:14:10Z")

</div>

Whole lot longer than 134 days now… I just ran into the same issue with MySQL 5.5.31

-Michael