3

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) ?
Solid
  • 105
  • 2
  • 15
  • 1
    Have you tried that to verify they let you disable the primary key requirement before creating the table? It looks like if you create tables without primary keys the tables won't be replicated properly, which is why they are enforcing this requirement now. – Mark B Mar 25 '21 at 16:10
  • @MarkB thank you for that remark. I had no idea that was the reason. So only solution I have is to migrate my databases it seems, since I have no control over some of the plugins my clients use, and creating databases manually for each plugin isn't realistic since every update could potentially create a new one... That's a BIG bummer... – Solid Mar 26 '21 at 18:21
  • @MarkB; also: yes. I have verified that. Because, when I create the tables manually (and disabling the sql_require_primary_key option it through an SQL editor); the table is created successfully even without primary key. – Solid Mar 26 '21 at 18:24
  • 1
    Yes, I am also really bummed by this. I'm moving something from DO to Lightsail currently because of this, since Lightsail has no such index requirement. – Mark B Mar 26 '21 at 18:25
  • @MarkB if you don't mind me asking: does that mean that replication won't be possible for such databases; even if I move on to AWS or a separated SQL droplet cluster? – Solid Mar 26 '21 at 18:27
  • Replication works fine on AWS, including Lightsail, without the primary keys. I see this as a flaw in Digital Ocean's database replication feature. – Mark B Mar 26 '21 at 19:22
  • Good 2 know. I didn't even know Lightsail was a thing. I opted for DO because of EC2 high pricing. Thank you for this ! I guess you got me sold... – Solid Mar 27 '21 at 12:56

1 Answers1

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

selfagency
  • 1,481
  • 1
  • 9
  • 12