外部キーから参照されているテーブルに対して pt-online-schema-change を使う

% rake db:migrate DRY_RUN=1
bundle exec ridgepole -c database.yml --table-options 'ENGINE=InnoDB DEFAULT CHARSET=utf8' --apply --dry-run
Apply `Schemafile` (dry-run)
add_column("products", "deleted_at", :datetime, {:after=>"ended_at"})

# ALTER TABLE `products` ADD `deleted_at` datetime AFTER `ended_at`

% pt-online-schema-change --dry-run --print \
  --alter 'ADD `deleted_at` datetime AFTER `ended_at`' \
  --alter-foreign-keys-method "rebuild_constraints" \
  D=railsdm2018,t=products,h=localhost,u=root
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Child tables:
  `railsdm2018`.`orders` (approx. 10000 rows)
Will use the rebuild_constraints method to update foreign keys.
Starting a dry run.  `railsdm2018`.`products` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
CREATE TABLE `railsdm2018`.`_products_new` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shop_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `price` int(10) unsigned NOT NULL,
  `started_at` datetime NOT NULL,
  `ended_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_shop_id_ended_at` (`shop_id`,`ended_at`),
  KEY `ix_ended_at` (`ended_at`),
    CONSTRAINT `_fk_products_shops` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8
Created new table railsdm2018._products_new OK.
Altering new table...
ALTER TABLE `railsdm2018`.`_products_new` ADD `deleted_at` datetime AFTER `ended_at`
Altered `railsdm2018`.`_products_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
INSERT LOW_PRIORITY IGNORE INTO `railsdm2018`.`_products_new` (`id`, `shop_id`, `name`, `price`, `started_at`, `ended_at`) SELECT `id`, `shop_id`, `name`, `price`, `started_at`, `ended_at` FROM `railsdm2018`.`products` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 21221 copy nibble*/
SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `railsdm2018`.`products` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/
Not swapping tables because this is a dry run.
Not rebuilding foreign key constraints because this is a dry run.
ALTER TABLE `railsdm2018`.`orders` DROP FOREIGN KEY `fk_orders_products`, ADD CONSTRAINT `_fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `railsdm2018`.`products` (`id`)
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_del`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_upd`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_ins`
2019-05-13T07:27:57 Dropping new table...
DROP TABLE IF EXISTS `railsdm2018`.`_products_new`;
2019-05-13T07:27:57 Dropped new table OK.
Dry run complete.  `railsdm2018`.`products` was not altered.

% pt-online-schema-change --execute --print \
  --alter 'ADD `deleted_at` datetime AFTER `ended_at`' \
  --alter-foreign-keys-method "rebuild_constraints" \
  --plugin /path/to/pt-online-schema-change-fast-rebuild-constraints.pl \
  D=railsdm2018,t=products,h=localhost,u=root
-- snip --
2019-05-13T07:26:17 Copied rows OK.
2019-05-13T07:26:17 Analyzing new table...
2019-05-13T07:26:17 Swapping tables...
RENAME TABLE `railsdm2018`.`products` TO `railsdm2018`.`_products_old`, `railsdm2018`.`_products_new` TO `railsdm2018`.`products`
2019-05-13T07:26:17 Swapped original and new tables OK.
Disable foreign key checks
2019-05-13T07:26:17 Rebuilding foreign key constraints...
ALTER TABLE `railsdm2018`.`orders` DROP FOREIGN KEY `fk_orders_products`, ADD CONSTRAINT `_fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `railsdm2018`.`products` (`id`)
2019-05-13T07:26:17 Rebuilt foreign key constraints OK.
Enable foreign key checks
2019-05-13T07:26:17 Dropping old table...
DROP TABLE IF EXISTS `railsdm2018`.`_products_old`
2019-05-13T07:26:17 Dropped old table `railsdm2018`.`_products_old` OK.
2019-05-13T07:26:17 Dropping triggers...
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_del`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_upd`
DROP TRIGGER IF EXISTS `railsdm2018`.`pt_osc_railsdm2018_products_ins`
2019-05-13T07:26:17 Dropped triggers OK.
Successfully altered `railsdm2018`.`products`.

mysql> INSERT INTO `orders` (`user_id`, `product_id`, `count`, `created_at`) VALUES (1, 10000, 1, NOW());
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`railsdm2018`.`orders`, CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `orders` (`user_id`, `product_id`, `count`, `created_at`) VALUES (1, 10000, 1, NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM `orders` ORDER BY `id` DESC LIMIT 1;
+-------+---------+------------+-------+---------------------+
| id    | user_id | product_id | count | created_at          |
+-------+---------+------------+-------+---------------------+
| 10002 |       1 |      10000 |     1 | 2019-05-13 06:42:52 |
+-------+---------+------------+-------+---------------------+
1 row in set (0.01 sec)

mysql> UPDATE `orders` SET `product_id` = 10000, `count` = 2 WHERE `id` = 10002;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE `orders` SET `product_id` = 10001 WHERE `id` = 10002;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`railsdm2018`.`orders`, CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

mysql> show create table orders \G
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_orders_on_user_id` (`user_id`),
  KEY `index_orders_on_product_id` (`product_id`),
  CONSTRAINT `fk_orders_products` FOREIGN KEY (`product_id`) REFERENCES `_products_old` (`id`),
  CONSTRAINT `fk_orders_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)