search

lesterchan / wp-useronline

Enable you to display how many users are online on your WordPress blog with detailed statistics.
https://wordpress.org/plugins/wp-useronline/
36 stars 18 forks source link

Missing Primary Key on "wp_useronline" Table #36

Closed alistek closed 4 years ago

alistek commented 4 years ago

Hello,

Great job on the plugin, we use it on many sites and it works great! Our sites are hosted using the DigitalOcean platform and using MySQL Managed Databases as the backend. Recently a requirement was made to turn on the global variable, "sql_require_primary_key". This means that all new tables created must have a primary key defined. DigitalOcean is doing this to make sure that table replication and restores are performant and accurate.

When looking into what tables may be affected by this, I found the WP-UserOnline plugin has a single table that does not have a primary key defined. The create table statement does define a UNIQUE KEY, but not a PRIMARY KEY.

I ran the following SQL command to create a primary key using the same definition as the unique key and then non-exhaustively tested the plugin to see if it still worked as intended, which it appeared to have done.

ALTER TABLE my_db.wp_useronline ADD PRIMARY KEY (timestamp,user_type,user_ip);

I am wondering if it would be possible to update the plugin to update existing installations with a primary key and new installations with a create statement that adds in one as well. Thank you very much for your time and happy to provide any additional information that I can!

-Adam

lesterchan commented 4 years ago

I am not sure whether a primary key is needed and whether it will affect performance in the long term (probably need more data and people to chime in on this).

The plugin data are all transient and hence a unique key is good enough to prevent duplicate data at any given time.

alistek commented 4 years ago

Totally understand, and honestly it may not be functionally needed. The bigger problem is that without one, it prevents installation of the plugin (as the table can't be created) for sites hosted on a DigitalOcean droplet and using Managed Databases.

As a compromise, could you provide a hook for the table creation on install? That way I could overwrite the table creation command to modify UNIQUE to PRIMARY?

-Adam

alistek commented 4 years ago

Obviously, this is merely a request and your time valuable. Thank you for taking a look at it, and I appreciate any help you are able to provide. Thanks!

-Adam

alistek commented 4 years ago

After discussions back and forth with DigitalOcean, they have provided a softer way to manage this setting. Essentially a warning system that doesn't prevent the installation of the plugins. I'll go ahead and close this as I can run the alter command myself after plugin installation. So far it has been working well to no ill effects. Thank you for taking the time to look at this!

-Adam