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

Starting November 1, 2023, MySQL 5.7 will no longer receive updates or security patches, which could potentially impact the performance and security of your website. As this version will no longer be secure, WP Engine will End-of-Life MySQL 5.7 across our platform and upgrade all websites to MySQL 8.0 by the end of October 2023. Website owners and technical contacts will receive several notifications prior to upgrading in order to provide ample time for testing.

It’s recommended to test your website for compatibility as soon as possible to ensure functionality when the upgrade arrives.

---

Check Current MySQL Version

To check which version of MySQL an environment 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 (/wp-admin/site-health.php?tab=debug)

5. Scroll down and expand Database
6. Locate Server version

Websites showing version 5.7 have not been upgraded (shown in the image below). Websites showing version 8 have been upgraded.

---

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.

Connect to WP Engine

Local must be connected to WP Engine via API to easily pull and push the website for testing.

This connection process only needs to be performed once per User Portal user. Any sites a user has been granted access to in the User Portal will become available in Local after connecting the API. If your user has been added to multiple hosting accounts and API access is enabled for each account, those sites will also populate in Local.

1. In a web browser, open the API Access page of the WP Engine User Portal at: https://my.wpengine.com/api_access
   • This page can also be accessed in the User Portal at Users > API Access.
2. At the top right, click Generate Credentials
   • If API Access is disabled for the account, and you are an account owner, enable API using the toggle. If you are not an account owner, reach out to an account owner to enable API access. Learn more about WP Engines API here.

3. Leave this page open. The API password will only be shown once. Do not close this window until the credentials are copied into Local in the next steps. If this pop-up is closed, a new set of credentials must be generated in the same way.

4. Launch the Local application on your computer
5. Select the Connect button on the left (cloud icon)
   • If this is your first time launching Local, you may see the Create a site page first. Click (x) at the top right to exit this wizard and view the option to Connect.
6. Click Connect to a platform

7. From the pop-up, locate WP Engine and click Log in
8. A pop-up will prompt for API credentials
   • Copy and paste the API Username and Password exactly as shown on the API Access page in the WP Engine User Portal.
9. Click Connect to WP Engine

When successfully connected to WP Engine, a list of WP Engine sites will populate in Local.

Pull to Local

With Local connected to WP Engine, the live site can now be copied to Local for testing. This automated process is called a “pull”, and creates a copy of the live site into Local. Pulling to Local will not affect the live site, and the Local environment will be functionality independent unless it’s specifically requested to “push” back to WP Engine.

Existing Local sites cannot have their MySQL version changed, but may already be on the updated version. If a site in Local exists but is not on MySQL 8, pull to a new Local site on the updated database version using the process below.

1. In Local, select Connect (cloud icon)
2. Locate the site name and click Pull to Local

3. At the top, select Create a new site
   • What should we call your new site? The site name will auto-fill based on the site name used in the User Portal. This can be changed if needed.
   • Select environment – Select Production
4. Click Connect & Pull Site

The site will take a few minutes to configure automatically.

If prompted for permissions on your computer, be sure to allow access to Local.

Push Updates Live

Once any corrections have been made to your site’s code in Local those changes can be moved to WP Engine. WP Engine will not support multiple versions of MySQL on a server at a time, and servers will receive their update over time after notice has been provided. This means your site must still be compatible with MySQL 5.7, and no MySQL 8 dependent functions should be added prior to your server’s update.

For this reason, we recommend testing any changes on a Staging or Development environment before taking them live on Production.

Push to Staging or Development

In the WP Engine User Portal, ensure there is a Staging (Stg) or Development (Dev) environment added to this site where the changes can be pushed. Learn how to add an environment here. If adding a new environment, it may take a few minutes to display as an option in Local.

1. Open Local, view the site’s Overview
2. In the bottom right corner, click Push to WP Engine (cloud icon, with arrow pointing up)

3. Push site to: The site will be selected automatically. This was configured when pulling to Local previously.
4. Select environment: Select Staging or Development. We do not recommend pushing to Production at this time.
   • If Staging or Development do not exist in WP Engine, learn how to add a new environment here. It may take a few minutes for the new environment to sync back to Local.
5. Include Database: Leave deselected. It is best practice to not include the database in a push.
6. Push: All modified files
7. Click Push to WP Engine

The process may take some time, as a backup will be created of the environment being pushed to on WP Engine.

Once pushed to WP Engine, test the site again to ensure backwards compatibility with MySQL 5.7. 

Copy to Production

After the site is confirmed working in Staging (Stg) or Development (Dev) changes should be copied to Production (Prd). It’s recommended to exclude the database when copying to Production. This process can be automated using the Copy environment tool.

