Test for MySQL 8 Compatibility

Last updated on February 21st, 2024

database mysql8 performance security troubleshooting

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. WP Engine’s previous version of MySQL 5.7 reached its end-of-life status in October 2023.

As of November 1, 2023, MySQL 5.7 no longer received updates or security patches, which could have impacted the performance and security of your website. As this version was no longer secure, WP Engine removed MySQL 5.7 across our platform and upgraded all websites to MySQL 8.0 in October 2023.

All websites on WP Engine are using MySQL 8.

Contents hide

1 Check Current MySQL Version

2 Test Compatibility in Local

3 Change List

4 MySQL 8 Upgrade FAQ

5 Further Assistance

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+).

Log in to the website’s wp-admin dashboard
Expand Tools
Click Site Health
Select the Info tab (/wp-admin/site-health.php?tab=debug)

Scroll down and expand Database
Locate Server version to determine which version of MySQL is being used

Test Compatibility in Local

WordPress websites can have their compatibility with MySQL 8 tested on a local computer using Local. This is a free WordPress development tool available to everyone, and includes the WP Engine Connect utility. This makes pulling and pushing a site from WP Engine easier than ever. On average, Local can be installed, connected, and copying your WP Engine 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: https://localwp.com/help-docs/advanced/using-xdebug-within-local/
For more information on Xdebug see: https://xdebug.org/docs/
For a list of xDebug errors see: https://xdebug.org/docs/errors

The PHP error log can be accessed in the Local site’s file system. With the site open in Local, click Go to 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 WP Engine 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: https://wpengine.com/partners/agencies/ https://www.codeable.io/partners/wpengine-offer/

Q: What was the timeline for upgrades?

A: MySQL 5.7 to 8.0 upgrades ran throughout the months of September and October 2023.

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 here. Error logs are available in Local and after the server has been converted those logs are available in the User Portal.

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 in Local: https://localwp.com/help-docs/advanced/wordpress-multisite-with-local/

Q: Can I use my existing WPE 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 WP Engine account. The Local account and your WP Engine account are separate but can be connected via API authentication. To clarify, you do not need a WP Engine 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: https://localwp.com/help-docs/troubleshooting/working-with-large-sites-in-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 login to the backend just like a live WP 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 https://localwp.com/live-links/ for more information.

Q: Can we implement our dev/staging/production 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 WP Engine Dev environment, then Staging, then eventually replacing your WP Engine Production environment.

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 – https://localwp.com/help-docs/advanced/developing-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, and receive a free consultation and $60 off your next project.

If you have hired a developer for assistance this article can be provided, but ensure that you have done the following for your developer:

Add their user to the User Portal and grant the user access to the necessary environments. Learn how here.
Enable API access for the hosting account. Learn more here.

NEXT STEP: Learn how to upgrade PHP