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