1. Open the WP Engine User Portal
2. Select the site name from the list
3. Click Quick Actions at the top right
4. Select Copy Environment
   • Source environment: Stg or Dev, where the changes were pushed from Local. (In the example below we’ve used Staging.)
   • Destination environment: Production
   • Include: Only the file system. It is best practice to exclude the database when copying to Production.

4. Click Review and Confirm
   • Verify that the updated Staging or Development environment is Source and is being copied to the Destination of Production.
5. Click Copy the environment.
   • You will receive an email once the copy has completed.
   • A backup will be created before and after the copy process.

At this point it’s recommended to perform a final check of the Production environment to confirm the site is performing as expected.

---

Local Logs

Local supports several forms of logging to assist with troubleshooting.

Xdebug Log

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 

PHP Error Log

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 is the expected downtime for the upgrades?

A: On average, downtime can take up to 10 minutes as MySQL is restarted. Downtime will fall within the normal maintenance window for your server(s).

Q: Will the upgrade window be the same across all sites on my account?

A: Your designated upgrade window is based on the datacenter where your sites reside. If you have multiple accounts across different data centers, upgrade windows may vary by account. Our maintenance window is available here.

Q: What is the timeline for upgrades?

A: MySQL 5.7 –> 8.0 upgrades are slated to run 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: What is WPE doing to mitigate risk to my site related to the upgrade? 

A: Pre-upgrade, WP Engine will identify MySQL 8.0 compatibility gaps with the MySQL Upgrade Checker Utility

• If your database is incompatible, we will contact you via email with more information on correcting identified issues before attempting to upgrade your environment.
• Please note that database compatibility is not an exhaustive measure of site compatibility. You are responsible for ensuring your site code does not execute queries incompatible with MySQL 8.0. 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 also available directly from MySQL: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html  
• We will upgrade your website environment to MySQL 8.0 before the end-of-life date, if your databases pass the upgrade check. We will immediately and automatically revert your sites to 5.7 if we detect issues. 
• On or just before October 31st, we will upgrade your environment to MySQL 8.0 without reverting. We will not be reverting sites to MySQL 5.7 after the EOL date because of potential vulnerabilities with MySQL 5.7 post-security support.

Q: What is WP Engine doing to manage tech support load throughout this upgrade process?

A: Our main priority throughout the MySQL upgrade process is to continue providing the world-class level of support our customers expect from us. As such, we plan to roll out automated upgrades in waves, with specific attention to support volume associated with each wave. 

Q: What happens if my website experiences issues during or post upgrade?

A: During the upgrade, we will be using proprietary testing where we will check status codes before and after. In the event our smoke testing detects regressions, we will revert the upgrade and reach out to impacted customers via email. 

Should the site successfully upgrade and return a 200 response code, but afterwards has display or minor functionality issues, this could go undetected by our testing methods. This is why we recommend doing a post-upgrade test of your site(s) to ensure the expected functionality is in place.

Q: How can we guarantee that our site will have no issues with the upgrades?

A: As a managed host, we strive to ensure you are always on the latest and greatest versions of software. Sometimes, this will come with the added responsibility of making sure your site code is up to date. This is one of the reasons we have invested in awesome tools like Local to help you ensure the process is as seamless as possible. 

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: 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: If I notice an issue post-upgrade, are my sites able to be reverted until the issue is resolved?

A: In scenarios where a customer discovers MySQL 8.0 compatibility issues post-upgrade, we highly recommend “fixing forward” to mitigate risks of data loss, and our support team is available to provide assistance to impacted customers. 

However, we do have the ability to temporarily revert to the latest available backup on MySQL 5.7 if necessary and as requested. Please note: in the event of a MySQL 5.7 security vulnerability, all 5.7 sites would require an immediate upgrade to MySQL 8.

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: Will Local pull files from LargeFS for testing?

A: Local will not pull LargeFS files down to your machine, but it should maintain links to LargeFS such that they load properly on your local instance of the site. 

Q: Can I access my client accounts via Local?

A: As long as your user has access to the accounts (and API access is enabled on those accounts), every site you have access to will appear in Local with a single key. 

Q: Does LocalWP exactly mimic the WPE environment? Meaning, if I have a specific version of PHP and MySQL set in WPE, do I need to manually set those in LocalWP?

A: Local is configurable, and you can select the specific PHP and MySQL versions you wish to test with. When pulling from WP Engine, you can specify which versions you would like to use. 

The main difference between the environments would be resources – less memory, fewer PHP resources, etc. But those won’t affect your ability to test MySQL 8 compatibility and be edited in the site’s configuration if needed.

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