This post continues Getting rid of huge ibdata file, no dump required, part I, where I describe way of converting your single-tablespace InnoDB database into a file-per-table one, without the pain of exporting and importing everything at once.
In previous part we put aside the issue of foreign keys. We address this issue now.
What if my InnoDB tables have foreign keys?
MyISAM does not support them, so you can’t just ALTER an InnoDB table to MyISAM and back into InnoDB, and expect everything to work.
Alas, this calls for additional steps (i.e. additional ALTER commands). However, these still fall well under the concept of “do it one table at a time, then take time to recover your breath and replication lag”.
Save , drop and restore your Foreign Keys setup
You can use common_schema‘s sql_foreign_keys to get the full listing and create definition of your foreign keys. For example, assume we use the sakila database:
SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila' INTO OUTFILE '/somewhere/safe/create_foreign_keys.sql'
(replace TABLE_SCHEMA=’sakila’ with whatever you want).
A sample output would be something like this (note: no semicolon on end of line):
ALTER TABLE `sakila`.`address` ADD CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `sakila`.`city` (`city_id`) ON DELETE RESTRICT ON UPDATE CASCADE ALTER TABLE `sakila`.`city` ADD CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `sakila`.`country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_address` FOREIGN KEY (`address_id`) REFERENCES `sakila`.`address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE ALTER TABLE `sakila`.`customer` ADD CONSTRAINT `fk_customer_store` FOREIGN KEY (`store_id`) REFERENCES `sakila`.`store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE ALTER TABLE `sakila`.`film` ADD CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `sakila`.`language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE ...
Once the above is in a safe place, you will want to DROP all of your foreign keys. Again, using common_schema:
SELECT drop_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila'; +-----------------------------------------------------------------------------------+ | drop_statement | +-----------------------------------------------------------------------------------+ | ALTER TABLE `sakila`.`address` DROP FOREIGN KEY `fk_address_city` | | ALTER TABLE `sakila`.`city` DROP FOREIGN KEY `fk_city_country` | | ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_address` | | ALTER TABLE `sakila`.`customer` DROP FOREIGN KEY `fk_customer_store` | | ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language` | | ALTER TABLE `sakila`.`film` DROP FOREIGN KEY `fk_film_language_original` | | ... | +-----------------------------------------------------------------------------------+
You don’t want to issue all these at once: do them one at a time, and wait for your slave to catch up.
Once this is done, you can move on to the steps described in Part I of this post: converting tables to MyISAM, shutting down, removing InnoDB files, then converting back to InnoDB.
And then, taking breath again, you must re-import the foreign keys. Use the ADD CONSTRAINT commands you have saved earlier on. Again, one at a time, wait for slave to catch up.
To reiterate, for each table you would take the following steps:
- Make sure the FK definition is safely stored somewhere
- STOP SLAVE
- Drop all table’s foreign keys: ALTER TABLE … DROP FOREIGN KEY …, DROP FOREIGN KEY …
- START SLAVE
- Wait for slave to catch up
- STOP SLAVE
- ALTER TABLE … ENGINE=MyISAM (*)
- START SLAVE
- Wait for slave to catch up
(*) Altering to MyISAM drops FK constraints, so the above could actually be done in one step. I’m cautious and illustrate in two.
Once all tables are altered, and InnoDB tablespace is removed, restoration is as follows: for each table,
- STOP SLAVE
- ALTER TABLE … ENGINE=InnoDB [create options]
- START SLAVE
- Wait for slave to catch up
- STOP SLAVE
- ALTER TABLE … ADD CONSTRAINT …, ADD CONSTRAINT …(+)
- START SLAVE
- Wait for slave to catch up
(+) Alas, you can’t convert to InnoDB and add constraints at the same time…
This is not entirely safe
A MyISAM slave to an InnoDB master with foreign keys is a tricky business. It really depends on the type of foreign keys you have and the use you make of them. See Impact of foreign keys absence on replicating slaves.
s/Altering to MySQL drops FK constraints/Altering to MyISAM drops FK constraints/ ?
@Daniel,
Thanks — fixed
BAD idea….if you have constraints like delete cascades and you let the slave catch up your system is going to be out of sync. This is a REALLY BAD non-real world solution that should NEVER be used.
@joop,
Thank you. I urge you to read fully, and follow link in last paragraph.
Think it’s an interesting approach that you’ve taken to resolve the ibdata file size, but I still the risks and overheads (investigation to see what might break) to achieve it are too high in a real-world environment. More so if the data is that of a financial nature. The safest is to do dump loads, or we can all just keep begging the MySQL developers to make a vacuum function to do background thread cleanup of ibdata files. This size issue of ibdata and idb files and the locking when doing optimizing is honestly one of the biggest flaws in MySQL, if they can fix this issue then MysQL could and would be so much more powerful than it is right now.
Keep up the blogging, some interesting reading here.
Joop