2

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

Alaa Alomari
  • 638
  • 5
  • 18
  • 37

2 Answers2

0

Stop mysql, delete innodb log files , start mysql

johnshen64
  • 5,747
  • 23
  • 17
  • 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 02 '11 at 13:11
0

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.

Kedare
  • 1,766
  • 4
  • 20
  • 36