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.
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 ?