Getting weird warnings on mysql upgrade checker 8.4 "references a non unique key"

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi,
Getting a weird warning when running the mysql upgrade checker to 8.4
I’m getting several errors on foreign keys referencing a primary key on a single table.

alfresco_up.fk_alf_store_root - invalid foreign key defined as
‘alf_store(root_node_id)’ references a non unique key at table ‘alf_node’.
alfresco_up.fk_alf_sub_node - invalid foreign key defined as
‘alf_subscriptions(node_id)’ references a non unique key at table ‘alf_node’.
alfresco_up.fk_alf_sub_user - invalid foreign key defined as
‘alf_subscriptions(user_node_id)’ references a non unique key at table
‘alf_node’.
alfresco_up.fk_alf_usaged_n - invalid foreign key defined as
‘alf_usage_delta(node_id)’ references a non unique key at table ‘alf_node’.

Solutions:

  • Convert non unique key to unique key if values do not have any duplicates.
    In case of foreign keys involving partial columns of key, create composite
    unique key containing all the referencing columns if values do not have any
    duplicates.

  • Remove foreign keys referring to non unique key/partial columns of key.

  • In case of multi level references which involves more than two tables
    change foreign key reference.

Column being referenced is a primary key and therefore should be unique by default.
I also checked if there were actual duplicates somehow on the column being referenced but they are all unique.
Column name is “id”
table create statement is as follows:
| alf_node | CREATE TABLE alf_node (
id bigint NOT NULL AUTO_INCREMENT,
version bigint NOT NULL,
store_id bigint NOT NULL,
uuid varchar(36) NOT NULL,
transaction_id bigint NOT NULL,
type_qname_id bigint NOT NULL,
locale_id bigint NOT NULL,
acl_id bigint DEFAULT NULL,
audit_creator varchar(255) DEFAULT NULL,
audit_created varchar(30) DEFAULT NULL,
audit_modifier varchar(255) DEFAULT NULL,
audit_modified varchar(30) DEFAULT NULL,
audit_accessed varchar(30) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY store_id (store_id,uuid),
KEY idx_alf_node_txn_type (transaction_id,type_qname_id),
KEY fk_alf_node_acl (acl_id),
KEY fk_alf_node_store (store_id),
KEY idx_alf_node_tqn (type_qname_id,store_id,id),
KEY fk_alf_node_loc (locale_id),
KEY idx_alf_node_mdq (store_id,type_qname_id),
KEY idx_alf_node_cor (audit_creator,store_id,type_qname_id),
KEY idx_alf_node_crd (audit_created,store_id,type_qname_id),
KEY idx_alf_node_mor (audit_modifier,store_id,type_qname_id),
KEY idx_alf_node_mod (audit_modified,store_id,type_qname_id),
CONSTRAINT fk_alf_node_acl FOREIGN KEY (acl_id) REFERENCES alf_access_control_list (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_loc FOREIGN KEY (locale_id) REFERENCES alf_locale (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_store FOREIGN KEY (store_id) REFERENCES alf_store (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_tqn FOREIGN KEY (type_qname_id) REFERENCES alf_qname (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT fk_alf_node_txn FOREIGN KEY (transaction_id) REFERENCES alf_transaction (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=215405 DEFAULT CHARSET=utf8 |

2

Hi,
The warnings you getting because of the introduction of restrict_fk_on_non_standard_key in 8.4. This variable by default is enabled so it disallows the FK creation which reference only part of a composite key or non-unique.
Have you checked the relational tables?

3

Hi @ipcmlr,
There’s lots of bad practices in your schema design. For starters, using BIGINT everywhere wastes 4 bytes per column, per record if you are storing numbers less than 4B. Integers in MySQL always use the full storage. Second, instead of uuid as char, store uuid as binary(16) and save 1/2 the space. Use the native UUID_TO_BIN function. I also see lots of repeated columns in your indexes. Investigate if you are indeed using all of those indexes. Remember that WHERE clause makes no difference to the index order. Ex: WHERE a = 4 AND b = 2 is the same thing as WHERE b = 2 AND a = 4. The index (a, b) will be used for both queries.