Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
serverfault.com/questions/230551/… –  quanta Oct 2 '11 at 11:44

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer

Stop mysql, delete innodb log files , start mysql

share|improve this answer
    
i did this already, and mentioned that in my question, in this case i got ERROR 1146 (42S02): Table 'DBNAME.TableName' doesn't exist –  Alaa Alomari Oct 2 '11 at 13:11

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.