Track the usage of MyISAM storage engine

[desrosj]

In MySQL 5.5.5, the default storage engine of MySQL was changed from MyISAM to InnoDB. MariaDB followed suit and switched to InnoDB by default in version 10.2.

While still available, usage of MyISAM has been discouraged and considered legacy due to the lack of support for modern features such as transactions, foreign keys, and crash recovery since then..

In order to evaluate whether WordPress can officially declare MyISAM as an unsupported engine, more real world data is needed.

Because the minimum required version of MySQL/MariaDB was only recently bumped from 5.0 to 5.5.5 (see [57173]), the likelihood that MyISAM tables exist in the wild is high. But the true percentage of sites with a MyISAM table present is completely unknown.

In the past, there have been several decisions made directly related to PHP extension usage. This was helped by the changes in #48116. Additionally, #61981 and #62040 sent over a flag indicating whether the HEIC and JPEG XL image formats were supported on the server, respectively.

To ​help promote site health in the context of database software across the ecosystem, knowing the percentage usage for certain database-related practices will become important to make informed decisions.

This ticket proposes that the following checks are preformed when an update is requested from w.org and true/false values be sent over.

• Whether any default Core tables are using the MyISAM engine
• Whether any tables at all exist in the site's database using the MyISAM engine

[johnjamesjacoby]

Getting data on this is a great idea.

My initial reaction to your suggested approach is to broaden it somewhat, so that it is not MyISAM specific.

(It always seems backwards to me, to add extra support for something that is going away. 🤣)

I also understand the desire to collect the least amount of information, and make the reporting simple & clear.

I'm a +1 to this.

[johnbillion]

The underlying aim of this extra piece of data collection is to answer the question, can we introduce enhancements that require InnoDB? And in doing so declare that MyISAM is therefore no longer supported.

• I'm +1 to passing a new parameter to api.wordpress.org calls that indicate whether any core tables use MyISAM.
• I'm +/-1 to doing that for any other table, I don't think this tells us anything useful. After all, I can't imagine a feature would be introduced that potentially performs an InnoDB-specific function on a non-core table.

I briefly mentioned #30262 to Jonathan recently and I incorrectly said that views are not supported by MyISAM, this is not correct, views are not concerned about the underlying engine. I'm not aware of any other proposed features that would require InnoDB, although there are definitely features we could make use of such as transactions (used widely in the test suite).

This change needs to be accompanied by a meta change to the .org API.

This checks the default WordPress Core tables for the MyISAM engine when an update is requested and reports a non-zero number to wordpress.org.

Trac ticket: https://core.trac.wordpress.org/ticket/63640

[desrosj]

I'd like to get this into 6.9 if possible to begin collecting data.

I've opened a PR as a first pass that attempts to count the number of Core default tables that use the MyISAM engine. If no tables are found, nothing is reported, so w.org can assume the absence of myisam_count indicates that there are 0 tables using MyISAM.

I did consider a few other ways to approach this:

• Including a list of table names. It could help to see trends with certain tables, but I don't know that level of fidelity is necessary.
• Sending over a 0 value when no tables are found. I opted to not send the variable over to save a few bytes.

I also chatted this over with @johnbillion a bit today. A few notes:

• We shouldn't need to be concerned with any of the "old" tables here. If something is merged into Core that relies on InnoDB, it would not matter for these old tables.
• Having just the count is enough in some situations, but also knowing the table names with MyISAM could be more useful for helping to guide decisions in the future.

I've gone and updated the PR to send an array of table names (minus the prefix, which could be considered sensitive).

• Do you care about whether the site has mixed InnoDB/MyISAM?

I think that just the presence of MyISAM is enough here. Combined with the changes above, the (likely) InnoDB tables will be obvious (other engines are not really relevant for Core, and arguably __doing_it_wrong()).

• Do you care about what happens on SQLite databases? (Has it been tested with the SQLite plugin?) what about other DB engines that might be used?

I tested with SQLite, and it seems that the query would just return an error, which should mean an empty result is returned. The error will be written to the log depending on the WP_DEBUG configuration, and everything should proceed as normal.

From a querying perspective, all sites using SQLite (there's currently ~3,000 of them using the feature plugin) should just be ignored as engines are not a concept in SQLite.

[desrosj]

In 61001:

Upgrade/Install: Indicate use of MyISAM storage engine in upgrade check.

In MySQL 5.5.5, the default storage engine was changed from MyISAM to InnoDB. While still available, usage of MyISAM has been discouraged since and is considered legacy due to the lack of support for more modern feature.

The percentage of WordPress sites with MyISAM tables in the wild is currently unknown. This change adds a field to upgrade checks that includes a list of tables using MyISAM to help make more informed decisions about database features in the future.

Props johnjamesjacoby, johnbillion, dd32, desrosj, mukesh27.
Fixes #63640.