2.10.3 Changes in MySQL 5.7 (original) (raw)

$> mysql -u root -p  
Enter password: ****  <- enter root password here  
mysql> ALTER USER USER() IDENTIFIED BY 'root-password'; # MySQL 5.7.6 and up  
mysql> SET PASSWORD = PASSWORD('root-password');        # Before MySQL 5.7.6  
mysql> quit  
$> mysql_upgrade -p  
Enter password: ****  <- enter root password here

The password-resetting statement normally does not work if the server is started with--skip-grant-tables, but the first invocation of mysql_upgrade flushes the privileges, so when you run mysql, the statement is accepted.
If mysql_upgrade itself expires theroot password, you must reset the password again in the same manner.
After following the preceding instructions, DBAs are advised also to convert accounts that use themysql_old_password authentication plugin to use mysql_native_password instead, because support for mysql_old_password has been removed. For account upgrade instructions, seeSection 6.4.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

SET sql_mode = '';  
CREATE TABLE t (d DATE DEFAULT 0);  
SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';  
INSERT INTO t (d) VALUES(DEFAULT);

In this case, 0 should be accepted for theCREATE TABLE but rejected for the INSERT. However, previously the server did not evaluateDEFAULT values used for inserts or updates against the current sql_mode. In the example, the INSERT succeeds and inserts '0000-00-00' into the DATE column.
As of MySQL 5.7.2, the server applies the propersql_mode checks to generate a warning or error at insert or update time.
A resulting incompatibility for replication if you use statement-based logging (binlog_format=STATEMENT) is that if a replica is upgraded, a source which has not been upgraded executes the preceding example without error, whereas the INSERT fails on the replica and replication stops.
To deal with this, stop all new statements on the source and wait until the replicas catch up. Then upgrade the replicas followed by the source. Alternatively, if you cannot stop new statements, temporarily change to row-based logging on the source (binlog_format=ROW) and wait until all replicas have processed all binary logs produced up to the point of this change. Then upgrade the replicas followed by the source and change the source back to statement-based logging.

<AUDIT_RECORD  
 TIMESTAMP="2013-04-15T15:27:27"  
 NAME="Query"  
 CONNECTION_ID="3"  
 STATUS="0"  
 SQLTEXT="SELECT 1"  
/>

Example of new format: 

<AUDIT_RECORD>  
 <TIMESTAMP>2013-04-15T15:27:27 UTC</TIMESTAMP>  
 <RECORD_ID>3998_2013-04-15T15:27:27</RECORD_ID>  
 <NAME>Query</NAME>  
 <CONNECTION_ID>3</CONNECTION_ID>  
 <STATUS>0</STATUS>  
 <STATUS_CODE>0</STATUS_CODE>  
 <USER>root[root] @ localhost [127.0.0.1]</USER>  
 <OS_LOGIN></OS_LOGIN>  
 <HOST>localhost</HOST>  
 <IP>127.0.0.1</IP>  
 <COMMAND_CLASS>select</COMMAND_CLASS>  
 <SQLTEXT>SELECT 1</SQLTEXT>  
</AUDIT_RECORD>

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:

  1. Stop the server.
  2. Rename the current audit log file manually. This file contains log entries using only the old format.
  3. Update the server and restart it. The audit log plugin creates a new log file, which contains log entries using only the new format.
    For information about the audit log plugin, seeSection 6.4.5, “MySQL Enterprise Audit”.