All tables should have Primary Keys for Group replication in SQL

[waynetheisinger]

Summary (*)

When hosting OpenMage on Digital Ocean you get sent the following email daily (which is really annoying).

Hello,

Our systems have indicated that your MySQL cluster, db-mysql-fra1-xxx, has tables without primary keys. We have identified that MySQL tables without primary keys can lead to service replication issues that jeopardize performance and availability. If primary keys are not present for database tables exceeding 5,000 rows, data-loss can occur.

Examples (*)

The following tables don't have primary keys:

api2_acl_user
api_session
catalog_category_anc_categs_index_idx
catalog_category_anc_categs_index_tmp
catalog_category_anc_products_index_idx
catalog_category_anc_products_index_tmp
catalog_category_product_index_enbl_idx
catalog_category_product_index_enbl_tmp
catalog_category_product_index_idx
catalog_category_product_index_tmp
log_url
m_db
oauth_nonce
weee_discount
widget_instance_page_layout

Proposed solution

1.) Identify unique combinations of columns and create PKs on them
2.) Where uniqueness cannot be guaranteed create an update script that adds new PK column.
3.) Test and alter all CRUD operations to be aware of the new PK especially if they are new columns rather than keys simply added to existing columns.

[waynetheisinger]

I did a naive trial run on this problem after adding the fake_pk columns and reindexing we get the following error

Insert value list does not match column list: 1136 Column count doesn't match value count at row 1, query was: INSERT INTO catalog_category_product_index (category_id, product_id, position, is_parent, store_id, visibility) SELECT catalog_category_product_index_idx.category_id, catalog_category_product_index_idx.product_id, catalog_category_product_index_idx.position, catalog_category_product_index_idx.is_parent, catalog_category_product_index_idx.store_id, catalog_category_product_index_idx.visibility, catalog_category_product_index_idx.fake_pk FROM catalog_category_product_index_idx ON DUPLICATE KEY UPDATE category_id = VALUES(category_id), product_id = VALUES(product_id), position = VALUES(position), is_parent = VALUES(is_parent), store_id = VALUES(store_id), visibility = VALUES(visibility)

I notice that catalog_category_product_index has a three column product key but the *_idx and *_tmp tables that the index are writing into ie catalog_category_product_index_idx and catalog_category_product_index_tmp have the three needed columns index set to non-unique making them unsuitable for a primary key in those temp tables, so this requires further thought.

[waynetheisinger]

Looking into this further I've found that whilst MySQL tables do not necessarily need a primary key[1], if you are using group replication it is needed [2]. So Digital Ocean are right to keep warning... :(

[1] https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-indexes-keys
[2] https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html

[joshua-bn]

this can just be changed from a unique to a PK. Check each for that. Then I'd split this up so that you can do those that have just a change from unique keys to PKs and those that need more work.

[joshua-bn]

I wonder why catalog_category_product_index_idx doesn't have a unique key. Its only index covers product_id, category_id, and store_id. I can't see that as having any additional columns or multiple values. Changing that to a unique might make it perform better.

EDIT: it's empty for me... so, I won't see any performance improvements. Not sure what it does since it is empty.

[Flyingmana]

Sounds like a great addition, when doing this changes, please take the Branch 20.0 as target

[waynetheisinger]

@Flyingmana assign it to me and I'll crack on with it... 👍

[jfksk]

I'm currently migrating a store to a cluster w/ PXC. Most tables IMHO do not need a new coloumn (maybe weee_discount does). Here is what I got:

ALTER TABLE `api2_acl_user` DROP INDEX `UNQ_API2_ACL_USER_ADMIN_ID`, ADD PRIMARY KEY (`admin_id`) USING BTREE;

ALTER TABLE `api_session` DROP INDEX `IDX_API_SESSION_SESSID`, ADD PRIMARY KEY (`sessid`) USING BTREE; 

ALTER TABLE `catalog_category_anc_categs_index_idx` DROP INDEX `IDX_CATALOG_CATEGORY_ANC_CATEGS_INDEX_IDX_CATEGORY_ID`, ADD PRIMARY KEY (`category_id`) USING BTREE; 
ALTER TABLE `catalog_category_anc_categs_index_tmp` DROP INDEX `IDX_CATALOG_CATEGORY_ANC_CATEGS_INDEX_TMP_CATEGORY_ID`, ADD PRIMARY KEY (`category_id`) USING HASH; 

ALTER TABLE `catalog_category_anc_products_index_idx` DROP INDEX `IDX_CAT_CTGR_ANC_PRDS_IDX_IDX_CTGR_ID_PRD_ID_POSITION`, ADD PRIMARY KEY (`category_id`, `product_id`, `position`) USING BTREE; 
ALTER TABLE `catalog_category_anc_products_index_tmp` DROP INDEX `IDX_CAT_CTGR_ANC_PRDS_IDX_TMP_CTGR_ID_PRD_ID_POSITION`, ADD PRIMARY KEY (`category_id`, `product_id`, `position`) USING HASH; 

ALTER TABLE `catalog_category_product_index_enbl_idx` ADD PRIMARY KEY(`product_id`);
ALTER TABLE `catalog_category_product_index_enbl_tmp` ADD PRIMARY KEY(`product_id`);

ALTER TABLE `catalog_category_product_index_idx` DROP INDEX `IDX_CAT_CTGR_PRD_IDX_IDX_PRD_ID_CTGR_ID_STORE_ID`, ADD PRIMARY KEY (`product_id`, `category_id`, `store_id`) USING BTREE; 
ALTER TABLE `catalog_category_product_index_tmp` DROP INDEX `IDX_CAT_CTGR_PRD_IDX_TMP_PRD_ID_CTGR_ID_STORE_ID`, ADD PRIMARY KEY (`product_id`, `category_id`, `store_id`) USING HASH; 

-- Already in setup 1.6. Was not applied for some reason
ALTER TABLE `log_url` DROP INDEX `url_id`, ADD PRIMARY KEY (`url_id`) USING BTREE; 

ALTER TABLE `oauth_nonce` DROP INDEX `UNQ_OAUTH_NONCE_NONCE`, ADD PRIMARY KEY (`nonce`) USING BTREE; 

-- Not sure about that - but we don't use it...
ALTER TABLE `weee_discount` ADD PRIMARY KEY (`entity_id`, `website_id`, `customer_group_id`); 

ALTER TABLE `widget_instance_page_layout` DROP INDEX `UNQ_WIDGET_INSTANCE_PAGE_LAYOUT_LAYOUT_UPDATE_ID_PAGE_ID`, ADD PRIMARY KEY (`layout_update_id`, `page_id`) USING BTREE;