Aruba-MySQL: can't create/change table to engine=INNODB

Question

We have our database stored in aruba (mysql.aruba.it) where there is a table called "task". Because of many changes in the requirements we decided to drop the table and create it again from 0 with different fields and constraints. The problem is that MySQL/Aruba won't let us create a called "task" table anymore. Or better, we can create another task table only with engine MyISAM but we need INNODB because we will use contraints and foreign keys in the table. So I have tried to create a MyISAM table and then convert it into INNODB but I get an error like this:

ALTER TABLE  `task` ENGINE = INNODB
#1025 - Error on rename of './Sql689345_4/#sql-6962_1891f' to './Sql689345_4/task' (errno: -1) 

I don't know why there is this problem with this table: for other tables we have we can drop them and re-create them as many times as we want. Is there a way to fix it?

Answer 1

Take a look at the InnoDB Internals (Picture from Percona CTO Vadim Tkachenko)

Please note the data dictionary inside ibdata1 (the system tablespace)

It sounds like you have a broken data dictionary entry on the task table when it existed

The only way around would be move all the tables into another database.

For starters, suppose you have database mydb1. Create another database mydb2

CREATE DATABASE mydb2;

For example, to move mydb1.mytable into mydb2, do this

ALTER TABLE mydb1.mytable RENAME mydb2.mytable;

Move all your tables sideways into mydb2.

You would then have to drop your old database

DROP DATABASE mydb1;

You should be able to convert the task table in mydb2 into InnoDB.

USE mydb2
ALTER TABLE `task` ENGINE = INNODB;

Give it a Try !!!