MySQL Bugs: #118023: get_foreign_key_references_check wrongly identifies unique key on the referenced columns

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:	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

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;

Correcting category

Hello Kedar,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

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;