Easy-digital-downloads: Custom table registry with health check

[mintplugins]

Since we are adding custom tables for a lot of different things, it might be good to have some sort of table registry which confirms those tables exist, and if they don't upgrade notices are shown to create them.

This has happened in the MailChimp extension, where the customer was never shown the upgrade notice, and the table was never created. Since there was no error check for the table's existence, none of the settings were being saved, and it was just silently failing.

I'm wondering if it might be good to have a single function in EDD core which checks for each table's existence, including extensions with custom tables. If any of the tables do not exist, an upgrade function will run to create those tables.

Extensions can add their tables to the health check using a filter and a callback function to create the table.

[mindctrl]

I think this will be handled in release/3.0 with the new DB Table class, without any prompts. It'll just do it. This link may die due to some pending renames, but here: https://github.com/easydigitaldownloads/easy-digital-downloads/blob/release/3.0/includes/database/tables/class-wp-db-table.php

[mintplugins]

The customer affected here is not getting the tables created at all because of their collation:

mod_fcgid: stderr: WordPress database error Unknown collation: 'utf8mb4_unicode_ci' for query CREATE TABLE wp_edd_mailchimp_lists 

If the tables do not exist, we should output some sort of message instructing them what they need to do to fix their database. Otherwise these are tough support tickets to track down.

This customer is running MySQL Version 5.0.96. Upgrading their version seems to be the solution. I am working with them here to confirm: https://secure.helpscout.net/conversation/515860962/75271?folderId=749231

[mintplugins]

Unless we can somehow fix this for older versions of mySQL, which may be worth looking at as well.

[mintplugins]

Related:
https://github.com/easydigitaldownloads/edd-mail-chimp/issues/54

[sunnyratilal]

We can add something to the System Info which shows whether all of the custom tables exist.

With regards to the database collation, WordPress Core upgraded to this collation back in April 2015. See here: https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/

@JJJ and I have not set a custom collation on any of the columns so the error the customer was experiencing shouldn't occur.

[JJJ]

@mintplugins First pass at showing the table names & versions is merged into release/3.0.

The table creation & upgrade process does work very much like you described originally. The code is in the EDD\Database\Tables\Base class here:

https://github.com/easydigitaldownloads/easy-digital-downloads/blob/release/3.0/includes/database/tables/class-base.php

---

The way it works, is:

• Class instantiated very early
• Setup sanitizes the database name & version key
• Silently fails if name or key are bad
• Version is grabbed from option
• Table is added to wpdb global
• Schema is set
• Hooks are set
• Upgrades are performed if necessary
• Versions are bumped when upgraded

The missing piece (and one I think we can handle via EDD_Notices) is alerting a shop admin if a table could not be created or upgraded. This shouldn't happen, and if it does, it's not EDD's fault, because EDD uses the same database table creation methods that WordPress would have used to originally install itself. That said, ¯\_(ツ)_/¯ happens, so a notice probably makes some sense. I'll give it a think.

[sunnyratilal]

Going to close this out as I feel the database version in System Info is enough for this issue.

As @JJJ said, the database API for 3.0 works completely different to EDD_DB and is set to create tables on admin_init. This means the customer does not need to run an upgrade routine to force the tables to be created, they will get created automatically when 3.0 is activated and we listen on admin_init to ensure they exist.

It is very "difficult" for a table not to get created.