Mysql upgrade from 5.7 to 8.0.x
To maintain the security and functionality of the database
By Mangayarkarasi J — “Building the relations digitally”
MySQL is the most trusted and widely used open-source database platform in use today. To maintain the security and functionality of the database, it is crucial to upgrade to MySQL 8.0. It introduces several new features that were not available in MySQL 5.7, such as window functions, common table expressions, and JSON enhancements. MySQL 8.0 offers better performance than MySQL 5.7 such as improved index and query execution.
So we underwent a MySQL upgrade from version 5.7 to 8.0.32. During the process, we encountered certain warnings when running the utility checker, necessitating specific changes before proceeding with the upgrade.
1) Addressing Object Names Conflict with Reserved Keywords
We faced conflicts with certain object names conflicting with newly reserved keywords. There are two potential solutions:
- Quoting the column name using backticks (`) for the remaining lifespan of the table. This approach requires consistent backticking of the column name.
- Renaming the column to an alternative name that isn’t a reserved word.
2) Handling Zero Date, Datetime, and Timestamp Values
As of MySQL 5.7.8, zero date/datetime/timestamp values are disallowed by default. However, by excluding NO_ZERO_IN_DATE and NO_ZERO_DATE modes in SQL_MODE settings, zero values can be inserted. Nonetheless, it’s strongly recommended to replace zero values with valid ones to ensure proper functionality.
There are two methods to resolve this:
- Adjust SQL_MODE to permit the use of ‘0000–00–00’ as a date, necessitating the removal of NO_ZERO_IN_DATE and NO_ZERO_DATE.
Query for excluding SQL_MODE
- Before upgrading, replace zero values in date/datetime columns with valid ones.
3) Trigger “trigger_name” lacks the CREATED attribute
This warning is purely informational and doesn’t require any changes to the trigger.
4) Conversion to utf8mb4 Character Set
It’s advisable to convert objects using the utf8mb3 character set to utf8mb4 for enhanced Unicode support. While utf8mb4 is the default in MySQL 8.0, this transition doesn’t impact existing data nor mandates immediate upgrades. The utf8mb3 and utf8mb4 character sets differ as follows:
- utf8mb3 supports only characters in the Basic Multilingual Plane (BMP). utf8mb4 additionally supports supplementary characters that lie outside the BMP.
- utf8mb3 uses a maximum of three bytes per character. utf8mb4 uses a maximum of four bytes per character
For each table :
Query to alter character set table-wise
For each column :
Query to alter character set column-wise
Kindly take into account the following guidelines during your conversion process:
- Ensure the Maximum Length of the Column:
MySQL can store a maximum of 65,535 bytes in a VARCHAR column. This translates to 21,845 (65535 / 3) characters for the utf8mb3 character set and 16,383 (65535 / 4) characters for the utf8mb4 character set. Hence, if any columns exceed 16,383 characters, it’s necessary to convert them to either the TEXT or LONGTEXT data type.
You can find all such columns if you run the query
Query to filter columns
- Another potential challenge that may arise post-conversion to the utf8mb4 character set is the enlargement of implicit temporary tables generated by MySQL to resolve queries.
To determine whether you encounter this challenge, monitor the global status variable “Created_tmp_disk_tables.” If you notice a significant rise in this variable following the migration, it might be prudent to consider upgrading your machine’s RAM and expanding the maximum size allocated for temporary tables.
5) Managing Redundant Row Format
Tables with redundant row format face limitations regarding index size exceeding 767 bytes. This limitation is based on the maximum value for the chosen character set. To address this, the default row_format of tables should be changed to dynamic.
Query to change row format
These adjustments are essential to ensure a smooth transition and optimal performance post-upgrade.
In conclusion, upgrading to MySQL 8.0 offers significant advancements in performance, security, and functionality compared to previous versions like MySQL 5.7. The new version introduces several innovative features, including window functions, common table expressions, and enhanced JSON support, which can greatly benefit applications and data processing tasks. However, the upgrade process may present challenges such as handling reserved keywords, managing zero date, datetime, and timestamp values, and addressing trigger configurations. Despite these complexities, the transition to MySQL 8.0 is essential for leveraging its improved capabilities and ensuring a robust and efficient database environment for current and future needs. Proper planning, testing, and adherence to best practices are crucial to successfully navigating the upgrade process and maximizing the benefits of MySQL 8.0.
We at CaratLane are solving some of the most intriguing challenges to make our mark in the relatively uncharted omnichannel jewelry industry. If you are interested in tackling such obstacles, feel free to drop your updated resume/CV to careers@caratlane.com
