Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB Read-Only Slave and Temporary Tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB Read-Only Slave and Temporary Tables

    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

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

    Comment


    • #3
      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 fy=195

      Comment


      • #4
        Whole lot longer than 134 days now... I just ran into the same issue with MySQL 5.5.31

        -Michael

        Comment

        Working...
        X