List:General Discussion« Previous MessageNext Message »
From:Christophe Date:March 23 2014 5:49pm
Subject:Re: Database migration from default configuration to innodb_file_per_table
View as plain text  
Hi Shawn, and thanks for this concise anwser ;) .

Le 22/03/2014 05:35, shawn a écrit :
> The system is operating exactly as designed. The ibdata* file(s) contain
> more than just your data and indexes. This is the common tablespace and
> it contains all the metadata necessary to identify where *all* your
> InnoDB tables actually are (where they are in a tablespace and which
> tablespace they are in) and several other things about them. In the
> terms of the InnoDB developers, this is the "data dictionary".  This
> means that once you blow it away, MySQL has no details about any where
> any of your InnoDB tables are, exactly as the message says.
> The table names are visible in a SHOW TABLES command because that is
> essentially performing a directory listing of any .FRM files in that
> database's folder. 

It's good to know, and explains why I got this behaviour while my lab
run ...

> Without both parts (the definition in the .FRM file
> and the metadata in the common tablespace) your tables are broken. If
> you have the .frm file, you can find out which columns you have defined,
> what data types they are, if the table is partitioned or not and what
> your indexes and other constraints look like. The .frm file cannot tell
> the InnoDB engine which tablespace a table is in or what offset the root
> page of the table is within the tablespace. That information was stored
> in the ibdata file that you erased during your test run.
> The proper way to change the size of your common tablespace is
> documented here in the user manual
> Search for the section header "Decreasing the Size of the InnoDB
> Tablespace"

It's just what I wanted to avoid :( ... but thanks for the link ;).

As a Workaround, I think I will run a second MySQL instance during task,
and make a binary copy of files after making sure they are good.

Best thanks and Regards,
Database migration from default configuration to innodb_file_per_tableChristophe21 Mar
  • Re: Database migration from default configuration to innodb_file_per_tableshawn l.green22 Mar
    • Re: Database migration from default configuration to innodb_file_per_tableChristophe23 Mar
      • Re: Database migration from default configuration to innodb_file_per_tableChristophe26 Mar