3

We're trying out MySQL 5.5, and have installed it on one of our slave servers as a test run. Our main server is 5.0, and we have zero optimization here for InnoDB, so I want everything to just be MyISAM for now. I've disabled InnoDB everywhere I can think, but the replication is throwing errors like:

Error 'Unknown storage engine 'InnoDB'' on query. Default database: ''. Query: 'CREATE TEMPORARY TABLE ... SELECT ...

The query never specifies the engine is InnoDB. default-storage-engine is set toMyISAM. innodb is set to OFF. Why is it trying to create this termporary table as InnoDB? This is 5.5, not 5.6, so we don't have the default-tmp-storage-engine variable yet. Please help, this is driving me insane.

Improve this question
3
  • 4
    Rolando provided the answer to your question as asked, but I question your reasons for using MyISAM. There are virtually no good reasons to use MyISAM anymore and the ease of which MyISAM tables become corrupted is a very good reason not to use it.
    – Aaron Brown
    Jun 25, 2012 at 15:38
  • Simply put, it's more important to get the replication caught up using MyISAM than taking the time to optimize InnoDB to the level that we have MyISAM optimized. I fully intend to use InnoDB, but not at this very moment. InnoDB tables were taking days to do what MyISAM can do in hours, so yes, there IS a good reason to use MyISAM.
    – Andrew
    Jun 25, 2012 at 16:17
  • Son of a gumble, I have a theory. I can't see the entire query, it's being cut off, but my guess is, it's trying to create this temporary table based on one of the tables that was created as InnoDB before I noticed the problem, and thus, by doing "CREATE ... SELECT", it's trying to create the temporary table in the image of the InnoDB table. That's the best I have to go on at the moment...
    – Andrew
    Jun 25, 2012 at 16:40

2 Answers 2

Reset to default
3

To disable InnoDB, add this to /etc/my.cnf on the MySQL 5.5. slave:

[mysqld]
skip-innodb

and run this on the Slave:

service mysql restart

According to MySQL Documentation on CREATE TABLE :

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl SELECT * FROM orig_tbl; For more information, see Section 13.1.17.1, “CREATE TABLE ... SELECT Syntax”.

Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:

CREATE TABLE new_tbl LIKE orig_tbl;

The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.

You must specify the ENGINE upon creation

CREATE TEMPORARY TABLE ... ENGINE=MyISAM SELECT ...

to get around this problem

Improve this answer
1
  • Doesn't work. Still getting the error "Unknown storage engine 'InnoDB'", despite the CREATE TEMPORARY TABLE instruction not specifying InnoDB. skip-innodb is there, default-storage-engine = MyISAM is there. Any ideas why it's trying to create this table as InnoDB?
    – Andrew
    Jun 25, 2012 at 16:20
-1

InnoDB is the default storage engine in MySQL 5.5.5. You need to specify otherwise in the my.cnf by using:

default-storage-engine = MYISAM

-- redacted inaccurate content --

Improve this answer
7
  • I said in my post I already have that in the config. The default storage engine is set to MyISAM, and InnoDB is disabled. Yet when trying to create this temporary table, it insists on trying InnoDB, yet the query to create the table does not specify it should use the InnoDB Engine.
    – Andrew
    Jun 25, 2012 at 16:30
  • Okay, did a little more digging and updated answer. It appears you cannot set the storage type of temporary tables until MySQL 5.6.3.
    – randomx
    Jun 25, 2012 at 17:51
  • According to the documentation, default-storage-engine applies to both regular and temporary tables in < 5.5.
    – Andrew
    Jun 25, 2012 at 18:02
  • Andrew, please supply a link to the documentation that states "default-storage-engine applies to both regular and temporary tables" so we can benefit from the knowledge.
    – randomx
    Jun 27, 2012 at 17:40
  • I was mistaken, it's not <5.5, it's <5.6.3. From dev.mysql.com/doc/refman/5.6/en/storage-engine-setting.html "Before MySQL 5.6.3, default_storage_engine sets the engine for both permanent and TEMPORARY tables."
    – Andrew
    Jun 27, 2012 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.