I was having innodb with one ibdata file. i have changed it in my.cnf to have ibd file for every table (innodb_file_per_table). then i run the following query on all of my innodb tables to have its own ibd file

alter table tablename engine=innodb;

now after converting all of my tables, ibdata still having the same size, so i have deleted it, and restarted mysql. mysql has created it again with 10MB size (as defined in my.cnf) but the problem now is that i can see all of my tables normally when show tables; but whenever i want to desc tablename or select * from tablename i am getting this error message

mysql> desc staff;
ERROR 1286 (42000): Unknown table engine 'InnoDB'

and in show engines i can't see innodb in the list!!
also i tried to delete the ib_logfile0 and ib_logfile1, i got another message

ERROR 1146 (42S02): Table 'DBNAME.TableName' doesn't exist

I know that i was supposed to mysqldump and restore, but this is what i have done :( , anybody has an idea of how to delete the ibdata1 file and keep innodb engine enabled? Thanks


2 Answers 2


Even with innodb_file_per_table setting, the ibdata* still stores critical (meta)data about your tables, you should not have destroyed it. I'm affraid your data are lost, except if there is a way to extract them from the idb files and restore them after the ibdata* file has been rebuilt.

All the metadata still resides in ibdata1 and there is absolutely no way around that. Redo logs and MVCC data also still live with ibdata1.


Stop mysql, delete innodb log files , start mysql

  • i did this already, and mentioned that in my question, in this case i got ERROR 1146 (42S02): Table 'DBNAME.TableName' doesn't exist Oct 2, 2011 at 13:11

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.