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:
None 
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:05] Kedar Vaijanapurkar
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;
[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.