MySQL Rails migration error: "Error on rename of schema_migration (errno: -1)"

Question

Hi there, I'm a PHP dev and I'm new to Rails but have been getting on pretty well, everything seems pretty straightforward. However, up until this morning I have been using SQLite and decided to move what I'm building to MySQL. rake db:create works perfectly, but when I attempt to rake db:migrate I get the following error:

rake aborted! Mysql::Error: Error on rename of './test_development/schema_migrations' to './test_development/#sql2-c7b-c' (errno: -1): CREATE UNIQUE INDEX unique_schema_migrations ON schema_migrations (version)

Thinking that it might be an error in my migration scripts, I created a clean rails project with the simplest of tables and get the same error. So, I reinstalled MySQL and the mysql gem to ensure it wasn't something ill with either of those and I'm still having no luck. Here's the versions I'm running:

• Rails 2.3.4
• Ruby 1.8.6
• MySQL 5.1.40
• MySQL Gem 2.8.1

I have a feeling it could be InnoDB related, as I've had problems with that engine before on this box. But, if anyone can help me out I'd be extremely grateful as it's stopping me progressing.

Thanks,

Kieran

UPDATE: As requested by Phil here's the results of:

SHOW ENGINE INNODB STATUS

and

SHOW VARIABLES LIKE 'innodb%'

Answer 1

You've got the innodb_force_recovery option set to 6. This was presumably changed from the default 0 to fix an earlier problem. InnoDB won't allow anything much other than SELECT, CREATE TABLE and DROP TABLE in this mode.

Try shutting down MySQL and then editing your my.cnf file. Set innodb_force_recovery to 0 (or remove the option) and then restart MySQL.