Select Page

MySQL Upgrade Gotchas: 5.6 to 5.7

Charleste King | | July 12, 2016

MySQL 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.

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Hyperion Myth #9: SOX Audit Requests Are Time-consuming

With serious financial penalties, SOX audits can be intimidating — but they don’t have to be. Find out how you can use Datavail’s software to automatically prove SOX compliance.

Jonathan Berry | March 13, 2018

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS