Description:
While I was testing Percona online-schema-change, I found an error with FK constraints.
On a high level, what the tool does is this:
1. Create a tmp table
2. Copy all rows from the original one to the new tmp table
3. Disable FK checks
4. Drop old table
5. Rename tmp to the original name
6. Re-enable FKs.
After that, FKs constraints are still being considered by MySQL 5.7, but in MySQL 8, FKs constraints are ignored after the rename, even when they still exist in INFORMATION_SCHEMA.INNODB_FOREIGN and INFORMATION_SCHEMA.INNODB_FOREIGN_COLS.
The last DELETE fails in MySQL 5.7 due to the FK constraints, but in MySQL 8.0.4-rc, the row with country_id = 1 is being deleted since FK constraints are being ignored.
How to repeat:
CREATE DATABASE pt_osc;
-- Query OK, 1 row affected (0.01 sec)
USE pt_osc;
-- Database changed
SET foreign_key_checks=0;
-- Query OK, 0 rows affected (0.01 sec)
CREATE TABLE `country` (
`country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`country` varchar(50) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB;
-- Query OK, 0 rows affected (0.01 sec)
CREATE TABLE `city` (
`city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(50) NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`),
KEY `idx_fk_country_id` (`country_id`),
CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB;
-- Query OK, 0 rows affected (0.01 sec)
CREATE TABLE `address` (
`address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`address` varchar(50) NOT NULL,
`city_id` smallint(5) unsigned NOT NULL,
`postal_code` varchar(10) DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`address_id`),
KEY `idx_fk_city_id` (`city_id`),
CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE
) ENGINE=InnoDB;
-- Query OK, 0 rows affected (0.02 sec)
INSERT INTO pt_osc.country (`country_id`, `country`) VALUES
(1, 'Canada'),
(2, 'USA'),
(3, 'Mexico'),
(4, 'France'),
(5, 'Spain');
-- Query OK, 5 rows affected (0.00 sec)
-- Records: 5 Duplicates: 0 Warnings: 0
INSERT INTO pt_osc.city (`city_id`, `city`, `country_id`) VALUES
(1, 'Montral', 1),
(2, 'New York', 2),
(3, 'Durango', 3),
(4, 'Paris', 4),
(5, 'Madrid', 5);
-- Query OK, 5 rows affected (0.00 sec)
-- Records: 5 Duplicates: 0 Warnings: 0
INSERT INTO pt_osc.address (`address_id`, `address`, `city_id`, `postal_code`) VALUES
(1, 'addy 1', 1, '10000'),
(2, 'addy 2', 2, '20000'),
(3, 'addy 3', 3, '30000'),
(4, 'addy 4', 4, '40000'),
(5, 'addy 5', 5, '50000');
-- Query OK, 5 rows affected (0.01 sec)
-- Records: 5 Duplicates: 0 Warnings: 0
SET foreign_key_checks=1;
-- Query OK, 0 rows affected (0.00 sec)
DELETE FROM country WHERE country_id =1;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`pt_osc`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
-- +------------------------+----------------+----------------+--------+------+
-- | ID | FOR_NAME | REF_NAME | N_COLS | TYPE |
-- +------------------------+----------------+----------------+--------+------+
-- | test/fk_city_country | test/city | test/country | 1 | 0 |
-- | pt_osc/fk_city_country | pt_osc/city | pt_osc/country | 1 | 0 |
-- | pt_osc/fk_address_city | pt_osc/address | pt_osc/city | 1 | 0 |
-- +------------------------+----------------+----------------+--------+------+
-- 3 rows in set (0.01 sec)
CREATE TABLE tmp_country LIKE country;
-- Query OK, 0 rows affected (0.01 sec)
INSERT INTO tmp_country SELECT * FROM country;
-- Query OK, 5 rows affected (0.00 sec)
-- Records: 5 Duplicates: 0 Warnings: 0
SET foreign_key_checks=0;
-- Query OK, 0 rows affected (0.00 sec)
DROP TABLE country;
-- Query OK, 0 rows affected (0.00 sec)
RENAME TABLE tmp_country TO country;
-- Query OK, 0 rows affected (0.01 sec)
SET foreign_key_checks=1;
-- Query OK, 0 rows affected (0.00 sec)
DELETE FROM country WHERE country_id =1;
-- Query OK, 1 row affected (0.00 sec)
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
-- +------------------------+----------------+----------------+--------+------+
-- | ID | FOR_NAME | REF_NAME | N_COLS | TYPE |
-- +------------------------+----------------+----------------+--------+------+
-- | test/fk_city_country | test/city | test/country | 1 | 0 |
-- | pt_osc/fk_city_country | pt_osc/city | pt_osc/country | 1 | 0 |
-- | pt_osc/fk_address_city | pt_osc/address | pt_osc/city | 1 | 0 |
-- +------------------------+----------------+----------------+--------+------+
-- 3 rows in set (0.00 sec)
SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS;
-- +------------------------+--------------+--------------+-----+
-- | ID | FOR_COL_NAME | REF_COL_NAME | POS |
-- +------------------------+--------------+--------------+-----+
-- | test/fk_city_country | country_id | country_id | 1 |
-- | pt_osc/fk_city_country | country_id | country_id | 1 |
-- | pt_osc/fk_address_city | city_id | city_id | 1 |
-- +------------------------+--------------+--------------+-----+
-- 3 rows in set (0.00 sec)
Description: While I was testing Percona online-schema-change, I found an error with FK constraints. On a high level, what the tool does is this: 1. Create a tmp table 2. Copy all rows from the original one to the new tmp table 3. Disable FK checks 4. Drop old table 5. Rename tmp to the original name 6. Re-enable FKs. After that, FKs constraints are still being considered by MySQL 5.7, but in MySQL 8, FKs constraints are ignored after the rename, even when they still exist in INFORMATION_SCHEMA.INNODB_FOREIGN and INFORMATION_SCHEMA.INNODB_FOREIGN_COLS. The last DELETE fails in MySQL 5.7 due to the FK constraints, but in MySQL 8.0.4-rc, the row with country_id = 1 is being deleted since FK constraints are being ignored. How to repeat: CREATE DATABASE pt_osc; -- Query OK, 1 row affected (0.01 sec) USE pt_osc; -- Database changed SET foreign_key_checks=0; -- Query OK, 0 rows affected (0.01 sec) CREATE TABLE `country` ( `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `country` varchar(50) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`country_id`) ) ENGINE=InnoDB; -- Query OK, 0 rows affected (0.01 sec) CREATE TABLE `city` ( `city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `city` varchar(50) NOT NULL, `country_id` smallint(5) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`city_id`), KEY `idx_fk_country_id` (`country_id`), CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE ) ENGINE=InnoDB; -- Query OK, 0 rows affected (0.01 sec) CREATE TABLE `address` ( `address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(50) NOT NULL, `city_id` smallint(5) unsigned NOT NULL, `postal_code` varchar(10) DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`address_id`), KEY `idx_fk_city_id` (`city_id`), CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE ) ENGINE=InnoDB; -- Query OK, 0 rows affected (0.02 sec) INSERT INTO pt_osc.country (`country_id`, `country`) VALUES (1, 'Canada'), (2, 'USA'), (3, 'Mexico'), (4, 'France'), (5, 'Spain'); -- Query OK, 5 rows affected (0.00 sec) -- Records: 5 Duplicates: 0 Warnings: 0 INSERT INTO pt_osc.city (`city_id`, `city`, `country_id`) VALUES (1, 'Montral', 1), (2, 'New York', 2), (3, 'Durango', 3), (4, 'Paris', 4), (5, 'Madrid', 5); -- Query OK, 5 rows affected (0.00 sec) -- Records: 5 Duplicates: 0 Warnings: 0 INSERT INTO pt_osc.address (`address_id`, `address`, `city_id`, `postal_code`) VALUES (1, 'addy 1', 1, '10000'), (2, 'addy 2', 2, '20000'), (3, 'addy 3', 3, '30000'), (4, 'addy 4', 4, '40000'), (5, 'addy 5', 5, '50000'); -- Query OK, 5 rows affected (0.01 sec) -- Records: 5 Duplicates: 0 Warnings: 0 SET foreign_key_checks=1; -- Query OK, 0 rows affected (0.00 sec) DELETE FROM country WHERE country_id =1; -- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`pt_osc`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE) SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN; -- +------------------------+----------------+----------------+--------+------+ -- | ID | FOR_NAME | REF_NAME | N_COLS | TYPE | -- +------------------------+----------------+----------------+--------+------+ -- | test/fk_city_country | test/city | test/country | 1 | 0 | -- | pt_osc/fk_city_country | pt_osc/city | pt_osc/country | 1 | 0 | -- | pt_osc/fk_address_city | pt_osc/address | pt_osc/city | 1 | 0 | -- +------------------------+----------------+----------------+--------+------+ -- 3 rows in set (0.01 sec) CREATE TABLE tmp_country LIKE country; -- Query OK, 0 rows affected (0.01 sec) INSERT INTO tmp_country SELECT * FROM country; -- Query OK, 5 rows affected (0.00 sec) -- Records: 5 Duplicates: 0 Warnings: 0 SET foreign_key_checks=0; -- Query OK, 0 rows affected (0.00 sec) DROP TABLE country; -- Query OK, 0 rows affected (0.00 sec) RENAME TABLE tmp_country TO country; -- Query OK, 0 rows affected (0.01 sec) SET foreign_key_checks=1; -- Query OK, 0 rows affected (0.00 sec) DELETE FROM country WHERE country_id =1; -- Query OK, 1 row affected (0.00 sec) SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN; -- +------------------------+----------------+----------------+--------+------+ -- | ID | FOR_NAME | REF_NAME | N_COLS | TYPE | -- +------------------------+----------------+----------------+--------+------+ -- | test/fk_city_country | test/city | test/country | 1 | 0 | -- | pt_osc/fk_city_country | pt_osc/city | pt_osc/country | 1 | 0 | -- | pt_osc/fk_address_city | pt_osc/address | pt_osc/city | 1 | 0 | -- +------------------------+----------------+----------------+--------+------+ -- 3 rows in set (0.00 sec) SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS; -- +------------------------+--------------+--------------+-----+ -- | ID | FOR_COL_NAME | REF_COL_NAME | POS | -- +------------------------+--------------+--------------+-----+ -- | test/fk_city_country | country_id | country_id | 1 | -- | pt_osc/fk_city_country | country_id | country_id | 1 | -- | pt_osc/fk_address_city | city_id | city_id | 1 | -- +------------------------+--------------+--------------+-----+ -- 3 rows in set (0.00 sec)