MySQL 5.6 reached its end-of-life (EOL) in February 2021. If you haven’t upgraded to MySQL 5.7, you are not the only one. It can be an intimidating endeavor. There are configuration, system table, server, Innodb and SQL changes to consider. And, documentation is not an easy read. Preparation for the upgrade is made easier by starting with this blog first.
Before any changes are made, backup the database. Moreover, upgrades should be tested before implementing them on the primary or production server. One way is to use the backup to restore a lower environment for testing. Steps for upgrades are dependent on how MySQL was initially installed and which platform you are using to host your database. For more information, read the MySQL 5.7 Upgrade documentation.
The instructions below are the result of me reading the documentation (sometimes more than once), writing notes, and writing notes of notes in an effort to simplify preparation for a MySQL 5.7 upgrade. There are further steps to take after an upgrade that should also be considered but are not discussed in this blog. Post upgrade recommendations along with more detailed information can be found in the Upgrading from Previous Series section of the MySQL 5.7 Manual.
Testing is recommended to identify issues before making changes to a production server. Described in the table below are behaviors to look out for when testing an upgrade in addition to MySQL 5.7 and 5.6 behavior comparison.
|Behaviors to Test||Behavior in 5.7||Behavior in 5.6|
|App||GET_LOCK() call will NOT |
release existing locks in any circumstances./td>
|A second GET_LOCK() call will |
release existing locks.
|Using ||Compressed row sizes very |
close to the maximum row
size could now fail.
|Compressed row sizes close |
to the maximum row size were
|Reserved Words Changes||Added (can NOT be used |
as identifiers) : GENERATED,OPTIMIZER_COSTS,
Removed (can be used as identifiers): OLD_PASSWORD
|Words that could be used as identifiers: GENERATED,OPTIMIZER_COSTS,|
Word that could NOT be used
as identifiers: OLD_PASSWORD
SQL Mode Changes in MySQL 5.7:
sql_mode system variable is used to help MySQL “communicate” or exchange information with different client applications within their environment requirements. The default includes multiple modes, not just one value.
To check which modes are being utilized, use the following queries:
If you are using
ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and
NO_ZERO_IN_DATE, strict mode also needs to be enabled to prevent a warning after an upgrade. These variables will be integrated with strict mode in MySQL 8.0 and removed in future versions.
ONLY_FULL_GROUP_BY will be enabled by default. Sometimes this mode causes queries to be rejected by applications. In this scenario, prevent errors by modifying the offending query if possible. Make nonaggregate columns functionally dependent on
GROUP BY columns or refer to them by using
Other modes that will be enabled by default in MySQL 5.7 are
Other Variables Changes to Address:
In earlier versions, the temporal columns (TIME, DATETIME, and TIMESTAMP) include fractional seconds whereas MySQL 5.7 does not. Disable
avoid_temporal_upgrade before executing
CHECK TABLE ... FOR UPGRADE, REPAIR_TABLE, or
mysql_upgrade. This might seem counterintuitive, but
mysql_upgrade will ignore the tables that include the old temporal columns and not upgrade them if
avoid_temporal_upgrade is enabled.
In MySQL 5.7,
slave_net_timeout default changes from one hour to one minute. If
slave_net_timeout is set to default (3600 seconds) before an upgrade, and the heartbeat interval is more than a minute, include the
MASTER_HEARTBEAT_PERIOD option when issuing the
CHANGE MASTER TO ... command and set the heartbeat interval to 30 seconds.
An error will occur when executing
UPDATE commands if the
sql_mode is disabled and a replica is using statement-based logging. There are two workarounds to prevent this error: 1) stop all new statements on the source and wait for the replica to catch up, then upgrade the replica; or 2) change
ROW, wait until all replicas have processed all binary logs, upgrade the replica, then change
binlog_format back to
Considerations for a Logical Upgrade:
The password column is removed from table mysql.user as of MySQL 5.7.6. When making a backup with mysqldump, you must include
--add-drop-table and do NOT include
Considerations for an In-place Upgrade:
If the database is not shutdown cleanly, changes in the undo and redo logs can cause errors after an in-place upgrade. These errors and other similar upgrade failures can be prevented by setting
innodb_fast_shutdown=0 before upgrading.
Hopefully, reading this blog was easier than reading the documentation. I do still recommend giving the documentation for a MySQL 5.7 Upgrade a read. Good luck with your upgrade!