Bug #85787 Inconsistent INFORMATION_SCHEMA across restarts after RENAME
Submitted: 4 Apr 14:53 Modified: 4 Apr 17:39
Reporter: Luca Pino Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6 and 5.7 OS:Mac OS X
Assigned to:
Tags: foreign constraints, foreign keys, information_schema, osx, restart

[4 Apr 14:53] Luca Pino
Description:
When doing a RENAME of two tables with a foreign constraint across them to a different DB on OSX, the foreign constraint works out just fine as long as you don't restart the server. 

In particular the query:
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA <> CONSTRAINT_SCHEMA;
does not return any result before restarting the server. As soon as you restart the server the same query returns a result.

I've reproduced this on 5.6 and 5.7 on OSX. It does not seem to apply to other platforms (cannot reproduce it on MySQL over Docker)

How to repeat:
Run:
------
CREATE SCHEMA `MY_DB_1` DEFAULT CHARACTER SET utf8 ;
CREATE SCHEMA `MY_DB_2` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE `MY_DB_1`.`MY_TABLE_1` (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `MY_DB_1`.`MY_TABLE_2` (
  `foreignKeyColumn` INT NULL,
  INDEX `foreign_KEY_1_idx` (`foreignKeyColumn` ASC),
  CONSTRAINT `foreign_KEY_1`
    FOREIGN KEY (`foreignKeyColumn`)
    REFERENCES `MY_DB_1`.`MY_TABLE_1` (`id`));

RENAME TABLE `MY_DB_1`.`MY_TABLE_1` TO `MY_DB_2`.`MY_TABLE_1`;
RENAME TABLE `MY_DB_1`.`MY_TABLE_2` TO `MY_DB_2`.`MY_TABLE_2`;
------ 
Check the results of:
------
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA <> CONSTRAINT_SCHEMA;
------
Restart the server
Now check again the results of:
------
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA <> CONSTRAINT_SCHEMA;

Suggested fix:
INFORMATION_SCHEMA should return the same info before and after a restart.
[4 Apr 14:54] Luca Pino
This is the script to then go and reproduce the issue

Attachment: restart_mysql_bug.sql (application/octet-stream, text), 554 bytes.

[4 Apr 17:39] Miguel Solorzano
Thank you for the bug report.

miguel:~ miguel$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE SCHEMA `MY_DB_1` DEFAULT CHARACTER SET utf8 ;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE SCHEMA `MY_DB_2` DEFAULT CHARACTER SET utf8 ;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> CREATE TABLE `MY_DB_1`.`MY_TABLE_1` (
    ->   `id` INT NOT NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> CREATE TABLE `MY_DB_1`.`MY_TABLE_2` (
    ->   `foreignKeyColumn` INT NULL,
    ->   INDEX `foreign_KEY_1_idx` (`foreignKeyColumn` ASC),
    ->   CONSTRAINT `foreign_KEY_1`
    ->     FOREIGN KEY (`foreignKeyColumn`)
    ->     REFERENCES `MY_DB_1`.`MY_TABLE_1` (`id`));
Query OK, 0 rows affected (0.31 sec)

mysql>
mysql> RENAME TABLE `MY_DB_1`.`MY_TABLE_1` TO `MY_DB_2`.`MY_TABLE_1`;
Query OK, 0 rows affected (0.00 sec)

mysql> RENAME TABLE `MY_DB_1`.`MY_TABLE_2` TO `MY_DB_2`.`MY_TABLE_2`;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA <> CONSTRAINT_SCHEMA\G
Empty set (0.37 sec)

mysql> exit
Bye
miguel:~ miguel$ sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
miguel:~ miguel$ sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
miguel:~ miguel$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_SCHEMA <> CONSTRAINT_SCHEMA\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: MY_DB_2
              CONSTRAINT_NAME: foreign_KEY_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: MY_DB_2
                   TABLE_NAME: MY_TABLE_2
                  COLUMN_NAME: foreignKeyColumn
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: MY_DB_1
        REFERENCED_TABLE_NAME: MY_TABLE_1
       REFERENCED_COLUMN_NAME: id
1 row in set (0.15 sec)

mysql>