Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 to MyISAM. 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.

share|improve this question
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 '12 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 '12 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 '12 at 16:40
add comment

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
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 '12 at 16:20
add comment

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

share|improve this answer
    
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 '12 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. –  randy melder Jun 25 '12 at 17:51
    
According to the documentation, default-storage-engine applies to both regular and temporary tables in < 5.5. –  Andrew Jun 25 '12 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. –  randy melder Jun 27 '12 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 '12 at 18:42
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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