WordPress & DigitalOcean Managed Database SQL Require Primary Key issue

Question

Context: Not a lot of WP dev knowledge, but experienced PHP developer in general.

So, as everyone might know (or not, like I didn't) DigitalOcean's Managed Database system is set to have a required primary key (not globally changeable). While I understand it's better that way, sometimes when installing an existing plugin on a WordPress website, the plugin have to create tables, that may or may not have a primary key in it.

I have a list of those kind of plugins, to which I will send an improvement request where it's possible but as a workaround I would like to resolve this by making use of a hook and/or the "must-use plugin" system of WordPress to call SET SESSION sql_require_primary_key = OFF; before every create table statement (or every sql statement in general, if there is no way to focus only on create table statements).

1. Is this a "good" & feasable workaround ?
2. How can I achieve this (what hook should I be calling) ?

Answer 1

Having this same issue with wp-cerber right now and got this response from Digital Ocean:

We currently have the variable sql_require_primary_key turned on to enable users to create a primary key on tables to avoid replication issues, 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. the 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 the old master is replicated and without primary keys, this process may take exceedingly long or fail, Also failed nodes are replaced by restoring a backup, which requires playing back binary logs and that may not work if large tables without primary keys have had recent changes.

If you decide to proceed without Primary Key, you can proceed with two options:

1. We can disable the Primary requirement from our end

2. You can use the SET SESSION sql_require_primary_key = 0;

Note No. 2 is for a single session though. Once you log out, you would need to run this again.

The below URL has more information https://www.digitalocean.com/docs/databases/mysql/how-to/create-primary-keys/#how-digitalocean-uses-primary-keys