| Bug #82961 | Information_schema Foreign key meta data differs in 8.0.0 | ||
|---|---|---|---|
| Submitted: | 12 Sep 2016 21:15 | Modified: | 10 Nov 2016 18:14 |
| Reporter: | Morgan Tocker | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Data Dictionary | Severity: | S3 (Non-critical) |
| Version: | 8.0.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[12 Sep 2016 21:15]
Morgan Tocker
[12 Sep 2016 23:09]
MySQL Verification Team
Thank you for the bug report.
C:\dbs>c:\dbs\8.0r\bin\mysql -uroot -p --port=3590 --prompt="mysql 8.0 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.0-dmr 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 8.0 > create database yahonda;
Query OK, 1 row affected (0.05 sec)
mysql 8.0 > use yahonda;
Database changed
mysql 8.0 >
mysql 8.0 > DROP TABLE IF EXISTS `testing_parents`;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql 8.0 > DROP TABLE IF EXISTS `testings`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 8.0 >
mysql 8.0 > CREATE TABLE `testing_parents` (`id` int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.34 sec)
mysql 8.0 >
mysql 8.0 > CREATE TABLE `testings` (`id` int AUTO_INCREMENT PRIMARY KEY, `col_1` int, `col_2` int, CONSTRAINT `fk_ra
_28528b19fe`
-> FOREIGN KEY (`col_1`)
-> REFERENCES `testing_parents` (`id`),
-> CONSTRAINT `fk_rails_ad9b332772`
-> FOREIGN KEY (`col_2`)
-> REFERENCES `testing_parents` (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.31 sec)
mysql 8.0 >
mysql 8.0 > SELECT fk.referenced_table_name AS 'to_table',
-> fk.referenced_column_name AS 'primary_key',
-> fk.column_name AS 'column',
-> fk.constraint_name AS 'name',
-> rc.update_rule AS 'on_update',
-> rc.delete_rule AS 'on_delete'
-> FROM information_schema.key_column_usage fk
-> JOIN information_schema.referential_constraints rc
-> USING (constraint_schema, constraint_name)
-> WHERE fk.referenced_column_name IS NOT NULL
-> AND fk.table_schema = 'yahonda'
-> AND fk.table_name = 'testings';
+-----------------+-------------+--------+---------------------+-----------+-----------+
| to_table | primary_key | column | name | on_update | on_delete |
+-----------------+-------------+--------+---------------------+-----------+-----------+
| testing_parents | id | col_1 | fk_rails_28528b19fe | RESTRICT | RESTRICT |
+-----------------+-------------+--------+---------------------+-----------+-----------+
1 row in set (0.65 sec)
mysql 8.0 > exit
Bye
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.16 Source distribution PULL: 2016-SEP-07
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 5.7 > create database yahonda;
Query OK, 1 row affected (0.00 sec)
mysql 5.7 > use yahonda;
Database changed
mysql 5.7 >
mysql 5.7 > DROP TABLE IF EXISTS `testing_parents`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.7 > DROP TABLE IF EXISTS `testings`;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.7 >
mysql 5.7 > CREATE TABLE `testing_parents` (`id` int AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
Query OK, 0 rows affected (0.28 sec)
mysql 5.7 >
mysql 5.7 > CREATE TABLE `testings` (`id` int AUTO_INCREMENT PRIMARY KEY, `col_1` int, `col_2` int, CONSTRAINT `fk_ra
_28528b19fe`
-> FOREIGN KEY (`col_1`)
-> REFERENCES `testing_parents` (`id`),
-> CONSTRAINT `fk_rails_ad9b332772`
-> FOREIGN KEY (`col_2`)
-> REFERENCES `testing_parents` (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.45 sec)
mysql 5.7 >
mysql 5.7 > SELECT fk.referenced_table_name AS 'to_table',
-> fk.referenced_column_name AS 'primary_key',
-> fk.column_name AS 'column',
-> fk.constraint_name AS 'name',
-> rc.update_rule AS 'on_update',
-> rc.delete_rule AS 'on_delete'
-> FROM information_schema.key_column_usage fk
-> JOIN information_schema.referential_constraints rc
-> USING (constraint_schema, constraint_name)
-> WHERE fk.referenced_column_name IS NOT NULL
-> AND fk.table_schema = 'yahonda'
-> AND fk.table_name = 'testings';
+-----------------+-------------+--------+---------------------+-----------+-----------+
| to_table | primary_key | column | name | on_update | on_delete |
+-----------------+-------------+--------+---------------------+-----------+-----------+
| testing_parents | id | col_1 | fk_rails_28528b19fe | RESTRICT | RESTRICT |
| testing_parents | id | col_2 | fk_rails_ad9b332772 | RESTRICT | RESTRICT |
+-----------------+-------------+--------+---------------------+-----------+-----------+
2 rows in set (2.67 sec)
mysql 5.7 >
[13 Sep 2016 12:11]
Yasuo Honda
Looks like information_schema.key_column_usage only shows one foreign key.
* 8.0.0-dmr
mysql> select * from information_schema.key_column_usage where table_schema = 'yahonda' and constraint_name != 'PRIMARY'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: yahonda
CONSTRAINT_NAME: fk_rails_28528b19fe
TABLE_CATALOG: def
TABLE_SCHEMA: yahonda
TABLE_NAME: testings
COLUMN_NAME: col_1
ORDINAL_POSITION: 0
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: yahonda
REFERENCED_TABLE_NAME: testing_parents
REFERENCED_COLUMN_NAME: id
1 row in set (0.01 sec)
* 5.7.15
mysql> select * from information_schema.key_column_usage where table_schema = 'yahonda' and constraint_name != 'PRIMARY'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: yahonda
CONSTRAINT_NAME: fk_rails_28528b19fe
TABLE_CATALOG: def
TABLE_SCHEMA: yahonda
TABLE_NAME: testings
COLUMN_NAME: col_1
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: yahonda
REFERENCED_TABLE_NAME: testing_parents
REFERENCED_COLUMN_NAME: id
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: yahonda
CONSTRAINT_NAME: fk_rails_ad9b332772
TABLE_CATALOG: def
TABLE_SCHEMA: yahonda
TABLE_NAME: testings
COLUMN_NAME: col_2
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: yahonda
REFERENCED_TABLE_NAME: testing_parents
REFERENCED_COLUMN_NAME: id
2 rows in set (0.00 sec)
mysql>
[10 Nov 2016 18:14]
Daniel Price
Posted by developer: Fixed as of the upcoming 8.0.0 release, and here's the changelog entry: In some cases, INFORMATION_SCHEMA.KEY_COLUMN_USAGE did not report data for all foreign key constraints. Thank you for the bug report.
[11 Nov 2016 12:37]
MySQL Verification Team
Bug #83681 marked as duplicate of this one
