Michael Lepage Posted March 18, 2023 Share Posted March 18, 2023 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! Link to comment Share on other sites More sharing options...
Marc Stridgen Posted March 20, 2023 Share Posted March 20, 2023 Does it state what tables? Do not add any manually. You should never manualy change your database Link to comment Share on other sites More sharing options...
Michael Lepage Posted March 22, 2023 Author Share Posted March 22, 2023 @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? Link to comment Share on other sites More sharing options...
Marc Stridgen Posted March 22, 2023 Share Posted March 22, 2023 I have tagged this for our developers to see if they can advise SeNioR- 1 Link to comment Share on other sites More sharing options...
Michael Lepage Posted March 23, 2023 Author Share Posted March 23, 2023 Looking forward to hear from them! Thanks! Link to comment Share on other sites More sharing options...
Michael Lepage Posted March 30, 2023 Author Share Posted March 30, 2023 @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. Link to comment Share on other sites More sharing options...
Marc Stridgen Posted March 30, 2023 Share Posted March 30, 2023 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 SeNioR- 1 Link to comment Share on other sites More sharing options...
Michael Lepage Posted March 30, 2023 Author Share Posted March 30, 2023 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. Link to comment Share on other sites More sharing options...
Marc Stridgen Posted March 30, 2023 Share Posted March 30, 2023 Its not something I would expect an immediate resolution to unfortunately Michael Lepage 1 Link to comment Share on other sites More sharing options...
Michael Lepage Posted October 3, 2023 Author Share Posted October 3, 2023 Hi Marc, We are still having this issue? Is there an update related to this? Thanks! Link to comment Share on other sites More sharing options...
teraßyte Posted October 3, 2023 Share Posted October 3, 2023 (edited) 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 Link to comment Share on other sites More sharing options...
Marc Stridgen Posted October 3, 2023 Share Posted October 3, 2023 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 Link to comment Share on other sites More sharing options...
Michael Lepage Posted October 3, 2023 Author Share Posted October 3, 2023 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. Link to comment Share on other sites More sharing options...
Marc Stridgen Posted October 3, 2023 Share Posted October 3, 2023 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. Link to comment Share on other sites More sharing options...
Recommended Posts