Hello Christophe,
On 3/21/2014 4:47 PM, Christophe wrote:
Hi list,
I'd like your advice, (one more time ;) ) about this case :
The context is :
A huge database using InnoDB engine from filling about several years
(without possible shrinking, as I've seen, except dropping all databases
to recreate them ... ) the ibdata file , which is taking over 9GiB on
filesystem.
We have to separate data from databases in two cases , whilst it is
running , after setting innodb_file_per_table in MySQL configuration ,
and restarting service.
Creating First database, containing at oldest 6 months of data.
Second database, considered "archive" containing data older than 6 month.
Not such a problem to separate actual data : using several mysqldump
with --where switch, which handles the case.
After this, Shell scripts using "INSERT INTO archive SELECT * FROM
realtime WHERE ..." seem to be reliable to do this.
*But*, in this one timed scheduled task in data migration (Previewed and
accepted by customer, by night / not tonight ... :) ), and *I'd like to
remove the ibdata1 file* , as it takes huge disk space.
Migration task also includes converting old tables (previously in
InnoDB), to alter them into .... InnoDB, to recreate the InnoDB file
using innodb_file_per_table parameter.
Problem :
While testing this in lab, I came to fact that removing ibdata1 file,
cancels MySQL to get reference to any table in databases .
"use database" works ... but DESCRIBE table goes to : "table
database.table does not exist".
Is there anyway to handle this case ?
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. 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
http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html
Search for the section header "Decreasing the Size of the InnoDB Tablespace"
Best regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
