MySQL Community Server 5.7.3 has been released (part 1/2)

[Bjørn Munch]

Dear MySQL users,

MySQL Server 5.7.3 (Milestone Release) is a new version of the world's
most popular open source database. This is the third public milestone
release of MySQL 5.7.

[Due to length restrictions, this announcement is split into two parts.
This is part 1.]

http://dev.mysql.com/doc/mysql-development-cycle/en/development-milestone-releases.html

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.3 includes all features in MySQL 5.6.

For information on installing MySQL 5.7.3 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.3 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.3 are the
same as for 5.6.

MySQL Server 5.7.3 is also available from our Yum repository for
some Linux platforms, go here for details:

http://dev.mysql.com/downloads/repo/

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/

5.7.3 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.3 since the
previous milestone.

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

Enjoy!

On behalf of the MySQL Build Team at Oracle,

- Bjorn Munch

--------------------------------------------------------------------

Changes in MySQL 5.7.3 (3 December 2013, Milestone 13)

   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.

   Optimizer Notes

     * The server no longer uses a temporary table for UNION
       statements that meet certain qualifications. Instead, it
       retains from temporary table creation only the data structures
       necessary to perform result column typecasting. The table is
       not fully instantiated and no rows are written to or read from
       it; rows are sent directly to the client. As a result, The
       result is reduced memory and disk requirements, and smaller
       delay before the first row is sent to the client because the
       server need not wait until the last query block is executed.
       EXPLAIN and optimizer trace output will change: The UNION
       RESULT query block will not be present because that block is
       the part that reads from the temporary table.
       The conditions that qualify a UNION for evaluation without a
       temporary table are:

          + The union is UNION ALL, not UNION or UNION DISTINCT.

          + There is no global ORDER BY clause.

          + The union is not the top-level query block of an {INSERT
            | REPLACE} ... SELECT ... statement.
       (Bug #50674, Bug #11758470)

     * The modified filesort algorithm now includes an additional
       optimization designed to enable more tuples to fit into the
       sort buffer: For additional columns of type CHAR or VARCHAR,
       or any nullable fixed-size data type, the values are packed.
       For example, without packing, a VARCHAR(255) column value
       containing only 3 characters takes 255 characters in the sort
       buffer. With packing, the value requires only 3 characters
       plus a two-byte length indicator.
       For data containing packable strings shorter than the maximum
       column length or many NULL values, more records fit into the
       sort buffer. This improves in-memory sorting of the sort
       buffer and performance of disk-based merge sorting of the
       temporary file.
       In edge cases, packing may be disadvantageous: If packable
       strings are the maximum column length or there are few NULL
       values, the space required for the length indicators reduces
       the number of records that fit into the sort buffer and
       sorting is slower in memory and on disk.
       Packing is not applicable if the filesort uses a priority
       queue for sorting, as is the case when an ORDER BY ... LIMIT
       optimization is applied (see Optimizing LIMIT Queries
       (http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.htm
       l)).
       If a filesort is done, optimizer trace output includes a
       filesort_summary block. For example:
"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}
       The sort_mode value provides information about the algorithm
       used and the contents of the sort buffer:
<sort_key, rowid>: sort using row pointers
<sort_key, additional_fields>: sort using additional fields
<sort_key, packed_additional_fields>: sort using packed additional fi
elds
       For additional information about the filesort algorithm, see
       ORDER BY Optimization
       (http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.
       html). For information about the optimizer trace, see MySQL
       Internals: Tracing the Optimizer
       (http://dev.mysql.com/doc/internals/en/optimizer-tracing.html)
       .

   Packaging Notes

     * Previously, MySQL Server distributions included the MySQL
       Reference Manual in Info format (the Docs/mysql.info file).
       Because the license for the manual restricts redistribution,
       its inclusion in Community packages caused problems for
       downstream redistributors, such as those who create Linux
       distributions. Community distributions of MySQL Server no
       longer include the mysql.info file, to make the repackaging
       and redistribution process easier (for example, the source
       tarball and its checksum can be used directly). This change
       applies to all source and binary Community packaging formats.
       Commercial (Enterprise) distributions are unchanged.
       For those who wish to continue using the MySQL Reference Manual
       in Info format, we have made it available at
       http://dev.mysql.com/doc/.

   Performance Schema Notes

     * The Performance Schema now exposes metadata lock information:

          + Locks that have been granted (shows which sessions own
            which current metadata locks)

          + Locks that have been requested but not yet granted (shows
            which sessions are waiting for which metadata locks).

          + Lock requests that have been killed by the deadlock
            detector or timed out and are waiting for the requesting
            session's lock request to be discarded
       This information enables you to understand metadata lock
       dependencies between sessions. You can see not only which lock
       a session is waiting for, but which session currently holds
       that lock.
       The Performance Schema now also exposes table lock information
       that shows which table handles the server has open, how they
       are locked, and by which sessions.
       These specific changes were implemented:

          + The metadata_locks and table_handles tables list current
            locks and lock requests for metadata locks and table
            locks.

          + The setup_instruments table now has a
            wait/lock/metadata/sql/mdl instrument for metadata locks.
            This instrument is disabled by default.

          + The performance_schema_max_metadata_locks system variable
            configures the maximum number of metadata locks tracked
            in the metadata_locks table. For table_handles, the size
            is configured by the existing
            performance_schema_max_table_handles system variable.

          + The Performance_schema_metadata_lock_lost status variable
            indicates the number of times a metadata lock could not
            be recorded. For table_handles, tables that are opened
            but cannot be instrumented are counted by the existing
            Performance_schema_table_handles_lost status variable.
       For more information, see Performance Schema Lock Tables
       (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-loc
       k-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 transactions. The
       information collected includes quantitative and qualitative
       data including transaction duration, transaction counts, and
       frequency of various transaction attributes such as isolation
       level and access modes. This information is collected in
       tables that contain current and recent transaction events, and
       is aggregated in summary tables across several dimensions,
       including user, account, and thread (client connection).
       These new tables store transaction events:

          + events_transactions_current: Current transaction events

          + events_transactions_history: The most recent transaction
            events for each thread

          + events_transactions_history_long: The most recent
            transaction events overall
       There are also summary tables that provide aggregated
       transaction information.
       Within the event hierarchy, wait events nest within stage
       events, which nest within statement events, which nest within
       transactions. To reflect this, the NESTING_EVENT_TYPE column,
       in those tables that have it, permits a new value,
       TRANSACTION, in addition to the existing values STATEMENT,
       STAGE, and WAIT.
       To permit control over configuration of transaction event
       collection, these changes were made to Performance Schema
       setup tables:

          + The setup_instruments table contains a new instrument
            named transaction. This instrument is disabled by
            default.

          + The setup_consumers table contains new consumer values
            with names corresponding to the current and recent
            transaction event table names. These consumers may be
            used to filter collection of transaction events. Only
            events_transactions_current is enabled by default.

          + The setup_timers table contains a new row with a NAME
            value of transaction that indicates the unit for
            transaction event timing. The default unit is NANOSECOND.
       For more information, see Performance Schema Transaction
       Tables
       (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-tra
       nsaction-tables.html), and Transaction Summary Tables
       (http://dev.mysql.com/doc/refman/5.7/en/transaction-summary-ta
       bles.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.

   Security Notes

     * Incompatible Change: Previously, the --ssl option has been
       treated as advisory: When given, an SSL connection was
       permitted but not required. Also, several other --ssl-xxx
       options implied --ssl. Because of this, the option was usually
       not used explicitly as --ssl, but in its negated form as
       --ssl=0, which prevents use of SSL. This was true on both the
       client and server sides, and true for any synonyms of --ssl
       (--ssl=1, --enable-ssl) or --ssl=0 (--skip-ssl,
       --disable-ssl).
       Now the meaning of --ssl has changed on the client-side only.
       (There are no SSL changes on the server side.)
       When given on the client side as --ssl (or a synonym), the
       option now is prescriptive, not advisory: The client
       connection must use SSL or the connection attempt fails. In
       addition, other SSL options no longer imply --ssl. This is an
       incompatible change in the sense that MySQL client commands
       that use --ssl now will fail unless an SSL connection can be
       established. On the other hand, for a successful connection
       attempt, the connection is guaranteed to use SSL. Previously,
       there was no such guarantee.
       There is no change in the meaning of --ssl=0 (and its
       synonyms) to prevent use of SSL and override other SSL
       options.
       For more information, see SSL Command Options
       (http://dev.mysql.com/doc/refman/5.7/en/ssl-options.html).

   Functionality Added or Changed

     * Performance; InnoDB: The log_write_up_to function, which
       writes to redo log files up to a certain log sequence number
       (LSN) and optionally flushes writes to disk, has been
       refactored to improve performance for workloads with heavy
       log_sys::mutex contention and where
       innodb_flush_log_at_trx_commit=2.

     * Performance: The LOCK_thread_count mutex protected several
       independent internal server structures and variables, and was
       a bottleneck, particularly affecting server performance in the
       circumstance when many clients were connecting and
       disconnecting at once. This mutex was decomposed into more
       specific mutexes and atomic operations to alleviate the
       bottleneck and improve performance.
       As part of this work, the following status variables are no
       longer visible in the embedded server because for that server
       they were not updated and were not meaningful:
       Aborted_connects, Connection_errors_accept,
       Connection_errors_internal, Connection_errors_max_connections,
       Connection_errors_peer_addr, Connection_errors_select,
       Connection_errors_tcpwrap.

     * Incompatible Change: Several statement instruments in the
       setup_instruments table are used by the Performance Schema
       during the early stages of statement classification before the
       exact statement type is known. These instruments were renamed
       to more clearly reflect their "abstract" nature:

         Old Instrument Name        New Instrument Name
       statement/com/          statement/abstract/new_packet
       statement/com/Query     statement/abstract/Query
       statement/rpl/relay_log statement/abstract/relay_log
       In addition, statistics for abstract instruments are no longer
       collected in the following tables, because no such instrument
       is ever used as the final classification for a statement:
events_statements_summary_by_thread_by_event_name
events_statements_summary_by_account_by_event_name
events_statements_summary_by_user_by_event_name
events_statements_summary_by_host_by_event_name
events_statements_summary_global_by_event_name
       Applications that refer to the old instrument names must be
       updated with the new names. For more information about the use
       of abstract instruments in statement classification, see
       Performance Schema Statement Event Tables
       (http://dev.mysql.com/doc/refman/5.7/en/performance-schema-sta
       tement-tables.html). (Bug #16750433, Bug #17271055)

     * Incompatible Change: The EXPLAIN statement has been changed so
       that the effects of the EXTENDED and PARTITIONS keywords are
       always enabled. EXTENDED and PARTITIONS are still recognized,
       but are superfluous and have been deprecated. They will be
       removed from EXPLAIN syntax in a future MySQL release.
       EXPLAIN output differs as follows as a result of this change:

          + The filtered and partitions columns appear in EXPLAIN
            output regardless of whether the EXTENDED and PARTITIONS
            keywords are specified. This is an incompatible change
            for applications that expect to identify column
            information by position rather than by name, and such
            applications will need adjustment.

          + SHOW WARNINGS immediately following EXPLAIN shows
            additional execution plan information regardless of
            whether the EXTENDED keyword is specified. (An additional
            deprecation warning is included if the statement includes
            the EXTENDED or PARTITIONS keyword.)

     * Important Change; InnoDB: InnoDB now supports external
       full-text parser plugins. In order to support InnoDB full-text
       parser plugins that are called in boolean mode, a new
       "position" member has been added to the
       MYSQL_FTPARSER__BOOLEAN_INFO structure. If you plan to use an
       existing full-text parser plugin that is called in boolean
       mode with MySQL 5.7.3 or later, you must add support for the
       new "position" member, which is described in Writing Full-Text
       Parser Plugins
       (http://dev.mysql.com/doc/refman/5.7/en/writing-full-text-plug
       ins.html). Altering a MyISAM table with a full-text parser
       plugin to use InnoDB is also supported. For additional
       information about full-text parser plugins, see Full-Text
       Parser Plugins
       (http://dev.mysql.com/doc/refman/5.7/en/full-text-plugins.html
       ).

     * InnoDB: The InnoDB memcached plugin now supports inserts and
       reads on mapped InnoDB tables that have an INTEGER defined as
       the primary key. (Bug #17315083, Bug #17203937)

     * Replication: Replication filtering rules can now be set
       dynamically on the slave using the SQL statement CHANGE
       REPLICATION FILTER introduced in this release. This statement
       has the same effect as starting the slave mysqld with one or
       more of the options --replicate-do-db, --replicate-ignore-db,
       --replicate-do-table, --replicate-ignore-table,
       --replicate-wild-do-table, --replicate-wild-ignore-table, and
       --replicate-rewrite-db.
       For example, issuing the statement CHANGE REPLICATION FILTER
       REPLICATE_DO_TABLE = (d1.t2) is equivalent to starting the
       slave mysqld with --replicate-do-table='d1.t2'.
       CHANGE REPLICATION FILTER differs from the server options in
       that, to take effect, the statement requires only that the
       slave SQL thread be stopped beforehand and restarted
       afterwards, using STOP SLAVE SQL_THREAD and START SLAVE
       SQL_THREAD, respectively.
       This statement leaves any existing replication filtering rules
       unchanged; to unset all filters of a given type, set the
       filter to an empty list, as shown in this example:
CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();
       You can list multiple replication filtering rules in the same
       statement, separated by commas. When multiple instances of the
       same rule are found, only the last instance is used.
       For more information, see CHANGE REPLICATION FILTER Syntax
       (http://dev.mysql.com/doc/refman/5.7/en/change-replication-fil
       ter.html); see also How Servers Evaluate Replication Filtering
       Rules
       (http://dev.mysql.com/doc/refman/5.7/en/replication-rules.html
       ). (Bug #15877941, Bug #11752237, Bug #67362, Bug #43366)

     * Replication: Previously, with semisynchronous replication
       enabled, the master waited for a single slave acknowledgment
       per transaction before proceeding. A new system variable,
       rpl_semi_sync_master_wait_for_slave_count, enables the number
       of slave acknowledgments required per transaction to be
       configured. The minimum (and default) value is 1. The maximum
       is 65,536. Performance is best for small values of this
       variable.

     * Overhead for Performance Schema instrumentation associated
       with thread creation was reduced. (Bug #17539520)

     * The Performance Schema now instruments the read/write lock
       Delegate::lock, which is used for the following classes:
Trans_delegate
Binlog_storage_delegate
Binlog_transmit_delegate
Binlog_relay_IO_delegate
       A different instrument name is used for each subclass, to have
       distinct statistics for distinct uses. The instruments are
       visible in the schema.setup_instruments table and have these
       names:
wait/synch/rwlock/sql/Trans_delegate::lock
wait/synch/rwlock/sql/Binlog_storage_delegate::lock
wait/synch/rwlock/sql/Binlog_transmit_delegate::lock
wait/synch/rwlock/sql/Binlog_relay_IO_delegate::lock
       (Bug #17590161, Bug #70577)

     * Some dependencies between client-side plugin header files were
       removed:

          + The MYSQL_PLUGIN_EXPORT macro required by plugin
            declarations is now declared directly in
            mysql/client_plugin.h instead of getting the definition
            from mysql/plugin.h. That macro was the only thing
            required by client-side plugins and declared in
            server-side header mysql/plugin.h, so including
            mysql/client_plugin.h in an application no longer
            requires the application to also include mysql/plugin.h.

          + mysql/plugin_trace.h no longer uses C_MODE_START or
            C_MODE_END, so my_global.h. including
            mysql/plugin_trace.h in an application no longer requires
            the application to also include my_global.h.
       Applications might require mysql/plugin.h or my_global.h for
       other reasons, of course. (Bug #17582168)

     * It is now possible to enable the Performance Schema but
       exclude certain parts of the instrumentation. For example, to
       enable the Performance Schema but exclude stage and statement
       instrumentation, do this:
shell> cmake . -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
        -DDISABLE_PSI_STAGE=1 \
        -DDISABLE_PSI_STATEMENT=1
       For more information, see the descriptions of the
       DISABLE_PSI_XXX CMake options in MySQL Source-Configuration
       Options
       (http://dev.mysql.com/doc/refman/5.7/en/source-configuration-o
       ptions.html). (Bug #17478068)

     * A new CMake option, WITH_ASAN, permits enabling address
       sanitization for compilers that support it. (Bug #17435338)

     * Several compilation warnings were fixed that occurred when
       compiling without debugging enabled. (Bug #17332094)

     * The implementation of condition variables specific to Windows
       XP and Windows Server 2003 was removed from the source code
       because MySQL is not supported on those platforms as of MySQL
       5.6. (Bug #17332056)

     * A new ER_ENGINE_OUT_OF_MEMORY error code is available for use
       by storage engines to report out-of-memory conditions. (Bug
       #16807964)

     * For GRANT statements, ER_SP_DOES_NOT_EXIST errors for
       nonexistent stored procedures and functions now specify
       PROCEDURE does not exist or FUNCTION does not exist rather
       than the less-specific PROCEDURE or FUNCTION does not exist.
       (Bug #69628, Bug #17036976)

     * The hash function used for metadata locking was modified to
       reduce overhead. (Bug #68487, Bug #16396598)

     * Overhead for deprecation warnings was reduced. (Bug #70402,
       Bug #17497869)

     * The optimizer now is able to apply the range scan access
       method to queries of this form:
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd
' ));
       Previously, for range scans to be used it was necessary for
       the query to be written as:
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );
       For the optimizer to use a range scan, queries must satisfy
       these conditions:

          + Only IN predicates can be used, not NOT IN.

          + There may only be column references in the row
            constructor on the IN predicate's left hand side.

          + There must be more than one row constructor on the IN
            predicate's right hand side.

          + Row constructors on the IN predicate's right hand side
            must contain only runtime constants, which are either
            literals or local column references that are bound to
            constants during execution.
       EXPLAIN output for applicable queries will change from full
       table or index scan to range scan. Changes are also visible by
       checking the values of the Handler_read_first,
       Handler_read_key, and Handler_read_next status variables.

     * When a connection is returned to the thread pool plugin, the
       connection thread context must be cleaned up. Previously, this
       was done using COM_CHANGE_USER (which is like the
       mysql_change_user() C API function). However, that operation
       reauthenticates, which is unnecessary network roundtrip
       overhead in this context.
       Now it is possible for client connection state to be reset in
       a more lightweight manner without causing reauthentication.
       The API is exposed publicly through these changes:

          + A new COM_RESET_CONNECTION protocol command (defined in
            mysql_com.h)

          + A new mysql_reset_connection() C API function

          + A new resetconnection command for the mysql client
       Resetting a connection has effects similar to
       mysql_change_user() or an auto-reconnect except that the
       connection is not closed and reopened, and reauthentication is
       not done. See mysql_change_user()
       (http://dev.mysql.com/doc/refman/5.7/en/mysql-change-user.html
       )) and see Controlling Automatic Reconnection Behavior
       (http://dev.mysql.com/doc/refman/5.7/en/auto-reconnect.html)).
       For more information, see mysql_reset_connection()
       (http://dev.mysql.com/doc/refman/5.7/en/mysql-reset-connection
       .html) and mysql --- The MySQL Command-Line Tool
       (http://dev.mysql.com/doc/refman/5.7/en/mysql.html).