Bug #118023 | get_foreign_key_references_check wrongly identifies unique key on the referenced columns | ||
---|---|---|---|
Submitted: | 21 Apr 8:05 | Modified: | 22 Apr 9:32 |
Reporter: | Kedar Vaijanapurkar | Email Updates: | |
Status: | Verified | 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 8:05]
Kedar Vaijanapurkar
[21 Apr 8:06]
Kedar Vaijanapurkar
Correcting category
[22 Apr 9:32]
MySQL Verification Team
Hello Kedar, Thank you for the report and feedback. Verified as described. regards, Umesh
[9 Jul 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;