Primary keys on database tables

[NexusMods]

Hi,

We were wondering if there is a reason that the following tables do not have primary keys?

• ibf_core_acp_tab_order - unique (id)
• ibf_core_alerts_seen - unique (seen_alert_id, seen_member_id)
• ibf_core_automatic_moderation_pending - unique (pending_object_class, pending_object_id)
• ibf_core_cache - unique (cache_key)
• ibf_core_follow_count_cache - unique (id, class)
• ibf_core_item_markers - unique (item_key, item_member_id, item_app)
• ibf_core_item_member_map - unique (map_class, map_item_id, map_member_id)
• ibf_core_members_logins - unique (member_id, member_date)
• ibf_core_oauth_server_access_tokens - unique (client_id, access_token)
• ibf_core_oauth_server_authorization_codes - unique (client_id, code)
• ibf_core_reputation_leaderboard_history - unique (leader_date, leader_member_id)
• ibf_core_search_index_item_map - unique (index_item_id, index_class, index_author_id)
• ibf_core_security_answers - unique (answer_question_id, answer_member_id)
• ibf_core_tags_cache - unique (tag_cache_key)
• ibf_core_tags_perms - unique (tag_perm_aai_lookup)
• ibf_forums_view_method - unique (member_id, type)

We believe that there would be a benefit to having primary keys when using MySQL in a cluster to replicate changes. However, we don't want to manually add these and customise our setup making future upgrades riskier / harder.

Thanks

[NexusMods]

Just to add a little more background. Digital Ocean issue this warning:

Quote

Our systems have indicated that your MySQL cluster 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.

We urge you to take the following actions to ensure that your database continues to operate correctly:

1. Review the following product documentation which details the issue and provides clear mitigation strategies for all impacted users.
2. Implement primary keys for all database tables as described in the documentation.

See the full details at the following link: https://www.digitalocean.com/docs/databases/mysql/how-to/create-primary-keys/

We will continue to notify you of this issue until all MySQL tables have primary keys.

It also requires the setting `sql_require_primary_key` needs to be disabled in order to setup the DB. By default this is off in normal MySQL installs, but with Digital Ocean it defaults to on due to the data loss risk and requires this API call https://docs.digitalocean.com/reference/api/api-reference/#operation/databases_patch_config.

[Nathan Explosion]

 

[NexusMods]

Hi Nathan,

Thanks for your reply. That post seems mainly concerned with OAuth and how best to integrate with Invision. It does though confirm that making changes to the database schema is a bad idea.

We are concerned with reliability and performance. Our understanding is that for replication to be reliable and performant it is recommended to have primary keys (although unique indexes will work as well https://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/).

Is there a technical reason why not to add primary keys to the above tables?

Thanks,

[Nathan Explosion]

1 minute ago, NexusMods said:

Thanks for your reply. That post seems mainly concerned with OAuth

And within that post is a link off to another discussion which contains further information.

Here - let me save you reading it again: