Description:
Server version: 5.7.21-log - MySQL Wsrep Server (GPL), wsrep_25.14
table deleted on rename failed
2018-10-05T22:07:23.941017Z 2 [ERROR] InnoDB: In RENAME TABLE table `global`.`address_country` is referenced in foreign key constraints which are not compatible with the new table definition.
2018-10-05T22:07:23.951287Z 2 [ERROR] Slave SQL: Error 'Error on rename of './global/_address_country_new' to './global/address_country' (errno: 150 - Foreign key constraint is incorrectly formed)' on query. Default database: 'global'. Query: 'RENAME TABLE `global`.`_address
_country_new` TO `global`.`address_country`', Error_code: 1025
2018-10-05T22:07:23.951335Z 2 [Warning] WSREP: RBR event 1 Query apply warning: 1, 2069298981
2018-10-05T22:07:23.951668Z 2 [Warning] WSREP: Ignoring error for TO isolated action: source: 87a243ea-ba5d-11e8-bf76-5beb62fbdc38 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 10773378 trx_id: -1 seqnos (l: 550113256, g: 2069298981, s: 2069298970, d: 2069298980, ts
: 1558444088952883)
then the table was deleted from disk
this sql:
ALTER TABLE `global`.`address_country` CHANGE `iso_a2` `iso_a2` char(2) COLLATE ascii_general_ci NOT NULL COMMENT '' AFTER `iso_n3`, CHANGE `iso_a3` `iso_a3` char(3) COLLATE ascii_general_ci NOT NULL COMMENT '' AFTER `iso_a2`, CHANGE `dial_code` `dial_code` varchar(45) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `iso_a3`, CHANGE `zip_format` `zip_format` varchar(255) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `dial_code`, CHANGE `continent` `continent` char(2) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `region_type_secondary`, CHANGE `neighbours` `neighbours` varchar(64) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `continent`, CHANGE `languages` `languages` varchar(128) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `neighbours`, CHANGE `tld` `tld` char(3) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `languages`, CHANGE `currency_code` `currency_code` varchar(3) COLLATE ascii_general_ci NULL DEFAULT null COMMENT '' AFTER `tld`
this table:
CREATE TABLE `address_country` (
`id` int(11) UNSIGNED NOT NULL,
`iso_n3` int(3) UNSIGNED ZEROFILL DEFAULT NULL,
`iso_a2` varchar(2) DEFAULT NULL,
`iso_a3` varchar(3) DEFAULT NULL,
`dial_code` varchar(45) DEFAULT NULL,
`zip_format` varchar(255) DEFAULT NULL,
`name_long` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`name_un_long` varchar(255) DEFAULT NULL,
`name_un` varchar(255) DEFAULT NULL,
`default_lang_id` int(11) UNSIGNED DEFAULT NULL,
`default_currency_id` int(11) UNSIGNED DEFAULT NULL,
`states` tinyint(4) DEFAULT NULL,
`region_type_primary` varchar(128) DEFAULT NULL,
`region_type_secondary` varchar(128) DEFAULT NULL,
`continent` varchar(2) DEFAULT NULL,
`neighbours` varchar(64) DEFAULT NULL,
`languages` varchar(128) DEFAULT NULL,
`tld` varchar(3) DEFAULT NULL,
`currency_code` varchar(3) DEFAULT NULL,
`dummy` tinyint(4) DEFAULT
there are many references to id column from other tables
when I try to recreate the table:
ERROR 1215 (HY000): Cannot add foreign key constrain
How to repeat:
not sure why this happened
how do I fix this?
Suggested fix:
restore original table when query fails, don't delete id
say which key failed for ERROR 1215
