Introduction
On october 26, 2015, MySQL 5.7 is released for the first time in «General Availability» version with revision 5.7.9.
The pression of the competition of other opensource database and the success of MariaDB which relies systematically on each new major version of MySQL leads Oracle to a tightrope exercice : To innove without encroach on analytic territory, preserve of her commercial database. In this context, MySQL 5.7 brings his batch of changes.
New features of Version 5.7
After the re-writing of some of the code, MySQL 5.7 greatly improves performances, GIS support (Géographic Information System) and now supports computed columns. Hereafter are shown some evolutions arbitrary choosed by their interests, in the long list of new features.
Security evolution
MySQL 5.7 now requires the completion of mysql.user.plugin column, that contains autentification plugin. On the other hand, the user is disabled.
Otherwise, the administrator is invited to convert to mysql_native_password the user account who own the mysql_old_password plugin, this latter is no longer supported
SQL modes and associated behaviors
The STRICT_TRANS_TABLE mode is enabled by default for transactionnal engines like InnoDB.
Moreover, the ONLY_FULL_GROUP_BY mode now acts with discernment :
It only imposes repetition of GROUP BY fields in the SELECT clause if there is effectively an ambiguity in the choice of the values to aggregate.
Index Renaming
To modify the index’s name, it was before necessary to destroy it and to recreate it.
The operation is quite time consuming when it applies to millions of rows.
It is now simply possible to rename an index by doing RENAME INDEX old_nam TO new_name in an ALTER TABLE clause.
InnoDB Improvements
The size modification of a VARCHAR column in a table structure supports the INPLACE algorythm, meaning that the modification is applied directly in the table without going trought a temporary copy. This only if the new size storage needs the same number of bytes.
For exemple, the «INPLACE» modification of a VARCHAR column size from 40 to 256 characters is possible. From 300 to 2000 likewise. But not from 200 to 300 because these two values are respectively stored on one and two bytes.
The temporary table managment is optimized, InnoDB now supports the DATA_GEOMETRY type for the storage of Spacial data and the Spacial Indexes.
Moreover innodb_buffer_pool_size can be modified without requiring the server restart.
Triggers
Previously, a table only admitted one trigger for a combination between an event (INSERT, UPDATE, DELETE) and a temporal situation (BEFORE or AFTER).
This restriction is removed in MySQL 5.7.
CTRL C behaviour in console
The CTRL C behaviour changed. In MySQL 5.7 terminal, the actual process is still interrupted but the user is redirected to the shell and doesn’t quit MySQL anymore.
Replication Improvement
Management of concurrent access on binary logs has been optimized.
Since MySQL 5.7.2, a MASTER’s log can be read by several processes, even when it’s being written, if the reading attempts are made in the area before the last event successfully written.
Furthermore, since MySQL 5.7.4, do a STOP SLAVE on slave servers when changing MASTER is no longer mandatory.
Finally, the MySQL 5.7.6 version allows to replicate several masters servers to the same slave server to, for example, federate data of various production servers.