I have MySQL database with tables on InnoDB engine. But server is configured to use only one file ibdata0 without parameter innodb_file_per_table. I want to change this without making dump and restore it on reconfigured server. So my plan was to add parameter innodb_file_per_table in my.cnf restart server and force with ALTER TABLE to rebuild all InnoDB tables.

Doing ALTER TABLE tablename ENGINE = InnoDB should create .idb file for every .frm file in my /val/lib/mysql/mydatabase directory and it did (and I thought move data from ibdata file). But after I shut down the server and removed the old ibdata file I see all tables (SHOW TABLES) but if I make any SELECT in them I get this error:

ERROR 1146 (42S02): Table 'mytable' doesn't exist

So I suppose that some of the data remained in the old ibdata file.

Have I omitted something? Is there a way to do what I want to do ?


No, you are going to have to do a dump and restore. Even after doing the alter table engine trick, some data will remain in the ibdata1 file.

  • Dump all innodb tables to external files
  • Drop all innodb tables
  • Shut down mysql
  • Remove ibdata1 (and any logfiles if they exist)
  • Start mysql
  • Import sql dump files

I have run into this issue myself, and dumping all the tables and then re-importing them without shutting down mysql doesnt work. All innodb tables must be gone before you can remove the ibdata1 file.

  • You are right... it works only that way. – B14D3 Mar 22 '12 at 7:38

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.