Adding primary keys to all tables that are missing one?

[imJexs]

Context

I am currently migrating our community to a new provider who requires primary key existence on all tables for binary log replication purposes. It's a little frustrating and was very much so unforeseen when planning this migration. Nonetheless, we're stuck with it and I've got to make it work.

Question

There are currently 23 tables in Core, 3 in CMS, 1 in Forums, and 4 in Nexus that do not have a primary key set. From what I've seen looking through the source it appears it would be safe to just add a primary key auto increment column to all of these tables, but I'm curious if anyone has any experience with this or if there are any issues I'm not seeing right now.

Future Request/Suggestion

Ideally, it would be great to see this updated with an official IPS release and set as a standard moving forward to require a primary key. I know it doesn't completely make sense for simple mapping tables, but it does help with replication and I think is justified. If I add a key/column to each of these tables, I'd prefer to not have to monitor each release for new tables without a key. 😅

Tables without a PRIMARY or UNIQUE key:

cms_database_fields_reciprocal_map
cms_url_store
core_acp_search_index
core_attachments_map
core_googleauth_used_codes
core_search_index_tags
core_sys_social_group_members
core_tasks_log
core_theme_settings_values
core_view_updates

Tables without a PRIMARY but with a UNIQUE key (could set primary as same as unique):

cms_page_widget_areas
core_acp_tab_order
core_automatic_moderation_pending
core_cache
core_follow_count_cache
core_item_markers
core_item_member_map
core_members_logins
core_oauth_server_access_tokens
core_oauth_server_authorization_codes
core_post_before_registering
core_reputation_leaderboard_history
core_search_index_item_map
core_security_answers
core_tags_cache
core_tags_perms
forums_view_method
nexus_customer_spend
nexus_package_filters_map
nexus_package_filters_values
nexus_support_staff_dpt_order

 

[Charles]

I doubt it's a good idea to change our default table setups.

We use replication to multiple read-replicas on our services without any issue.

[imJexs]

9 minutes ago, Charles said:

I doubt it's a good idea to change our default table setups.

We use replication to multiple read-replicas on our services without any issue.

Yeah, its certainly not ideal. Unfortunately this particular cloud provider requires primary keys for their replication.

Maybe we'll revisit the planning board and see if there's a better option.

[Unraid Community Admin]

Hi @imJexs,

Were you able to add primary keys to your tables to make DigitalOcean happy? We're considering a similar option.

- Justin

[Marc]

11 hours ago, Unraid Community Admin said:

Hi @imJexs,

Were you able to add primary keys to your tables to make DigitalOcean happy? We're considering a similar option.

- Justin

Its not something we would recommend attempting on our database. It would likely take a lot of work both on the database and software. As mentioned by Charles above, we use replication to multiple replicas on our own network without the need for this

[G17 Media]

That's a complete bizarre modification DigitalOcean have made to MySQL. There are definitely use cases for not using a primary ID in some tables. As @Marc Stridgen mentions it's not even needed for replication. We also use replication on our self-hosted infra without this need.

Be aware that if you make any schema change, IPS will attempt to undo it every upgrade.

Edited September 29, 2023 by G17 Media

[imJexs]

5 hours ago, G17 Media said:

That's a complete bizarre modification DigitalOcean have made to MySQL. There are definitely use cases for not using a primary ID in some tables. As @Marc Stridgen mentions it's not even needed for replication. We also use replication on our self-hosted infra without this need.

Be aware that if you make any schema change, IPS will attempt to undo it every upgrade.

I think it just comes down to how the replication is configured and what method is being used. I'm certainly no SME on database replication, but based on some searches it does appear that "no replication without a primary key" is indeed a thing in some configurations. It's a shame that DO chose to go that route when there are better solutions.

On 9/26/2023 at 1:42 PM, Unraid Community Admin said:

Hi @imJexs,

Were you able to add primary keys to your tables to make DigitalOcean happy? We're considering a similar option.

- Justin

Ultimately we chose not to modify IPS at all as I'm sure it would cause more headaches than its worth in the long run. Instead we moved our database to AWS Aurora and are running our IPS containers in AWS ECS.