What Is New in MySQL 5.7 (New Features of MySQL 5.7), mysql5.7

What Is New in MySQL 5.7 (New Features of MySQL 5.7), mysql5.7

1.4 What Is New in MySQL 5.7

1.4 New Features of MySQL 5.7

This section summarizes what has been added to, deprecated in, and removed from MySQL 5.7.

This section summarizes the outdated features that have been added and removed from MySQL 5.7.

• Added Features

• Add features

• Deprecated Features

• Outdated features

• Removed Features

• Deleted Functions

Added Features

Add feature

The following features have been added to MySQL 5.7:

The following features have been added to MySQL 5.7:

• Security improvements. the server now requires account rows in the mysql. user table to have a nonempty plugin column value and disables accounts with an empty value. for server upgrade instructions, see Section 2.10.1.3, "Upgrading from MySQL 5.6 to 5.7 ". DBAs are advised to also convert accounts that use the mysql_old_password authentication plugin to use mysql_native_password instead, because support for mysql_old_password has been removed. for account upgrade
Instructions, see Section 6.3.9.3, "Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin ".

• Security improvement. The current server requires that the account has a non-empty column value plug-in the mysql. user table row and the account with null values is disabled. For instructions on server upgrade, see 2.10.1.3, "upgrade from MySQL 5.6 to 5.7 ".

DBAs should use mysql_native_password to replace the mysql_old_password authentication plug-in for the conversion account at the same time, because the support for mysql_old_password has been deleted. Account upgrade instructions, see section 6.3.9.3, "Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin ".

MySQL now enables database administrators to establish a policy for automatic password expiration: Any user who connects to the server using an account for which the password is past its permitlifeted time must change the passwor. for more information, see Section 6.3.6, "Password Expiration Policy ". mySQL deployments installed using mysql_install_db now are secure by default. the following changes have been implemented as the default deployment characteristics:

The MySQL database administrator can now create an automatic Password Expiration Policy: any user who connects to the server and uses an account whose password exceeds the allowed lifecycle must change the password. For more information, see Section 6.3.6 "Password Expiration Policy ". Currently, it is safe to use mysql_install_db by default for MySQL deployment and installation. The following changes have been implemented as a feature of default deployment:

• The installation process creates only a single root account, 'root' @ 'localhost', automatically generates a random password for this account, and marks the password expire. the MySQL administrator must connect as root using the random password and use set password to select a new password. the random password is found in the $ HOME /. mysql_secret file.

• Only one single root account is created during the installation process. 'root' @ 'localhost' automatically generates a random password for this account and marks the password as expired. The MySQL administrator must use a random password to connect to and use a new password. The random password is in the $ HOME/. mysql_secret file.

• Installation creates no anonymous-user accounts.

• Install and create any anonymous user account.

• Installation creates no test database.

• No test database is created after installation.

• SQL mode changes. Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default.

• Changes in the SQL mode. The strict SQL mode transaction storage engine (strict_trans_tables) is now enabled by default.

Implementation for the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. in consequence, this mode is now enabled by default, to prohibit only nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.

In the only_full_group_by SQL mode, more complex queries are implemented, and deterministic queries are no longer denied. Therefore, this mode is now enabled by default. It is forbidden that queries containing only uncertainty cannot guarantee that the expressions are uniquely identified in the group.

The changes to the default SQL mode result in a default SQL _mode system variable value with these modes enabled: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION.

Changing the default SQL mode causes default SQL _mode system variable values and enabling these modes: only_full_group_by, strict_trans_tables, no_engine_substitution.

• Online alter table.

• Online table modification.

Alter table now supports a rename index clause that renames an index. the change is made in place without a table-copy operation. it works for all storage engines. see Section 13.1.6, "alter table Syntax ".

Modification is manifested in the support for renaming and renaming an index clause. There is no table copy operation in the change. It is the work of all storage engines. See section 13.1.6, "Modify the table syntax.

• InnoDB enhancements. These InnoDB enhancements were added:

• InnoDB enhancements. Added the following InnoDB enhancements:

• VARCHAR size may be increased using an in-place alter table, as in this example:
• The varchar size can be increased by modifying the table, as shown in the following example:
Alter table t1 ALGORITHM = INPLACE, change column c1 c1 VARCHAR (255 );
This is true as long as the number of length bytes required by a VARCHAR column remains the same. for VARCHAR values of 0 to 255, one length byte is required to encode the value. for VARCHAR values of 256 bytes or more, two length bytes are required. as a result, in-place alter table only supports increasing VARCHAR size from 0 to 255 bytes or increasing VARCHAR size from a value equal to or greater than 256 bytes.

This is true as long as the number of bytes in the length of a VARCHAR column is the same. For a varchar value ranging from 0 to 255, the value required for one Length Byte encoding. For a varchar value of 256 bytes or more, two length bytes are required. As a result, in alter table, only the varchar size can be increased from 0 to 255 bytes, or the varchar size can be increased to or greater than 256 bytes.

In-place alter table does not support increasing VARCHAR size from less than 256 bytes to a value equal to or greater than 256 bytes. in this case, the number of required length bytes wocould change from 1 to 2, which is only supported by a table copy (ALGORITHM = COPY ). for example, attempting to change VARCHAR column size from 255 to 256 using in-place alter table wocould return an error:

A varchar value smaller than 256 bytes cannot be added to a modified table. The value is equal to or greater than 256 bytes. In this case, the length of the required bytes changes from 1 to 2, which is supported only by a table copy (algorithm = copy ). For example, if you try to change the VARCHAR column size from 255 to 256, using alter table will return an error:

Alter table t1 ALGORITHM = INPLACE, change column c1 c1 VARCHAR (256 );
ERROR 0A000: ALGORITHM = INPLACE is not supported. Reason: Cannot change
Column type INPLACE. Try ALGORITHM = COPY.

Decreasing VARCHAR size using in-place alter table is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM = COPY ).

Table modification using varchar is not supported. Reducing the varchar size requires a copy of the table (algorithm = copy ).

Follow-up efforts...