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:
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()Function | GET_LOCK() call will NOT release existing locks in any circumstances./td> | A second GET_LOCK() call will release existing locks. |
Using ROW_FORMAT=COMPRESSED When Creating or Inserting Into a Table | Compressed row sizes very close to the maximum row size could now fail. | Compressed row sizes close to the maximum row size were successful. |
| Reserved Words Changes | Added (can NOT be used as identifiers) : GENERATED,OPTIMIZER_COSTS, STORED, VIRTUAL Removed (can be used as identifiers): OLD_PASSWORD | Words that could be used as identifiers: GENERATED,OPTIMIZER_COSTS, STORED, VIRTUAL Word that could NOT be used as identifiers: OLD_PASSWORD |
SQL Mode Changes in MySQL 5.7:
The 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:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
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 ANY_VALUE().
Other modes that will be enabled by default in MySQL 5.7 are NO_ENGINE_SUBSTITUTION and STRICT_TRANS_TABLE.
Other Variables Changes to Address:
avoid_temporal_upgrade
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.
slave_net_timeout
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.
sql_mode when binlog_format=STATEMENT
An error will occur when executing INSERT or 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 binlog_format to ROW, wait until all replicas have processed all binary logs, upgrade the replica, then change binlog_format back to STATEMENT.
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 --flush-privileges.
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!