0

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 ?

Ladadadada
  • 26,337
  • 7
  • 59
  • 90
B14D3
  • 5,188
  • 15
  • 64
  • 83

1 Answers1

2

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.

phemmer
  • 5,909
  • 2
  • 27
  • 36