Not the answer you need?
Register and ask your own question!

InnoDB Read-Only Slave and Temporary Tables

ericherich EntrantCurrent User Role Participant
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 [email protected] |
+

+
| 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

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.