MySQL Upgrade Gotchas: 5.6 to 5.7

MySQL 5.7 upgrade has brought in a more secure environment. Indeed, this is one of the most significant changes in MySQL development.

Password expiration

By default, passwords now expire after 360 days. This new expiration policy affects new and existing user accounts. The “default_password_lifetime” global variable is dynamic, so this can be changed or disabled easily. You can also specifically set individual user accounts to have no expiration by issuing an ALTER USER statement, ie: (ALTER USER ‘script’@’localhost’ PASSWORD EXPIRE NEVER).

Systemd

In Linux systems whereby MySQL is installed using RPM packages, now server startup and shutdown is managed using systemd rather than mysqld_safe, and mysqld_safe is no longer installed. See Managing MySQL with systemd for more details.

Also, previously, the systemd unit file did not specify any –pid-fileoption for mysqld, which made the server to fail at startup. The unit file now includes a default –pid-fileoption in the ExecStart value. There is still some flexibility as the default can still be overridden in the override.conf file by modifying both PIDFile and ExecStart to specify the PID file path name.

Furthermore, when mysqld was run with –initialize, it used chown() to set the data directory owner, even if ownership was already correct. This caused problems for AppArmor and SELinux. The server now checks whether the data directory owner is correct and skips the chown() call if so.

Audit log plugin

In Oracle Systems, there are several changes made to the audit log plugin for better compatibility with Oracle Audit Vault. It should be noted that during upgrade, the default format of the audit log file has changed. The information within <AUDIT_RECORD> elements, previously written using attributes, now is written using sub-elements. If you previously used an older version of the audit log plugin, use this procedure to avoid writing new-format log entries to an existing log file that contains old-format entries:

Stop server > Rename current audit log file > Update server > Restart

A new audit log will be created.

SQL improvements

There have been some improvements in SQL. The GET_LOCK() function has now been re-implemented using the metadata locking (MDL) subsystem and its capabilities have been extended. Previously, GET_LOCK() permitted acquisition of only one named lock at a time, and a second GET_LOCK() call released any existing lock.

Now GET_LOCK() permits acquisition of more than one simultaneous named lock and does not release existing locks. Moreover, the failed evaluation of a generated column expression for CREATE TABLE or ALTER TABLE could cause a server exit. Now if expression evaluation causes truncation or provides incorrect input to a function, the statement terminates with an error and the DDL operation is rejected.

Timeout default value

Another improvement is in the version tokens plugin which previously called the locking service using a timeout value of only one second. The timeout is now taken from the default value of the lock_wait_timeout system variable (that is, one year). Also, the filename character set which is intended for internal use did not produce an error when referenced to in SQL statements, but now it does.

In replication of MySQL, the fatal errors encountered during flushing or synchronizing the binary log were being ignored. Such errors are now caught and handled depending on the setting of binlog_error_action.

Error logging enhancements

There are some enhancements in error logging. The error message returned when trying to define a BLOB, TEXT, JSON, or GEOMETRY column with a default value (Error 1101, ER_BLOB_CANT_HAVE_DEFAULT) referred to BLOB and TEXT columns only. The same error applies to any of these four types when trying to use the DEFAULT option with it in a column definition; the corresponding error message now makes this clear by referring to JSON and GEOMETRY columns as well.

Character encoding bug fixes

Some major bugs in character encoding have now been fixed in MySQL 5.7. Some punctuation characters in the armscii8 character set are represented by two encodings; with the result that a character stored using one encoding would not be found using a search with the other encoding. For such characters, MySQL now selects the encoding with the lowest value to consistently map instances onto the same encoding.

If you require MySQL upgrade from 5.6 to 5.7, Datavail can help you do that seamlessly. With more than 600 database administrators worldwide, Datavail is the largest pure-play database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL, regardless of the build you ultimately select.