Bug #92697 table deleted on rename
Submitted: 6 Oct 2018 9:37 Modified: 6 Oct 2018 18:11
Reporter: Pavel Dvořák Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.21 OS:Debian (9)
Assigned to: CPU Architecture:x86 (x64)
Tags: innodb, rename

[6 Oct 2018 9:37] Pavel Dvořák
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
[6 Oct 2018 9:44] Pavel Dvořák
old table structure was essentially the same but without unsigned
[6 Oct 2018 18:11] Miguel Solorzano
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.