What's new in MySQL 5.7

What's new in MySQL 5.7:

•         Configuration Changes
•         System Table Changes
•         Server Changes
•         InnoDB Changes
•         SQL Changes

Configuration Changes:

1.     The information_schema has table’s that contains system and status variables (Global Variables and Session Variables). As of MySQL 5.7.6 the performance_schema also contains same tables, those are actually intended to replace in information_schema which are deprecated as of MySQL 5.7.6, and will be removed in future releases.

2.     As of MySQL 5.7.6 mysqld_safe is no longer installed on Linux systems on which MySQL installed using RPM packages, the server startup and shutdown will be managed by using the systemd service.

3.     As of MySQL 5.7.6 and later mysql_install_db is moved from scripts folder to bin folder and scripts directory will no longer present.

4.     Strict SQL mode for transactional storage engine (InnoDB) – (STRICT_TRANS_TABLES) is enabled by default.

System Table Changes:

1.     The password column in mysql.user table was removed in MySQL 5.7.6 and the authentication information is stored in authentication_string column, including those formerly stored in the password column. While performing the in-place mysql_upgrade this changes will be done.

·       If performing a logical upgrade using mysqldump, --add-drop-table and --flush-privileges options must be added.

Server Changes:

1.     Support for passwords that use the older pre-4.1 password hashing is removed as of MySQL 5.7.5, which involves following changes and Applications have to modify accordingly.

·       The mysql_old_password authenticating plugin is removed. Accounts that use this plugin will be disabled at the server startup and an error message “unknown plugin” will be displayed in error log.

·       The --secure-auth option is default to server and client programs, but it is now a no-op

·       The --skip-secure-auth option is no longer available and using this will throw an error

·       The --secure-auth system variable permits only 1. 0 value is removed

·       For the old_passwords system variable, a value of 1 is no longer permitted

·       The OLD_PASSWORD() function is disabled

1.     The support for YEAR(2) was removed. Existing columns must have to be updated to YEAR(4) before upgrading the version.

2.     As of MySQL 5.7.7 CHECK TABLE …… FOR UPGRADE reports a table if it needed a rebuild or not for TIME, DATETIME and TIMESTAMP columns. avoid_temporal_upgrade variable is disabled to reconstruct the old temporal columns during mysql_upgrade.

3.     Many changes were made to the audit log for better compatibility with Oracle Audit Vault.

4.     Default value is acceptable while creating the table, but operations (insert,update) to that default columns is invalid.

InnoDB Changes:

1.     To Simplify the InnoDB tablespace discovery during crash recovery, new redo log record types were introduced. This enhancement changes the redo log format. A clean shutdown is recommended before performing an in-place upgrade.

2.     In MySQL 5.7.9, DYNAMIC replaces COMPACT as the implicit default row for InnoDB tables. A new configuration option, innodb_default_row_format specifies the InnoDB row format. Permitted values are DYMANIC, COMPACT and REDUNDANT.

3.     For existing tables that do not explicitly define a ROW_FORMAT option or that uses ROW_FORMAT=DEFAULT, any operation that rebuilds a table also silently changes the row format of the table to the format defined by innodb_default_row_format. 

SQL Changes:

1.     The GET_LOCK() function was re-implemented in MySQL 5.7.5 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.

·       Applications that rely on the behavior of GET_LOCK() releasing any previous lock must be modified for the new behavior.

·       The capability of acquiring multiple locks introduces the possibility of deadlock among clients. The MDL subsystem detects deadlock and returns an ER_USER_LOCK_DEADLOCK error when this occurs.

·       The MDL subsystem imposes a limit of 64 characters on lock names, so this limit now also applies to named locks. Previously, no length limit was enforced.

·       A new function, RELEASE_ALL_LOCKS() permits release of all acquired named locks at once.

2.     The optimizer now handles derived tables and views in the FROM clause in consistent fashion to better avoid unnecessary materialization and to enable use of pushed-down conditions that produce more efficient execution plans.

3.     Some keywords were reserved in MySQL 5.7 which is not reserved in MySQL 5.6, check those here.

4.     In UNION statements, to apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

Example:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)

UNION

(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

Note:

Previous versions of MySQL may permit such statements without parentheses. In MySQL 5.7, the requirement for parentheses is enforced.