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