7-point checklist to conquer MySQL 5.7 to 8.0 upgrade challenges

With the ever-increasing pace of technology, it’s easy to miss out on database end-of-life (EOL) dates, especially in complex multi-database environments. This is especially the case with MySQL 5.7, which reached end-of-life status in October 2023.

Databases based on the older versions like MySQL 5.7, after they stop receiving security updates, can have bugs and security vulnerabilities that put your organization at risk of data loss, breaches, and increased operational costs. If your servers are hosted on a PaaS platform running in the cloud, you won't be able to create new instances based on database technologies that have reached their end of life. As a result, you will be forced to upgrade, which might disrupt applications using those databases. While many cloud services (e.g., Azure Database for MySQL) offer extended support for MySQL 5.7 until Sep 2025, it is recommended to move to MySQL 8.0 at your earliest convenience.

Before upgrading from MySQL 5.7 to MySQL 8.0, however, it’s important to assess your current database deployments and applications for dependencies. This blog post provides all the details you need to keep in mind as you perform this major version upgrade.

Note: To address potential challenges caused by breaking changes across major versions, MySQL introduced Long-Term Support (LTS) and Innovation Release models last year. These models help distinguish between stable releases with bug fixes and those focused on new features and enhancements. Learn more.

A seven-point checklist for avoiding upgrade issues

To ensure a smooth and successful upgrade from MySQL 5.7 to 8.0, consider following this seven-point checklist.

Start with a compatibility check

You can identify compatibility issues by running the server upgrade checker utility via MySQL Shell for the specific target version. Be sure to resolve any before you perform the upgrade.

For example, to run the compatibility check from the MySQL Shell, you could run the following command:

mysqlsh> util.checkForServerUpgrade('user@targetazuremysqlinstance:3306', {"password":"password", "targetVersion":"8.0.39"})

Many MySQL Platform-as-a-Service (PaaS) providers have simplified and streamlined this process. For instance, Azure Database for MySQL offers an in-place major version upgrade from MySQL 5.7 to 8.0 that you can perform from the Azure portal with a single click. These streamlined upgrade processes come with built-in compatibility checks, making the transition seamless.

An illustration of the user experience in the Azure portal is shown in the following graphic.

For more information, in the Azure Database for MySQL documentation, see the article Major version upgrade in Azure Database for MySQL - Flexible Server.

Review and perform object conversions

Transitioning from MySQL 5.7 to 8.0 requires careful attention to several key object conversions to maintain compatibility and take full advantage of the new features. Below are some essential object conversions to consider:

• Review the objects and identify the tables which use the older row format (compact) and modify them to the new row format (Dynamic).
• If your configuration has lower_case_table_names=1, objects with UPPER CASE names may cause upgrade failures. You might need to recreate these objects with lower case names and try again. This is particularly crucial if you have a packaged deployment that creates objects or if you are migrating from Windows to Linux-based systems. Review this configuration in advance and make sure to remove such inconsistencies.
• Look for the changes to Reserved Words in MySQL 8.0.
• Check your application requirements and dependencies for leveraging the new optimal Unicode multi-byte charset and collation. Objects using the default utf8mb3 collation might be converted to utf8mb4. Starting with the database/schema level and then expand to tables and individual columns. Pay special attention to other objects like procedures, triggers, views, and events.

Beware of configuration changes and deprecated variables

Check for default configuration changes and deprecated variables when upgrading from MySQL 5.7.x to 8.0. Review and validate your current configuration to ensure that all parameters and values are compatible with the new version.

Example config changes: 

1. sql_mode changes
2. expire-logs-days --> binlog_expire_logs_seconds

Here is a list of deprecated variables in MySQL 8.0 that are no longer supported in this version or higher.

The following graphic shows the parameters deprecated in Azure Database for MySQL - Flexible Server version 5.7.

Leverage user profiles/roles and other security features

• With MySQL 8.0, you can now create User Profiles/Roles and map users to the appropriate roles, removing individual privileges granted to users. Make sure to leverage this new feature to simplify user management within the database. Here are some sample User Profiles for your reference:
  • DBA
  • Backup
  • Monitoring
  • Security/Audit accounts
  • Application SA
  • Reporting, etc.

