0

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?

  • Please run SHOW ENGINES; Does InnoDB appear in the list ? Does the Support columns say YES or DEFAULT ? – RolandoMySQLDBA Jun 16 '15 at 15:43
  • Yes InnoDB is supported because up to now I've always used it and all the othee tables are InnoDB... "Task" tables has references to other two tables: clients and projestcs. Should I drop them too to reset everything and make task tables be InnoDB again? – Lory Lory Jun 16 '15 at 15:47
1

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

InnoDB

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

  • Thank you for your explanation, I've understood the problem. Anyway I'm afraid I'm not allowed to create another DB where to move/copy the tables because that aruba account and the DB aren't mine but of my boss. Are you sure that also after dropping the tables that were referenced in the task table, the dictionary won't clean anyway? – Lory Lory Jun 16 '15 at 16:15
  • 1
    You are not dropping the tables, you are renaming them so they land in another database. You may have to talk to the sysadmin for Aruba for assistance in this. Of course, make sure the sysadmin does a back of MySQL beforehand. You may also have to talk to the Dev Team to explain the situation – RolandoMySQLDBA Jun 16 '15 at 16:17
  • 1
    As for the data dictionary, that "pidgeon hole" will still remain, pointing to the old database. The only way to make ibdata1 clean would be to dump all the data, stop mysql, delete ibdata1, start mysql, and reload the data. (dba.stackexchange.com/questions/14259/…) – RolandoMySQLDBA Jun 16 '15 at 16:20
  • I understand, ok...I will ask my boss if he can drop the DB and create another one...thank you – Lory Lory Jun 16 '15 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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