Upgrading MySQL to version 8.0

Learn what you should look out for when upgrading an existing database from MySQL 5.7 to 8 and how to change your database to be compatible with the new version.

Although MySQL 8 was released back in 2018, a significant share of MySQL servers out there are still running MySQL 5.x. MySQL 5 had a lengthy run from its release in 2005, and thus many organizations still have databases that were built on 5.x. But Oracle has been phasing out MySQL 5.7 support for various platforms over the past few years and end of life for MySQL 5.7 is slated for October 2023.

If youre still running a database on MySQL 5.7, its time to seriously consider upgrading. You'll get several new features that give you performance improvements and security enhancements, so it is important that you do this soon - especially with the imminent end-of-life of MySQL 5.7, which means there will be no further security updates. Fortunately, this process is usually pretty straightforward, but there are several changes you may have to make. This article will cover many of the things that you should look out for when upgrading an existing database from MySQL 5.7 to 8 and walks you through the process of changing your database to be compatible with the new version.

Before you upgrade

Before you upgrade, you should make sure that you have a backup of your database. Furthermore, you should ensure that your backup works. Many seasoned IT pros have not-so-fond memories of restoring a database from a backup only to find that it was corrupted or not backing up what they thought it was. If you are using a cloud service like Amazon RDS, you can use the automated backup feature to create a snapshot of your database. If you are running your own database server, you can use the mysqldump command to create a backup of your database.

Character sets and collations

MySQL 8 has changed how character sets and collations work. The character set determines how characters are stored in the database, while the collation determines how characters are compared.

In previous versions of MySQL, latin1 and utf8 (with 3-byte characters) were commonly used. In MySQL 5.7, the default collation was utf8mb4_general_ci. In MySQL 8, however, the default character set is utf8mb4, and the default collation is utf8mb4_0900_ai_ci. utf8mb4 is a more robust version of utf8 that supports 4-byte characters. The 0900 in the collation name indicates that it is using the Unicode 9.0 standard. The ai indicates that it is using accent-insensitive collation, and the ci means that it is case-insensitive.

When upgrading to MySQL 8, it's a good idea to change your character set and collation to utf8mb4 and utf8mb4_0900_ai_ci, respectively. This will ensure that your database is compatible with the new version of MySQL, and will allow your database to support more characters, such as emojis.

How to upgrade your database to the utf8mb4 character set and utf8mb4_0900_ai_ci collation

Step 1: Change the default character set and collation for the database

To change the default character set and collation for the database, you can use the ALTER DATABASE statement. For example, to change the default character set and collation for the my_database database to utf8mb4 and utf8mb4_0900_ai_ci respectively, you would use the following statement:

ALTER DATABASE my_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Step 2 Change the character set and collation for each table

To change the character set and collation for each table, you can use the ALTER TABLE statement. For example, to change the character set and collation for the my_table table to utf8mb4 and utf8mb4_0900_ai_ci respectively, you would use the following statement:

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Obsolete data types

While MySQL didn't remove any data types, there are a few that are no longer recommended for use.

Some of these are:

• YEAR(2) - This stored a two-digit year. It is recommended to store year values as YEAR(4), which uses four digits.

• ENUM - You could create a field with a defined list of allowed values using an ENUM. While still available in MySQL 8, it is no longer recommended. Instead, it is ideal to store enumerated values in a lookup table with foreign keys.

• TINYTEXT, MEDIUMTEXT, and LONGTEXT - While these text types are available in MySQL 8, it is recommended to use VARCHAR with a specified length, eg VARCHAR(255) or TEXT for long strings of text (eg longer than 255 characters) where you won't need to search for a specific substring.

• NATIONAL, CHARACTER SET, and COLLATE clauses - While these clauses are still available in MySQL 8, they were made obsolete and are no longer recommended for use. Instead, the recommended approach for specifying character sets and collations is to use the CHARACTER SET and COLLATE options in the column definition or table definition.

Authentication Changes

MySQL 8 has changed how authentication works. The most significant change is that the default authentication plugin is now caching_sha2_password instead of mysql_native_password. This means that if you are using the default authentication plugin, you will need to update your connection strings to use the new plugin.

Legacy accounts that use the old authentication plugin must be converted to the new one using the ALTER USER statement. It is also important to update any client applications that interact with the database to support the new authentication mechanism. Finally, thorough testing should be carried out to ensure that the database is functioning correctly with the new authentication plugin.

New reserved words

MySQL 8 has added a number of new reserved words. These are words that cannot be used as identifiers (e.g., table names, column names, etc). If you are using any of these words as identifiers, you will need to change them to something else or ensure that you are quoting them. For the full list, see the MySQL documentation new reserved words in MySQL 8. A few examples of these new reserved words are:

• ACTIVE
• ADMIN
• ATTRIBUTE
• COMPONENT
• DEFINITION
• DESCRIPTION
• EMPTY
• EXCLUDE
• FINISH
• GROUPS
• INACTIVE
• INITIAL
• LEAD
• LOCKED
• MEMBER
• NESTED
• OFF
• OLD
• ORGANIZATION
• OTHERS
• OVER
• PATH
• PROCESS
• RANDOM
• RANK
• RESOURCE
• RETURNING
• REUSE
• ROLE
• SKIP
• SRID
• STREAM
• SYSTEM
• TIES
• URL
• VISIBLE
• ZONE

SQL mode changes

MySQL 8 has changed the default SQL mode, which has to do with the behavior of the server when evaluating queries. If you are using the default SQL mode, you will need to update your SQL statements to be compatible with the new mode.

In MySQL 8, the new default SQL mode is ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION. This is stricter than the default mode in earlier versions of MySQL. For instance, it has more is more specific about how GROUP BY statements are evaluated, and it will throw an error if you try to divide by zero.

Additionally, MySQL 8 has deprecated the ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, STRICT_ALL_TABLES, and TRADITIONAL SQL modes, so you should remove those from your SQL statements if you are using them.

C-style operators

MySQL 8 has deprecated the use of the C-style &&, ||, and ! operators. These operators are still available in MySQL 8, but they will be removed in a future version. It is recommended to update your SQL statements to use the standard AND, OR, and NOT operators instead.

Server error codes

MySQL 8 has changed the error codes for some server errors. If you are using these error codes in your application (for instance, to check for specific errors), you will need to update them to the new codes. For the full list of error codes, see the MySQL documentation on error codes.

Upgrading MySQL versions with no downtime

The upgrade process should be pretty straightforward for most installations. However, it is very likely that you'll need some downtime to complete the upgrade process and any required schema changes for future-proofing your database.

Conclusion

To recap, for most installations, upgrading to MySQL 8 should be a relatively straightforward process. However, it is important to test your database thoroughly after the upgrade to ensure that it is functioning correctly. Remember to ensure that you have working backups of your database before you start the upgrade process in case something goes wrong. During the process, it is ideal if you can ensure that you are using the utf8mb4 character set and utf8mb4_0900_ai_ci collation. This will ensure that your database is future-proofed for the next few years. Additionally, it is important to ensure that you are using the new authentication plugin and that you are not using any of the deprecated data types, reserved words, or SQL modes.

If you have any questions about upgrading to MySQL 8 or if you need help with the upgrade process, please feel free to contact us.