• If you haven’t already, consider secure password policies to ensure complexity, expiration and rotation capabilities.
• Or consider integrating with Microsoft Entra ID (previously known as Azure Active Directory) for Single Sign-On (SSO) to enhance security. Note: Azure Database for MySQL seamlessly integrates with Microsoft Entra ID, offering it as an additional password authentication option that can be used alongside or instead of the native MySQL username/password-based authentication. Refer the Azure Database for MySQL documentation to learn more.
• Check for application compatibility towards new authentication protocols introduced in MySQL 8.0! Password authentication protocols were changed from mysql_native_password to caching_sha2_password. Check your application and drivers if those are compatible. For example, the following drivers are compatible with newer version: (Note - This is not an exhaustive list.)
  • PHP versions > 7.4
  • Java - Connector/J 8.0.9 or higher is required
  • MySQL Connector/NET 8.0.10 or higher

Optimize against performance-related issues

While MySQL 8.x is loaded with lots of new features and functionality (like Common Table Expressions, Window functions, enhancements to JSON functions, etc.) along with performance improvements (like Hash Join improvement, support for Histograms, etc.) many customers often report performance related issues (regressions) attributed to improper planning and testing of their upgrade approach.

Specifically, the issue of queries being slow after the 8.x upgrade is a common concern among several customers. This could be due to various factors, including differences in configuration changes, index patterns, execution plans, and optimizer changes.

• One major cause for this concern is the lack of Primary Key (PK) on tables. Some application deployments have objects without any PK/UK. Starting version 8.0.30, MySQL supports generation of invisible primary keys (sql_generate_invisible_primary_key)
• Look for changes in internal_tmp_mem_storage_engine TempTable vs MEMORY. TempTable is the default storage engine for in-memory internal temporary tables in MySQL 8.0.
• Changes to Optimizer switch: prefer_ordering_index (default on). For a query with an ORDER BY or GROUP BY and a LIMIT clause optimizer tries to use an ordered index instead of other index condition. Some query patterns might benefit from disabling this optimization.

For Azure Database for MySQL flexible servers, you can use pre-defined workbooks to evaluate the performance as shown in the following graphic.

Note: Some workloads may not exhibit enhanced performance after upgrading from 5.7 to 8.0. We suggest that you evaluate the performance of your workload by creating a replica server (as a test server), promoting it to a standalone server and then running the workload on the test server prior to implementing the upgrade in a production environment.

Potential application changes

Some features and functionality might require code changes and involve changes to your application. So, it is highly recommended to test your application in a lower environment (such as Staging or QA) during any such major upgrades or migrations. Additionally, performing benchmarks to validate performance before planning for production upgrades and cutover is essential.

Some points to keep in mind:

• Previous optimizer hints related to FORCE INDEX might behave differently due to the optimizer improvements in newer version.
• Performance Schema instruments have been expanded and changes to Information Schema and Sys Schema have been introduced. This might require modifications to your monitoring solutions if you are querying these schemas.

Here are some examples of SQL warnings and errors:

Warning 1287 in MySQL 8.0.21: Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead.
Error 1287 'VALUES function' is deprecated and will be removed in a future release. 
Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace 
VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
Error 1287 '!' is deprecated and will be removed in a future release. Please use NOT instead

Conclusion

In conclusion, transitioning from MySQL 5.7 to 8.0 requires careful planning and execution to avoid potential disruptions. By considering these seven key factors before and during the upgrade, you can ensure a smooth and successful migration.

Don't wait any longer — use the insights from this blog to upgrade to MySQL 8.0 today! If your MySQL 5.7 workload is hosted on Azure Database for MySQL, refer this tutorial to perform an in-place major version upgrade with just a click of a button!

Move away from risks associated with staying on an unsupported MySQL version and take advantage of all that MySQL 8.0 has to offer.

For a more exhaustive guide, see the whitepaper “How to Plan a MySQL 5.7 to MySQL 8.0 Upgrade Before End of Life” by Datavail.