Dear MySQL users, MySQL Server 5.7.2 (Milestone Release) is a new version of the world's most popular open source database. This is the second public milestone release of MySQL 5.7.
http://dev.mysql.com/doc/mysql-development-cycle/en/development-milestone-releases.html [ Due to a size limit of 50K and the large number of changes in the 5 months since the first milestone, this announcement has to be split into 4 parts. This is part 1. ] As with any other pre-production release, caution should be taken when installing on production level systems or systems with critical data. Note that 5.7.2 includes all features in MySQL 5.6. For information on installing MySQL 5.7.2 on new servers, please see the MySQL installation documentation at http://dev.mysql.com/doc/refman/5.7/en/installing.html MySQL Server 5.7.2 is available in source and binary form for a number of platforms from the "Development Releases" selection of our download pages at http://dev.mysql.com/downloads/mysql/ The platforms and package formats available for MySQL 5.7.2 are the same as for 5.6. Windows packages are now available via the new Installer for Windows Installer or .ZIP (no-install) packages for more advanced needs. It should be noted that the previous MSI packaging is no longer available and the point and click configuration wizards and all MySQL products are now available in the unified Installer for Windows: http://dev.mysql.com/downloads/installer/ Like the newly released MySQL 5.6.14, 5.7.2 also comes with a web installer as an alternative to the full installer. The web installer doesn't come bundled with any actual products and instead relies on download-on-demand to fetch only the products you choose to install. This makes the initial download much smaller but increases install time as the individual products will need to be downloaded. We welcome and appreciate your feedback, bug reports, bug fixes, patches, etc.: http://bugs.mysql.com/report.php The following section lists the changes in MySQL 5.7.2 since the first milestone. Many of these bug fixes have previously been released with MySQL 5.6.12 or 5.6.13 but are included here to give a complete list of changes since 5.7.1. The list may also be viewed online at http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html Enjoy! On behalf of the MySQL Build Team at Oracle, - Bjorn Munch =================================================================== Changes in MySQL 5.7.2 (Sep 21, 2013, Milestone 12) Note This is a milestone release, for use at your own risk. Significant development changes take place in milestone releases and you may encounter compatibility issues, such as data format changes that require attention in addition to the usual procedure of running mysql_upgrade. For example, you may find it necessary to dump your data with mysqldump before the upgrade and reload it afterward. Authentication Notes * Incompatible Change: Previously, account rows in the mysql.user table could have an empty plugin column value. In this case, the server authenticated such an account using either the mysql_native_password or mysql_old_password plugin, depending on whether the password hash value in the Password column used native hashing or the older pre-4.1 hashing method. With the deprecation of old-format password hashes in MySQL 5.6.5, this heuristic for deciding which authentication plugin to use is unnecessary and it is desirable that user table rows always specify explicitly which authentication plugin applies. To that end, the plugin column is now defined to be non-NULL with a default value of 'mysql_native_password', and associated server operations require the column to be nonempty. In conjunction with this plugin column definition modification, several other changes have been made: + The --default-authentication-plugin command-line option is reimplemented as the default_authentication_plugin system variable. Its use at server startup is unchanged, but now the default plugin value can be examined at runtime using SHOW VARIABLES or SELECT @@default_authentication_plugin. The variable is read only and cannot be changed at runtime. + When mysql_install_db is run, it invokes the server to initialize the mysql database. The server now assigns every user table row a nonempty plugin column value. The value is 'mysql_native_password' unless the default_authentication_plugin system variable is set otherwise at server startup. + mysql_upgrade checks user table rows and, for any row with an empty plugin column, sets that column to 'mysql_native_password' or 'mysql_old_password' depending on the hash format of the Password column value. + At startup, and at runtime when FLUSH PRIVILEGES is executed, the server checks user table rows. For any row with an empty plugin column, the server writes a warning to the error log of this form: [Warning] User entry 'user_name'@'host_name' has an empty plugin value. The user will be ignored and no one can login with this user anymore. To address this issue, execute mysql_upgrade. If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate the plugin column change into the mysql database and assign the appropriate nonempty plugin value to any empty plugin column values. However, because the server now checks for and disables accounts with empty plugin column values, it is necessary to upgrade as follows. For an upgrade in which you plan to use the data directory from your existing MySQL installation: 1. Stop the server 2. Upgrade MySQL in place 3. Restart the server with the --skip-grant-tables option to disable privilege checking 4. Run mysql_upgrade 5. Restart the server normally (without --skip-grant-tables) For an upgrade in which you plan to reload a dump file generated from your existing MySQL installation: 1. To generate the dump file, run mysqldump without the --flush-privileges option 2. Stop the server 3. Upgrade MySQL in place 4. Restart the server with the --skip-grant-tables option to disable privilege checking 5. Reload the dump file (mysql < dump_file) 6. Execute mysql_upgrade 7. Restart the server normally (without --skip-grant-tables) Diagnostics Notes * Incompatible Change: Per the SQL standard, nondiagnostic statements should clear the diagnostics area when they begin executing. Previously, MySQL differed from this in that some nondiagnostic statements did not do this. MySQL now follows the SQL standard, which affects the content of the diagnostics area for some statements. Consequently, the result from statements such as SHOW WARNINGS that display the diagnostics area now differs somewhat: + The previous behavior: SHOW WARNINGS displays information about the conditions (errors, warnings, and notes) resulting from the most recent statement in the current session that generated messages. It shows nothing if the most recent statement used a table and generated no messages. (That is, statements that use a table but generate no messages clear the message list.) Statements that do not use tables and do not generate messages have no effect on the message list. + The new behavior: SHOW WARNINGS displays information about the conditions resulting from execution of the most recent nondiagnostic statement in the current session. The result from other diagnostic statements is affected similarly (SHOW ERRORS, GET DIAGNOSTICS). The following example demonstrates the difference in behavior. Previously: mysql> DROP TABLE test.no_such_table; ERROR 1051 (42S02): Unknown table 'test.no_such_table' mysql> SELECT @@warning_count; Query OK, 0 rows affected (0.00 sec) mysql> SHOW WARNINGS; +-------+------+------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------+ | Error | 1051 | Unknown table 'test.no_such_table' | +-------+------+------------------------------------+ 1 row in set (0.00 sec) Here, the SELECT statement does not use tables and does not generate messages, so it does not change the diagnostics area. Consequently, SHOW WARNINGS output pertains to the DROP TABLE statement. Now: mysql> DROP TABLE test.no_such_table; ERROR 1051 (42S02): Unknown table 'test.no_such_table' mysql> SELECT @@warning_count; Query OK, 0 rows affected (0.00 sec) mysql> SHOW WARNINGS; Empty set (0.00 sec) Here, the SELECT statement clears the diagnostics area because it is a nondiagnostic statement. Consequently, SHOW WARNINGS output pertains to the SELECT statement (and is empty because the SELECT produces no messages). An implication of this change in diagnostics area handling is that if you expect to display the warning count as well as the list of messages, you should list the messages first because selecting the warning_count value clears the message list. Alternatively, use SHOW COUNT(*) WARNINGS to display the count; this is recognized as a diagnostic statement and does not clear the diagnostics area. Similar considerations apply to use of error_count. For compliance with the SQL standard, which states that diagnostics statements are not preparable, MySQL no longer supports the following as prepared statements: + SHOW WARNINGS, SHOW COUNT(*) WARNINGS + SHOW ERRORS, SHOW COUNT(*) ERRORS + Statements containing any reference to the warning_count or error_count system variable. In other words, those statements are now treated, in terms of preparability, the same as GET DIAGNOSTICS, which was already not preparable. Logging Notes * Incompatible Change: Several changes have been made to provide more logging control and more informative log messages: + The log_error_verbosity system variable now controls verbosity of the server in writing error, warning, and note messages to the error log. Permitted values are 1 (errors only), 2 (errors and warnings), 3 (errors, warnings, and notes), with a default of 3. log_error_verbosity is preferred over, and should be used instead of, the older log_warnings system variable. See the description of log_warnings for information about how that variable relates to log_error_verbosity (Server System Variables (http://dev.mysql.com/doc/refman/5.7/en/server-system-var iables.html)). Note The effective default verbosity is different now. The previous default (log_warnings=1) corresponds to log_error_verbosity=2, but the default log_error_verbosity is 3. To achieve a logging level similar to the previous default, set log_error_verbosity=2. + Default server verbosity is less when invoked with the --bootstrap option (such as is done by mysql_install_db): Only errors are written during the installation process so that they are less likely to be overlooked by the installer. + The log_timestamps system variable has been introduced for control of the timestamp time zone of messages written to the error log, and of general query log and slow query log messages written to files. (It does not affect the time zone of general query log and slow query log messages written to log tables, but rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.) Note The default timestamp time zone is different now (UTC rather than the local system time zone). To restore the previous default, set log_timestamps=SYSTEM. + The format of timestamps has changed for messages written to the error log, and for general query log and slow query log messages written to files. Timestamps are written using ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value of Z signifying Zulu time (UTC) or ±hh:mm (an offset from UTC). In addition, for the general query log file, timestamps are included in every message, not just when the second changes. The format of timestamps has also changed for messages written to the general query log and slow query log tables (mysql.general_log, mysql.slow_log), which now include fractional seconds. (The column type for timestamps has changed from TIMESTAMP to TIMESTAMP(6).) + Previously, the ID included in error log messages was the mysqld process ID. Now the ID is that of the thread within mysqld responsible for writing the message. This is more informative with respect to which part of the server produced the message. It is also more consistent with general query log and slow query log messages, which include the connection thread ID. For information about log output destinations, see Selecting General Query and Slow Query Log Output Destinations (http://dev.mysql.com/doc/refman/5.7/en/log-destinations.html) . For information about specific logs, see The Error Log (http://dev.mysql.com/doc/refman/5.7/en/error-log.html), The General Query Log (http://dev.mysql.com/doc/refman/5.7/en/query-log.html), and The Slow Query Log (http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html). Performance Schema Notes * The Performance Schema now instruments memory usage and aggregates memory usage statistics, detailed by these factors: + Type of memory used (various caches, internal buffers, and so forth) + Thread, account, user, host indirectly performing the memory operation The Performance Schema instruments the following aspects of memory use + Memory sizes used + Operation counts + Low and high water marks Memory sizes help to understand or tune the memory consumption of a server. Operation counts help to understand or tune the overall pressure the server is putting on the memory allocator, which has an impact on performance. Allocating a single byte one million times is not the same as allocating one million bytes a single time; tracking both sizes and counts can expose the difference. Low and high water marks are critical to detect workload spikes, overall workload stability, and possible memory leaks. These specific changes were implemented: + The setup_instruments table now has memory instruments. These have names of the form memory/component/instrument_name. Memory instrumentation is disabled by default. + The performance_schema_max_memory_classes system variable configures the maximum number of memory instruments. + The Performance_schema_memory_classes_lost status variable indicates the number of times a memory instrument could not be loaded. + Several summary tables aggregate memory-related events. For more information, see Memory Summary Tables (http://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables. html). If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database. * The Performance Schema now instruments stored program execution and aggregates statistics for them. This includes stored procedures, stored functions, triggers, and Event Scheduler events. These specific changes were implemented: + The setup_instruments table has new instruments. The statement/scheduler/event instrument tracks all events executed by the Event Scheduler. Instruments with names of the form statement/sp/program_instruction track internal instructions executed by stored programs. + The setup_objects table OBJECT_TYPE column now permits values of 'EVENT', 'FUNCTION', 'PROCEDURE', 'TABLE', or 'TRIGGER', not just 'TABLE'. + Statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) now have a NESTING_LEVEL column that indicates the event nesting level. + The performance_schema_max_program_instances and performance_schema_max_statement_stack system variables configure the maximum number of stored programs and the maximum depth of nested stored program calls for which the Performance Schema maintains statistics. + The Performance_schema_program_lost and Performance_schema_nested_statement_lost status variables indicate the number of stored programs for which statistics were lost, and the number of stored program statements for which statistics were lost. + The events_statements_summary_by_program summary table aggregates statement events per stored program. For more information, see Event Pre-Filtering (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-fil tering.html#performance-schema-pre-filtering), and Statement Summary Tables (http://dev.mysql.com/doc/refman/5.7/en/statement-summary-tabl es.html). If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database. * The Performance Schema now provides tables that expose replication information. This is similar to the information available from the SHOW SLAVE STATUS statement, but representation in table form is more accessible and has usability benefits: + SHOW SLAVE STATUS output is useful for visual inspection, but not so much for programmatic use. By contrast, using the Performance Schema tables, information about slave status can be searched using general SELECT queries, including complex WHERE conditions, joins, and so forth. + Query results can be saved in tables for further analysis, or assigned to variables and thus used in stored procedures. + The replication tables provide better diagnostic information. For multi-threaded slave operation, SHOW SLAVE STATUS reports all coordinator and worker thread errors using the Last_SQL_Errno and Last_SQL_Error fields, so only the most recent of those errors is visible and information can be lost. The replication tables store errors on a per-thread basis without loss of information. + The last seen transaction is visible in the replication tables on a per-worker basis. This is information not avilable from SHOW SLAVE STATUS. + Developers familiar with the Performance Schema interface can extend the replication tables to provide additional information by adding rows to the tables. These tables provide replication information: + replication_connection_configuration and replication_connection_status indicate the configuration parameters used by the slave for connecting to the master and the status of the connection. + replication_execute_configuration and replication_execute_status indicate, for aspects of transaction execution on the slave not specific to any given thread, the configuration parameters and the current execution status. + replication_execute_status_by_coordinator and replication_execute_status_by_worker contain thread-specific transaction execution information, either about the SQL thread (for a single-threaded slave), or about the coordinator and worker threads (for a multi-threaded slave). If the slave is multi-threaded, the SQL thread is the coordinator for worker threads. In this case, the Last_SQL_Error field of SHOW SLAVE STATUS output now shows exactly what the Last_Error_Message column in the Performance Schema replication_execute_status_by_coordinator table shows. The field value is modified to suggest that there may be more failures in the other worker threads which can be seen in the replication_execute_status_by_worker table that shows each worker thread's status. For more information, see Performance Schema Replication Tables (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-rep lication-tables.html). If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate these changes into the performance_schema database. RPM Notes * It was not possible to upgrade a community RPM to a commercial RPM using rpm -uvh or yum localupdate. To deal with this, the RPM spec file has been updated in MySQL 5.7.2, which has the following consequences: + For a non-upgrade installation (no existing MySQL version installed), it possible to install MySQL using yum. + For upgrades, it is necessary to clean up any earlier MySQL installations. In effect, the update is performed by removing the old installations and installing the new one. Additional details follow. For a non-upgrade installation of MySQL 5.7.2, it is possible to install using yum: shell> yum install MySQL-server-NEWVERSION.glibc23.i386.rpm For upgrades to MySQL 5.7.2, the upgrade is performed by removing the old installation and installing the new one. To do this, use the following procedure: 1. Remove the existing 5.7.X installation. OLDVERSION is the version to remove. shell> rpm -e MySQL-server-OLDVERSION.glibc23.i386.rpm Repeat this step for all installed MySQL RPMs. 2. Install the new version. NEWVERSION is the version to install. shell> rpm -ivh MySQL-server-NEWVERSION.glibc23.i386.rpm Alternatively, the removal and installation can be done using yum: shell> yum remove MySQL-server-OLDVERSION.glibc23.i386.rpm shell> yum install MySQL-server-NEWVERSION.glibc23.i386.rpm (Bug #16445097, Bug #16445125, Bug #16587285) Security Notes * Platform availability, usability, and security of mysql_secure_installation has been improved. Previously, this program was a shell script available for Unix and Unix-like systems. It has been converted to a binary executable program (written in C++) that is available on all platforms. Implementation as a C++ program permits mysql_secure_installation to connect directly to the MySQL server using the client/server protocol, rather than by invoking mysql to do so and communicating with mysql using temporary files. This reimplementation of mysql_secure_installation is feature-compatible with previous versions, but the following usability improvements have been made: + The validate_password plugin can be used for password strength checking. + Standard MySQL options such as --host and --port are supported on the command line and in option files. For more information, see mysql_secure_installation --- Improve MySQL Installation Security (http://dev.mysql.com/doc/refman/5.7/en/mysql-secure-installat ion.html). For more information about validate_password, see The Password Validation Plugin (http://dev.mysql.com/doc/refman/5.7/en/validate-password-plug in.html). Semisynchronous Replication Notes * Replication: Semisynchronous replication master servers now use a different wait point by default in communicating wih slaves. This is the point at which the master waits for acknowledgement of transaction receipt by a slave before returning a status to the client that committed the transaction. The wait point is controlled by the new rpl_semi_sync_master_wait_point system variable. These values are permitted: + AFTER_SYNC (the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed. + AFTER_COMMIT: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed. For older versions of MySQL, semisynchronous master behavior is equivalent to a setting of AFTER_COMMIT. The replication characteristics of these settings differ as follows: + With AFTER_SYNC, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master. In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date. + With AFTER_COMMIT, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgement. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client. If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master. The new wait point is a behavior change, but requires no reconfiguration. The change does introduce a version compatibility constraint because it increments the semisynchronous interface version: Servers for MySQL 5.7.2 and up do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7.2 and up. Trigger Notes * Previously, a table could have at most one trigger for each combination of trigger event (INSERT, UPDATE, DELETE) and action time (BEFORE, AFTER). This limitation has been lifted and multiple triggers are permitted. Along with that change, several additional modifications were made: + By default, triggers for each combination of trigger event and action time execute in the order they were created. To make it possible to specify trigger activation order, CREATE TRIGGER now supports FOLLOWS and PRECEDES clauses. Each clause takes the name of an existing trigger that has the same trigger event and action time. + The ACTION_ORDER column in the INFORMATION_SCHEMA.TRIGGERS table is no longer 0 but an integer greater than zero that indicates the order in which triggers activate. + Creation time for triggers is now maintained, as a TIMESTAMP(2) value (with a fractional part in hundredths of seconds): o The CREATED column in the TRIGGERS table is no longer NULL, for triggers created as of MySQL 5.7.2. o The same is true for the Created column of SHOW TRIGGERS output, and for the (new) Created column of SHOW CREATE TRIGGER output. o The tbl_name.TRG file that stores trigger information for table tbl_name now contains a created line with trigger creation times. For additional information, see Using Triggers (http://dev.mysql.com/doc/refman/5.7/en/triggers.html), CREATE TRIGGER Syntax (http://dev.mysql.com/doc/refman/5.7/en/create-trigger.ht ml), SHOW CREATE TRIGGER Syntax (http://dev.mysql.com/doc/refman/5.7/en/show-create-trigg er.html), SHOW TRIGGERS Syntax (http://dev.mysql.com/doc/refman/5.7/en/show-triggers.htm l), The INFORMATION_SCHEMA TRIGGERS Table (http://dev.mysql.com/doc/refman/5.7/en/triggers-table.ht ml), and Table Trigger Storage (http://dev.mysql.com/doc/internals/en/sp-storage.html#sp -storage-trigger). + If run against a table that has triggers, mysql_upgrade and CHECK TABLE ... FOR UPGRADE display this warning for each trigger created before MySQL 5.7.2: Trigger db_name.tbl_name.trigger_name does not have CREATED attribute . The warning is informational only. No change is made to the trigger. These changes have implications for backups, upgrades, and downgrades, as described following. For brevity, "multiple triggers" here is shorthand for "multiple triggers that have the same trigger event and action time." Backup and restore. mysqldump dumps triggers in activation order so that when the dump file is reloaded, triggers are re-created in the same activation order. However, if a mysqldump dump file contains multiple triggers for a table that have the same trigger event and action time, an error occurs for attempts to load the dump file into an older server that does not support multiple triggers. (See the downgrading notes for a workaround; you can convert triggers to be compatible with older servers.) Upgrades. Suppose that you upgrade an old server that does not support multiple triggers to MySQL 5.7.2 or newer. If the new server is a replication master and has old slaves that do not support multiple triggers, an error occurs on those slaves if a trigger is created on the master for a table that already has a trigger with the same trigger event and action time. To avoid this problem, upgrade the slaves first, then upgrade the master. Downgrades. If you downgrade a server that supports multiple triggers to an older version that does not, the downgrade has these effects: + For each table that has triggers, all trigger definitions remain in the .TRG file for the table. However, if there are multiple triggers with the same trigger event and action time, the server executes only one of them when the trigger event occurs. For information about .TRG files, see Table Trigger Storage (http://dev.mysql.com/doc/internals/en/sp-storage.html#sp -storage-trigger). + If triggers for the table are added or dropped subsequent to the downgrade, the server rewrites the table's .TRG file. The rewritten file retains only one trigger per combination of trigger event and action time; the others are lost. To avoid these problems, modify your triggers before downgrading. For each table that has multiple triggers per combination of trigger event and action time, convert each such set of triggers to a single trigger as follows: 1. For each trigger, create a stored routine that contains all the code in the trigger. Values accessed using NEW and OLD can be passed to the routine using parameters. If the trigger needs a single result value from the code, you can put the code in a stored function and have the function return the value. If the trigger needs multiple result values from the code, you can put the code in a stored procedure and return the values using OUT parameters. 2. Drop all triggers for the table. 3. Create one new trigger for the table that invokes the stored routines just created. The effect for this trigger is thus the same as the multiple triggers it replaces. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
