Test for MySQL 8 Compatibility

WordPress® relies on the robust and widely-used MySQL database to store and manage a website’s data.¹ To ensure the best performance, security, and compatibility, it is essential to keep database software up-to-date.

As a part of our managed hosting offering, we will be working towards having all new and existing sites using MySQL 8. This help doc will help you navigate your site(s) MySQL versioning, understanding what has changed between version 8 and version 5.7, and how to test compatibility on your sites.

Check Current MySQL Version

To check which version of MySQL a site is currently running on, the WordPress Site Health feature can be used (available on sites running WP core version 5.2+).

1. Log in to the website’s wp-admin dashboard
2. Expand Tools
3. Click Site Health
4. Select the Info tab (Or go directly to this page by adding the following string to the end of your root URL: /wp-admin/site-health.php?tab=debug)
   
5. Scroll down and expand Database
6. Locate Server version to determine which version of MySQL is being used
   

Test Compatibility in Local

You can test your WordPress website’s compatibility with MySQL 8 on a local computer using Local. This is a free WordPress development tool available to everyone, and includes the Local Connect utility (see the Local Connect help doc for more information). This makes pulling and pushing a site from Flywheel easier than ever. On average, you can use Local to install, connect, and copy your Flywheel site in just 10 minutes.

Download Local for free here.

Local supports several forms of logging to assist with troubleshooting.

Enable Xdebug in Local by toggling Xdebug to On. The log can then be viewed by clicking Details, or with the site running at http://yoursite.local/local-xdebuginfo.php

• Learn more about Xdebug in Local here: Xdebug within Local
• For more information on Xdebug see: Xdebug: Documentation
• For a list of xDebug errors see: Xdebug: Documentation » Description of errors

The PHP error log can be accessed in the Local site’s file system. With the site open in Local, click Go to the site folder. Next, open logs > php > error.log.

Change List

Below are changes that may commonly cause issues when upgrading from MySQL 5.7 to 8. This is not a comprehensive list, but may provide further insight into primary areas of impact.

• Changes in SQL Modes and GROUP BY Behavior
• Problem: MySQL 8.0 is more strict about GROUP BY clauses, requiring that selected columns are functionally dependent on GROUP BY columns when ONLY_FULL_GROUP_BY is enabled (which is the default in MySQL 8.0).
• Mitigation: Modify the SQL queries to be compatible with the new requirements or change the SQL mode to disable ONLY_FULL_GROUP_BY.
• Documentation:
  MySQL 5.7: SQL Mode
  MySQL 8.0: SQL Mode

• Regular Expression Changes
• Problem: MySQL 8.0 uses International Components for Unicode (ICU) for regular expression operations, which can lead to different results compared to previous versions.
• Mitigation:Update any REGEXP usage in your codebase to be compatible with the ICU implementation.
• Documentation:
  MySQL 5.7: Regular Expressions
  MySQL 8.0: Regular Expressions

• Default Character Set Change
• Problem: The default character set changed to utf8mb4 from utf8. This might cause issues with plugins or themes that assume the utf8 character set.
• Mitigation:Update your code to handle the new default character set or explicitly set the character set to utf8 when connecting to the database.
• Documentation:
  MySQL 5.7: Character Set Configuration
  MySQL 8.0: Character Set Configuration

• Removed Functions
• Problem: Some functions are removed in MySQL 8.0, such as PASSWORD(), ENCRYPT(), DES_DECRYPT(), DES_ENCRYPT(), etc.
• Mitigation:Update your code to replace these functions with alternatives.
• Documentation:
  MySQL 5.7: Functions and Operators
  MySQL 8.0: Functions and Operators

• Reserved Keywords
• Problem: MySQL 8.0 has additional reserved words. If unquoted identifiers in your SQL match these reserved words, it could cause errors.
• Mitigation:Quote identifiers that match any of MySQL’s reserved words.
• Documentation:
  MySQL 5.7: Keywords and Reserved Words
  MySQL 8.0: Keywords and Reserved Words

