[Managed DB] Tables without primary keys issue

[Michael Lepage]

Recently we are migrating our community to Digital Ocean where we are using their managed DB service. Soon after migration we started receiving these emails:

Quote

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

Currently we have only one DB node and we don't plan to extend that soon but it seems like it's a problem.

Any available fix for this? Maybe just adding the primary keys manually?

Thanks!

 

 

[Marc]

Does it state what tables?

Do not add any manually. You should never manualy change your database

[Michael Lepage]

@Marc Stridgen here is the list:

frm_core_acp_search_index
frm_core_acp_tab_order
frm_core_alerts_seen
frm_core_attachments_map
frm_core_automatic_moderation_pending
frm_core_cache
frm_core_follow_count_cache
frm_core_googleauth_used_codes
frm_core_item_markers
frm_core_item_member_map
frm_core_members_logins
frm_core_oauth_server_access_tokens
frm_core_oauth_server_authorization_codes
frm_core_post_before_registering
frm_core_reputation_leaderboard_history
frm_core_search_index_item_map
frm_core_search_index_tags
frm_core_security_answers
frm_core_sys_social_group_members
frm_core_tags_cache
frm_core_tags_perms
frm_core_tasks_log
frm_core_theme_settings_values
frm_core_view_updates
frm_forums_view_method
frm_ideas_votes

 

I know I am not supposed to change the DB manually but I need to figure out a working solution 🙂

 

What do you advice?

[Marc]

I have tagged this for our developers to see if they can advise

[Michael Lepage]

Looking forward to hear from them! Thanks!

[Michael Lepage]

@Marc Stridgen

I just wanted to ping you about this. We have already migrated (and haven't added the primary keys manually) and we are looking for a solution to avoid issues.

[Marc]

I have spoken to our developers on this. It is something that we are aware of. Unfortunately it isnt something that is a simple fix at present, for various reasons

[Michael Lepage]

Should I expect a general fix in the form of an update for everyone? If that would be the case I may add the primary keys manually for the time being and remove them before the update that fixes that.

Not sure if that makes sense to you.

[Marc]

Its not something I would expect an immediate resolution to unfortunately

[Michael Lepage]

Hi Marc,

We are still having this issue? Is there an update related to this?

Thanks!

[teraßyte]

Nothing has been changed about it so far.

 

Here's another topic with some recent replies for the same question:

 

Edited October 3, 2023 by teraßyte

[Marc]

This does appear to be an issue only with digital ocean from what I have seen so far. We indeed do not have primary keys on all tables, but this should not be causing any issues with database replication items. We have quite a lot of replication set up ourselves

[Michael Lepage]

Here is their reasoning:

 

Quote

We currently have the variable sql_require_primary_key turned on to enable user create primary key on tables to avoid replication issue, node replication, etc. This has worked in the past and was not enforced but experience, time, and the information we have gathered from frequent issues ex. time it takes to create a new node for a service from a backup with large tables.

Primary keys are essential for certain management operations also for services that do not have standby or read replica service; any node replacements are performed by first bringing up a standby to which all data from old master is replicated and without primary keys this process may take exceedingly long or fail, Also failed nodes are replaced by restoring backup, which requires playing back binary logs and that may not work if large tables without primary keys have had recent changes.

Users with missing_mysql_primary_keys, will receive this message, sent every 72 hours for services with any primary key-less tables having over 5,000 rows.

 

[Marc]

I understand what they have said there. I can tell you only that we run replication, and the software does not have primary keys. Make of that what you will of course.