Bug #118023 get_foreign_key_references_check wrongly identifies unique key on the referenced columns
| Submitted: | 21 Apr 2025 8:05 | Modified: | 18 Sep 9:52 |
|---|---|---|---|
| Reporter: | Kedar Vaijanapurkar | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | Shell Upgrade Checker | Severity: | S3 (Non-critical) |
| Version: | 8.4.4, 8.4.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | check-for-server-upgrade, mysql 8.4, mysql-shell | ||
[21 Apr 2025 8:06]
Kedar Vaijanapurkar
Correcting category
[22 Apr 2025 9:32]
MySQL Verification Team
Hello Kedar, Thank you for the report and feedback. Verified as described. regards, Umesh
[9 Jul 2025 19:11]
Duke Lee
Hi, I'd like to note that the corrected query still produces inaccurate results because the JOINs only account for intra-database FK references; it does not produce results for inter/cross-database FK references.
The query below should be a bit more accurate:
SELECT
fk.constraint_schema,
fk.constraint_name,
'',
fk.parent_fk_definition AS fk_definition,
fk.REFERENCED_TABLE_NAME AS target_table,
'##fkToNonUniqueKey'
FROM (
SELECT
rc.constraint_schema,
rc.constraint_name,
CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name ORDER BY kc.ORDINAL_POSITION), ')') AS parent_fk_definition,
CONCAT(kc.REFERENCED_TABLE_SCHEMA, '.', kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME ORDER BY kc.POSITION_IN_UNIQUE_CONSTRAINT), ')') AS target_fk_definition,
rc.REFERENCED_TABLE_NAME
FROM
information_schema.REFERENTIAL_CONSTRAINTS rc
JOIN
information_schema.KEY_COLUMN_USAGE kc
ON
rc.constraint_schema = kc.constraint_schema AND
rc.constraint_name = kc.constraint_name AND
rc.UNIQUE_CONSTRAINT_SCHEMA = kc.REFERENCED_TABLE_SCHEMA AND -- updated
rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND
kc.REFERENCED_TABLE_NAME IS NOT NULL AND
kc.REFERENCED_COLUMN_NAME IS NOT NULL
GROUP BY
rc.constraint_schema,
rc.table_name,
rc.constraint_name,
rc.UNIQUE_CONSTRAINT_SCHEMA, -- added
rc.REFERENCED_TABLE_NAME
) fk
LEFT JOIN (
SELECT
CONCAT(table_schema, '.', table_name, '(', GROUP_CONCAT(column_name ORDER BY seq_in_index), ')') AS fk_definition
FROM
INFORMATION_SCHEMA.STATISTICS
WHERE
sub_part IS NULL AND
non_unique = 0 -- Only consider unique indexes
GROUP BY
table_schema, table_name, index_name
) unique_idx ON fk.target_fk_definition = unique_idx.fk_definition
WHERE
unique_idx.fk_definition IS NULL;
[25 Feb 18:36]
Kedar Vaijanapurkar
There's correction in initial table definition as "UNIQUE KEY" definition was missed... but description covers it and bug's verified. Adding here for clarity.
DROP TABLE IF EXISTS clients;
CREATE TABLE clients (
id INT AUTO_INCREMENT PRIMARY KEY,
client_id INT NOT NULL,
name VARCHAR(100),
UNIQUE KEY (client_id),
INDEX idx_client_id (client_id)
);
[18 Sep 9:52]
Pawel Andruszkiewicz
Posted by developer: The 8.4 version is no longer supported, and 26.7 does not have this behaviour, it was fixed by a fix for BUG#38194922 (9.5.0)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: If the table has duplicate key (both unique and btree) indexes on the column used for foreign key reference. [root@centos_1 ~]# mysqlsh -- util check-for-server-upgrade root@localhost 4) Checks for foreign keys not referencing a full unique index (foreignKeyReferences) Foreign keys to partial indexes may be forbidden as of 8.4.0, this check identifies such cases to warn the user. test.orders_ibfk_1 - invalid foreign key defined as 'orders(client_id)' references a non unique key at table 'clients'. How to repeat: Create the tables DROP TABLE IF EXISTS clients; CREATE TABLE clients ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, name VARCHAR(100), INDEX idx_client_id (client_id) ); DROP TABLE IF EXISTS orders; CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT, order_details VARCHAR(255), FOREIGN KEY (client_id) REFERENCES clients(client_id) ); DROP TABLE IF EXISTS orders_2; DROP TABLE IF EXISTS clients_2; CREATE TABLE clients_2 ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, name VARCHAR(100), INDEX idx_client_id (client_id) ); CREATE TABLE orders_2 ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT, order_details VARCHAR(255), FOREIGN KEY (client_id) REFERENCES clients_2(client_id) ); Run mysql shell: mysqlsh -- util check-for-server-upgrade root@localhost Observe the output includes both the tables. This is because of the query: https://github.com/mysql/mysql-shell/blob/8.4.4/modules/util/upgrade_checker/upgrade_check... This is wrong because client_id does have a UNIQUE KEY yet it is reported. It is not a critical issue per se but an improvement is possible for sure. Suggested fix: Use this query instead: SELECT fk.constraint_schema, fk.constraint_name, '', fk.parent_fk_definition AS fk_definition, fk.REFERENCED_TABLE_NAME AS target_table, '##fkToNonUniqueKey' FROM ( SELECT rc.constraint_schema, rc.constraint_name, CONCAT(rc.table_name, '(', GROUP_CONCAT(kc.column_name ORDER BY kc.ORDINAL_POSITION), ')') AS parent_fk_definition, CONCAT(kc.REFERENCED_TABLE_SCHEMA, '.', kc.REFERENCED_TABLE_NAME, '(', GROUP_CONCAT(kc.REFERENCED_COLUMN_NAME ORDER BY kc.POSITION_IN_UNIQUE_CONSTRAINT), ')') AS target_fk_definition, rc.REFERENCED_TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS rc JOIN information_schema.KEY_COLUMN_USAGE kc ON rc.constraint_schema = kc.constraint_schema AND rc.constraint_name = kc.constraint_name AND rc.constraint_schema = kc.REFERENCED_TABLE_SCHEMA AND rc.REFERENCED_TABLE_NAME = kc.REFERENCED_TABLE_NAME AND kc.REFERENCED_TABLE_NAME IS NOT NULL AND kc.REFERENCED_COLUMN_NAME IS NOT NULL GROUP BY rc.constraint_schema, rc.constraint_name, rc.table_name, rc.REFERENCED_TABLE_NAME ) fk LEFT JOIN ( SELECT CONCAT(table_schema, '.', table_name, '(', GROUP_CONCAT(column_name ORDER BY seq_in_index), ')') AS fk_definition FROM INFORMATION_SCHEMA.STATISTICS WHERE sub_part IS NULL AND non_unique = 0 GROUP BY table_schema, table_name, index_name ) unique_idx ON fk.target_fk_definition = unique_idx.fk_definition WHERE unique_idx.fk_definition IS NULL;