• Changes in Error Handling
• Problem: MySQL 8.0 is more strict about error handling, throwing errors for invalid date, datetime, and timestamp values by default, while MySQL 5.7 would only give warnings.
• Mitigation:Update your code to ensure valid date, datetime, and timestamp values are provided.
• Documentation:
  MySQL 5.7: Error Handling
  MySQL 8.0: Error Handling

MySQL 8 Upgrade FAQ

Q: I need help with making updates to my site. What resources does Flywheel offer?

A: Our Support Team is always available to help answer any questions you may have regarding this upgrade (use code: mysql8).

If you have any site code-related issues that our team is unable to support, we have an extensive agency partner directory where you can connect with professionals:

• Flywheel Agency Partners
• Codeable

Q: What is the timeline for upgrades?

A: MySQL 5.7 to 8.0 upgrades will be performed throughout the remainder of 2024 with the goal of being completed before 2025.

Q: What should I look for to determine if my site is compatible?

A: You may review the MySQL 5.7 – 8.0 change list here for some query language changes that may impact your sites. Comprehensive changes are available directly from MySQL here:

If your site code doesn’t leverage any of the changing or deprecated SQL, and your internal testing against MySQL 8.0 does not surface any issues, it’s highly likely that your site(s) is compatible with the new version.

Most reputable plugins and themes are prepared for MySQL 8, reaching out to plugin developers directly would be the recommended source of truth.

Q: How can we identify errors related to the MySQL upgrades? Will there be any specific logging like with PHP errors or any debugger views we should be monitoring?

A: If PHP code executes a query that is incompatible with MySQL 8.0, you can expect a PHP “Fatal error” log identifying the query that failed.

We recommend using Local as outlined previously in this help doc. Error logs are available in Local, and after the server has been converted those logs are available in the Flywheel app.

Q: Is Local able to test MySQL 8.0 compatibility on multisites?

A: Local is compatible with multisite, though there are a few extra steps for configuration. Please see our guide here on multisite for WordPress in Local: Multisite for WordPress with Local.

Q: Can I use my existing Flywheel account for testing on Local?

A: If you do not have a Local account created, you will need to create one before pulling and pushing sites to your Flywheel account. The Local account and your Flywheel account are separate but can be connected following the instructions outlined here: https://localwp.com/help-docs/local-features/local-connect/. To clarify, you do not need a Flywheel account to use Local.

Q: Is there a limitation on site size for importing into Local?

A: There is no size limitation, though sites over 5GB may be slower. See this help doc for working with large sites in Local: Large sites and Local.

Q: Can I update themes or add plugins to a Local site for testing?

A: Yes! Once you have pulled the site down into Local, you can log in to the backend just like a live WordPress site and upgrade/install new plugins.

Q: Can a Local version of the site be shared and tested with other members of my team or do they have to set it up on their side as well?

A: You can use Local’s Live Link functionality to share the site from your machine if you wish to have others test it – see Live Links – Local for more information.

Q: Can we implement our staging testing workflow on Local?

A: Yes! We recommend downloading the current production version of your site to Local. Then after testing, we recommend that the Local version gets pushed up to your Flywheel Staging site and then to your Flywheel Production site.

Q: How does testing in Local work with large developer teams?

A: Local is a great testing resource for teams! We’d recommend using some form of source control to avoid overwriting changes. See this help doc on using Local with source control – Develop with Local and GitHub.

Q: I have some additional questions not listed here, where should I go?

A: Glad you asked! Our Support Team is always available 24/7 to help answer any questions you may have regarding this upgrade (use code: mysql8).

Further Assistance

If you need help making changes to your site to become compatible with MySQL 8 and would like to consult with an Agency Partner, check out our Agency Directory.

Have a smaller project? Check out Codeable, a premium WordPress development partner.

If you have hired a developer for assistance, you can provide them with this article. Be sure to add them as a Collaborator in your Flywheel app. Learn how here.

Need help?

If you have any questions our Happiness Engineers are here to help!