Web lists-archives.com

MySQL Community Server 8.0.0-dmr has been released (part 2/3)

[This is part 2 of the announcement]

   Performance Schema Notes

     * Incompatible Change: The Performance Schema now
       instruments server errors (and warnings), and exposes
       statistical information about them through a set of
       summary tables:

          + The error instrument controls whether error
            information is collected (enabled by default).

          + Several tables contain error information, summarized
            in various ways:

          + The performance_schema_error_size system variable
            controls the number of instrumented errors.
       For more information, see Performance Schema Error
       Summary Tables
       If you upgrade to this MySQL release from an earlier
       version, you must run mysql_upgrade (and restart the
       server) to incorporate these changes into the
       performance_schema database.
       In consequence of the preceding changes, two server error
       symbols were renamed:
       ING_TX and
       Also, several server error codes were found to be no
       longer used in the server and have been removed as
       obsolete. Applications that test specifically for any of
       these errors should be updated:

     * Previously, the DIGEST and DIGEST_TEXT columns in the
       Performance Schema events_statements_current table were
       populated only after statement execution ended. Now, the
       columns are populated just after parsing and before
       statement execution begins. This enables monitoring
       applications to access statement digest information during
       statement execution. (Bug #23336542)

     * Performance Schema tables now include indexes, which
       gives the optimizer access to execution plans other than
       full table scans and makes queries more efficient.
       Performance Schema table indexes are predefined and
       cannot be dropped, added, or altered. They are like hash
       indexes and do not provide row ordering.

     * The size of the ROLE column of the setup_actors
       Performance Schema table was increased from 16 to 32

   Security Notes

     * The validate_password_check_user_name system variable is
       now enabled by default rather than disabled. This means
       that when the validate_password plugin is enabled, by
       default it now rejects passwords that match the current
       session user name.

     * The client-side --ssl and --ssl-verify-server-cert
       options have been removed. Use --ssl-mode=REQUIRED
       instead of --ssl=1 or --enable-ssl. Use
       --ssl-mode=DISABLED instead of --ssl=0, --skip-ssl, or
       --disable-ssl. Use --ssl-mode=VERIFY_IDENTITY instead of
       --ssl-verify-server-cert options. (The server-side --ssl
       option remains unchanged.)
       For the C API, MYSQL_OPT_SSL_ENFORCE and
       mysql_options() correspond to the client-side --ssl and
       --ssl-verify-server-cert options and have been removed.
       Use MYSQL_OPT_SSL_MODE with an option value of

   Spatial Data Support

     * Spatial functions for import and export of Well-Known
       Text (WKT) values used MySQL 'GEOMETRYCOLLECTION()'
       nonstandard syntax rather than OpenGIS
       'GEOMETRYCOLLECTION EMPTY' standard syntax. Now both
       syntaxes are understood for import and the standard
       syntax is used for export. (Bug #23632147, Bug #81964)

     * The required version of the Boost library for server
       builds has been raised from 1.59.0 to 1.60.0. (Bug
       #79380, Bug #22253921)

     * The ST_X() and ST_Y() spatial functions now permit an
       optional second argument that specifies an X or Y
       coordinate value, respectively. With two arguments, the
       function result is the point value from the first
       argument with the appropriate coordinate modified. In
       addition, ST_X() and ST_Y() with a single argument now
       are stricter and produce an ER_UNEXPECTED_GEOMETRY_TYPE
       error rather than returning NULL if the argument is a
       valid geometry but not a point. For more information, see
       Point Property Functions

     * The ST_SRID() spatial function now permits an optional
       second argument that specifies a SRID value. With two
       arguments, the function result is the geometry value from
       the first argument with its SRID modified according to
       the second argument. For more information, see General
       Geometry Property Functions

     * MySQL now supports spatial reference systems other than
       SRID 0. (Specifically, georeferenced 2D projections.)
       Information about supported reference systems is stored
       in the st_spatial_reference_systems data dictionary

     * In MySQL 5.7, several spatial functions available under
       multiple names were deprecated to move in the direction
       of making the spatial function namespace more consistent,
       the goal being that each spatial function name begin with
       ST_ if it performs an exact operation, or with MBR if it
       performs an operation based on minimum bounding
       rectangles. The deprecated functions have now been
       removed to leave only the corresponding ST_ and MBR

          + These functions are removed in favor of the MBR
            names: Contains(), Disjoint(), Equals(),
            Intersects(), Overlaps(), Within().

          + These functions are removed in favor of the ST_
            names: Area(), AsBinary(), AsText(), AsWKB(),
            AsWKT(), Buffer(), Centroid(), ConvexHull(),
            Crosses(), Dimension(), Distance(), EndPoint(),
            Envelope(), ExteriorRing(), GeomCollFromText(),
            GeomCollFromWKB(), GeomFromText(), GeomFromWKB(),
            GeometryCollectionFromWKB(), GeometryFromText(),
            GeometryFromWKB(), GeometryN(), GeometryType(),
            InteriorRingN(), IsClosed(), IsEmpty(), IsSimple(),
            LineFromText(), LineFromWKB(), LineStringFromText(),
            LineStringFromWKB(), MLineFromText(),
            MLineFromWKB(), MPointFromText(), MPointFromWKB(),
            MPolyFromText(), MPolyFromWKB(),
            MultiLineStringFromText(), MultiLineStringFromWKB(),
            MultiPointFromText(), MultiPointFromWKB(),
            MultiPolygonFromText(), MultiPolygonFromWKB(),
            NumGeometries(), NumInteriorRings(), NumPoints(),
            PointFromText(), PointFromWKB(), PointN(),
            PolyFromText(), PolyFromWKB(), PolygonFromText(),
            PolygonFromWKB(), SRID(), StartPoint(), Touches(),
            X(), Y().

          + GLength() is removed in favor of ST_Length().

   Test Suite Notes

     * mysql-test-run.pl now supports a --do-suite option, which
       is similar to --do-test but permits specifying entire
       suites of tests to run. (Bug #24350345)

     * The mysqltest rmdir command fails if the directory to be
       removed contains any files or directories. To enable
       recursive removal of a directory as well as its contents,
       if any, mysqltest now supports a force-rmdir command.
       (Bug #24316799)

     * Two new test suite options make it easier to debug test

          + mysql-test-run.pl supports a --mysqltest=options
            option that enables options to be passed to

          + mysqltest supports a --trace-exec option that causes
            it to immediately print output from executed
            programs to stdout.
       mysql-test-run.pl now recognizes the MTR_CTEST_TIMEOUT
       environment variable. If set, the value is a timeout in
       seconds to pass to ctest unit test commands. (Bug
       #21821049, Bug #21278845)

     * For test cases in the MySQL test suite, it was previously
       possible to use symbolic error names for the --error
       command only for server errors. This is now also possible
       for client errors. For example:

       (Bug #21048973)

     * The mysqltest program now has a copy_files_wildcard
       command that copies all files that match a pattern from a
       source directory to a destination directory. See
       mysqltest Commands
       (Bug #82111, Bug #23743035)

   Functionality Added or Changed

     * Incompatible Change; Partitioning: The generic
       partitioning handler has been removed from the MySQL
       server. As part of this change, mysqld no longer supports
       the --partition and --skip-partition options, and the
       server can no longer be built using
       -DWITH_PARTITION_STORAGE_ENGINE. partition is also no
       longer displayed in the output of SHOW PLUGINS, or shown
       In order to support partitioning of a given table, the
       storage engine used for the table must now provide its
       own ("native") partitioning handler. InnoDB is the only
       storage engine supported in MySQL 8.0 which includes a
       native partitioning handler. An attempt to create
       partitioned tables in MySQL 8.0 using any other storage
       engine fails. (The NDB storage engine used by MySQL
       Cluster also provides its own partitioning handler, but
       is currently not supported by MySQL 8.0.)
       Affects on upgrades.  The direct upgrade of a partitioned
       table using a storage engine other than InnoDB (such as
       MyISAM) from MySQL 5.7 (or earlier) to MySQL 8.0 is not
       supported. There are two options for upgrading such a
       table to be compatible with MySQL 8.0, listed here:

          + Remove the table's partitioning; you can do this
            without any data loss by executing an ALTER TABLE
            ... REMOVE PARTITIONING statement.

          + Change the storage engine used for the table to
            InnoDB, using ALTER TABLE ... ENGINE=INNODB; this
            leaves the table's partitioning in place. At least
            one of these operations must be performed for any
            partitioned non-InnoDB table, prior to upgrading the
            server to MySQL 8.0. Otherwise, such a table cannot
            be used following the upgrade.
       An analogous situation is met when importing databases
       from a dump file that was created in MySQL 5.7 or earlier
       using mysqldump into a MySQL 8.0 server, due to the fact
       that table creation statements that would result in a
       partitioned table using a storage engine without such
       support fail with an error in MySQL 8.0. For this reason
       you must ensure that any statements in the dump file
       creating partitioned tables do not also specify an
       unsupported storage engine. You can do this either by
       removing any references to partitioning from CREATE TABLE
       statements that use a value for the STORAGE ENGINE option
       other than InnoDB, or by specifying the storage engine as
       InnoDB (or allowing InnoDB to be used by default).
       For more information, see Partitioning Limitations
       Relating to Storage Engines

     * Important Change; InnoDB: The following InnoDB file
       format configuration parameters were deprecated in MySQL
       5.7.7 and are now removed:

          + innodb_file_format

          + innodb_file_format_check

          + innodb_file_format_max

          + innodb_large_prefix
       File format configuration parameters were necessary for
       creating tables compatible with earlier versions of
       InnoDB in MySQL 5.1. Now that MySQL 5.1 has reached the
       end of its product lifecycle, the parameters are no
       longer required.
       The FILE_FORMAT column was removed from the
       Schema tables.

     * InnoDB: The innodb_buffer_pool_debug option permits
       multiple buffer pool instances when the buffer pool is
       less than 1GB in size, ignoring the 1GB minimum buffer
       pool size constraint imposed on
       innodb_buffer_pool_instances. (Bug #24287290)

     * InnoDB: A new dynamic configuration option,
       innodb_deadlock_detect, can be used to disable deadlock
       detection. On high concurrency systems, deadlock
       detection can cause a slowdown when numerous threads wait
       for the same lock. At times, it may be more efficient to
       disable deadlock detection and rely on the
       innodb_lock_wait_timeout setting for transaction rollback
       when a deadlock occurs. (Bug #23477773)

     * InnoDB: The libinnodb_zipdecompress.a library allows
       external tools to use the page_zip_decompress_low()
       function to decompress InnoDB pages. (Bug #21405300, Bug

     * InnoDB: To address contention that could occur under some
       workloads, the buffer pool mutex was removed and replaced
       by several list and hash protecting mutexes. Also,
       several buffer pool related variables no longer require
       buffer pool mutex protection.
       Thanks to Yasufumi Kinoshita and Laurynas Biveinis for
       the patch. (Bug #20381905, Bug #75534)

     * InnoDB: InnoDB now avoids intermediate commits that would
       occur every 10000 rows during ALTER TABLE ALGORITHM=COPY
       operations. The purpose of intermediate commits was to
       speed up recovery in the case of an aborted ALTER TABLE
       ALGORITHM=COPY operation. If an ALTER TABLE
       ALGORITHM=COPY operation is aborted, the new, uncommitted
       table is now dropped during DDL log recovery before the
       undo log is rolled back, thereby avoiding time-consuming
       data rollback for the uncommitted table. Undo logging is
       now suppressed for ALTER TABLE ALGORITHM=COPY operations
       unless there is an IGNORE clause or something else that
       requires rollback capability.
       If there is full-text index on the table being altered,
       full-text data is inserted into full-text auxiliary
       tables as the ALTER TABLE ALGORITHM=COPY operation
       inserts rows into the new, uncommitted table. Previously,
       full-text data was only processed on transaction commit.
       (Bug #17479594)

     * InnoDB: To reduce read-write lock contention that can
       result from multiple purge threads purging rows from the
       same table, undo records are now grouped and assigned to
       different purge threads by table ID.

     * InnoDB: InnoDB code now uses the C++ std::thread library
       for thread management.

     * InnoDB: BLOB code was refactored to provide an internal
       C++ interface for operations on compressed and
       uncompressed BLOB data.

     * InnoDB: The InnoDB memcached plugin now supports range
       queries and fetching multiple key value pairs in a single
       memcached query.

     * InnoDB: The current maximum auto-increment counter value
       is now written to the redo log each time the value
       changes, and it is saved to an engine-private system
       table on each checkpoint. These changes make the current
       maximum auto-increment counter value persistent across
       server restarts. Additionally:

          + A server restart no longer cancels the effect of the
            AUTO_INCREMENT = N table option. If you initialize
            the auto-increment counter to a specific value, or
            if you alter the auto-increment counter value to a
            larger value, the new value is persisted across
            server restarts.

          + A server restart immediately following a ROLLBACK
            operation no longer results in the reuse of
            auto-increment values that were allocated to the
            rolled-back transaction.

          + If you modify an AUTO_INCREMENT column value to a
            value larger than the current maximum auto-increment
            value (in an UPDATE operation, for example), the new
            value is persisted, and subsequent INSERT operations
            allocate auto-increment values starting from the
            new, larger value.
       For more information, see AUTO_INCREMENT Handling in
       and InnoDB AUTO_INCREMENT Counter

     * InnoDB: The innodb_stats_sample_pages system variable was
       removed. innodb_stats_sample_pages was deprecated in
       MySQL 5.6.3 and replaced by

     * InnoDB: When encountering index tree corruption, InnoDB
       writes a corruption flag to the redo log, which makes the
       corruption flag crash-safe. InnoDB also writes in-memory
       corruption flag data to an engine-private system table on
       each checkpoint. During recovery, InnoDB reads corruption
       flags from both locations and merges results before
       marking in-memory table and index objects as corrupt.

     * InnoDB: The innodb_locks_unsafe_for_binlog system
       variable was removed.

     * InnoDB: InnoDB no longer creates .isl files (InnoDB
       Symbolic Link files) when creating tablespace data files
       outside of the MySQL data directory. Redo log records are
       now used to locate remote tablespaces.
       With this change, moving a remote tablespace while the
       server is offline by manually modifying an .isl file is
       no longer supported.

     * InnoDB: InnoDB no longer supports compressed temporary
       tables. When innodb_strict_mode is enabled (the default),
       CREATE TEMPORARY TABLE returns an error if
       innodb_strict_mode is disabled, warnings are issued and
       the temporary table is created using a non-compressed row
       With this change, all temporary tables are created in the
       shared temporary tablespace, ibtmp1.
       The PER_TABLE_TABLESPACE and IS_COMPRESSED columns were
       removed from the

     * InnoDB: A new INFORMATION_SCHEMA table,
       INNODB_CACHED_INDEXES, reports the number of index pages
       cached in the InnoDB buffer pool for each index.

     * InnoDB: The innodb_checksums system variable was removed.
       innodb_checksums was replaced by
       innodb_checksum_algorithm in MySQL 5.6.3.

     * InnoDB: InnoDB startup code was refactored.

     * InnoDB: The innodb_flush_method default value is no
       longer NULL. On Unix-like systems, the default value is
       fsync. On Windows systems, the default value is
       On Windows, the innodb_flush_method setting no longer
       affects the innodb_use_native_aio setting. There are now
       two possible settings for innodb_flush_method on Windows,
       unbuffered (unbuffered I/O) and normal (buffered I/O).
       With this change, you can enable asynchronous I/O with
       buffered I/O, which is a new combination
       (innodb_use_native_aio=ON and
       innodb_flush_method=normal). The async_unbuffered setting
       was removed.
       You can now set innodb_flush_method and
       innodb_change_buffering configuration options using
       numeric values.

     * InnoDB: The innodb_support_xa system variable, which
       enables support for two-phase commit in XA transactions,
       was removed. As of MySQL 5.7.10, InnoDB support for
       two-phase commit in XA transactions is always enabled.

       table reports the number of index pages cached in the
       InnoDB buffer pool for each index.

     * InnoDB: InnoDB no longer creates an .isl file (InnoDB
       Symbolic Link files) when creating a file-per-table
       tablespace data file outside of the MySQL data directory.
       InnoDB now uses the redo log to locate remote tablespace
       data files.
       Offline relocation of a file-per-table tablespace data
       file created outside of the MySQL data directory by
       modifying the .isl file is no longer supported.

     * Replication: There are two improvements to how a CHANGE
       MASTER TO
       statement is written into the error log

          + Before, no commas were put between the option
            specifications (for example MASTER_USER = and
            MASTER_PASSWORD = ), so users who wanted to use the
            statement by copy and paste had to insert the commas
            manually. Commas are now inserted when the statement
            is written to the error log.

          + When the literal "<secret>" is inserted as a
            placeholder for the MASTER_PASSWORD value, no quotes
            are used now, so users who forget to replace the
            literal with the real password before a copy and
            paste gets a syntax error immediately, instead of
            running into other issues.
       (Bug #18194384)

     * Replication: It is now possible to restore a backup of a
       GTID-based replication server because you can add GTIDs
       to gtid_purged, regardless of whether gtid_executed is
       empty or not. This enables you to restore backups from
       GTID-based replication servers without losing existing
       GTID information and binary logs. The GTIDs to add are
       those which existed in gtid_executed at the time of
       taking the backup. The syntax for SET GTID_PURGED has
       been extended to enable this ability, for example SET
       GTID_PURGED ="+gtid_set" adds gtid_set to the existing
       gtid_purged GTID set.

     * To produce more accurate estimates, the MEMORY storage
       engine now calculates index statistics (records per key
       estimates) using floating-point rather than integer
       arithmetic. (Bug #23024059)

     * A new CMake option, INSTALL_STATIC_LIBRARIES, enables
       control over whether to install static libraries. The
       default is ON. If set to OFF, these libraries are not
       installed: libmysqlclient.a, libmysqld.a,
       libmysqlservices.a. (Bug #22891432)

     * The internal mysql_prepare_create_table() server function
       has been refactored for improved code maintainability and
       clarity. This code revision results in the following
       minor changes of behavior for CREATE TABLE and ALTER

          + Attempts to create a second primary key based on a
            NULL column now produce an ER_MULTIPLE_PRI_KEY error
            rather than ER_PRIMARY_CANT_HAVE_NULL.

          + Attempts to create a second primary key based on a
            generated column now produce an ER_MULTIPLE_PRI_KEY
            error rather than

          + Attempts to create a full-text key on a JSON column
            now produce an ER_JSON_USED_AS_KEY error rather than

          + Attempts to create a key in a storage engine that
            does not support keys (for example, EXAMPLE) now
            produce an ER_TOO_MANY_KEYS error rather than
       (Bug #22884886)

     * Previously, the Performance Schema was not built for
       libmysqld, the embedded server. This prevented use of the
       SHOW STATUS and SHOW VARIABLES statements with
       show_compatibility_56=OFF because, with that setting,
       those statements take their results from Performance
       Schema tables. Now for libmysqld, the required
       Performance Schema tables are built (with no
       instrumentation collected), so that those SHOW statements
       can be supported with show_compatibility_56=OFF. (Bug

     * Several internal functions used by JSON_CONTAINS(),
       JSON_SEARCH(), and other MySQL JSON functions created
       excessive numbers of local copies of keys, values, or
       both, when performing inspections of JSON objects. Such
       copying has been eliminated or reduced in many cases. In
       addition, the lifetimes of temporary objects used by some
       of these functions have been reduced. These changes
       should make these and related JSON functions perform more
       efficiently than previously, and with fewer resources
       required. (Bug #22602142)

     * If the system lz4 and openssl zlib commands are
       available, the lz4_decompress and zlib_decompress
       utilities are unneeded. Two changes enable those
       utilities not to be built: If the new WITH_LZ4 CMake
       option is set to system, lz4_decompress is not built or
       installed. If the WITH_ZLIB CMake option is set to
       system, zlib_decompress is not built or installed. (Bug

     * Source files for the MySQL strings library have been
       converted from C (.c suffix) to C++ (.cc suffix). This
       enables stricter compilation checks and use of C++
       features in the library code. (Bug #22124719)

     * Source code for the mysys library now uses C++ rather
       than C to take advantage of stricter compilation checks
       and permit use of C++ features. (Bug #21881278)

     * A new CMake option, WITH_TSAN, permits enabling
       ThreadSanitizer for compilers that support it. (Bug
       #80409, Bug #23171902)

     * This release adds an unquoting extraction operator ->>,
       sometimes also referred to as an inline path operator,
       for use with JSON documents stored in MySQL. The new
       operator is similar to the -> operator, but performs JSON
       unquoting of the value as well. For a JSON column mycol
       and JSON path expression mypath, the following three
       expressions are equivalent:

          + JSON_UNQUOTE( JSON_EXTRACT(mycol, "$.mypath") )

          + JSON_UNQUOTE(mycol->"$.mypath")

          + mycol->>"$.mypath"
       The ->> operator can be used in SQL statements wherever
       JSON_UNQUOTE(JSON_EXTRACT()) would be allowed. This
       includes (but is not limited to) SELECT lists, WHERE and
       HAVING clauses, and ORDER BY and GROUP BY clauses.
       For more information, see Functions That Search JSON
       and JSON Path Syntax
       (Bug #78736, Bug #21980346)

     * The global list of connections, previously protected by a
       single mutex, has been partitioned into eight parts, each
       protected by its own instance of the mutex. The result is
       a reduction of overhead and improved performance for
       connection processing. An implication of this change for
       monitoring purposes is that the Performance Schema now
       exposes eight different instances each of the
       LOCK_thd_list mutex, LOCK_thd_remove mutex, and
       COND_thd_list condition variable.

     * MySQL now provides functions to manipulate UUID values
       and make them easier to work with:

          + UUID_TO_BIN() and BIN_TO_UUID() convert between UUID
            values in string and binary formats (represented as
            hexadecimal characters and VARBINARY(16),
            respectively). This permits conversion of string
            UUID values to binary values that take less storage
            space. UUID values converted to binary can be
            represented in a way that permits improved indexing

          + IS_UUID() returns 1 or 0 to indicate whether its
            argument is a valid string-format UUID value.
       For more information about these functions, see
       Miscellaneous Functions

     * The mysql_plugin utility has been removed. Alternatives
       include loading plugins at server startup using the
       --plugin-load or --plugin-load-add option, or at runtime
       using the INSTALL PLUGIN statement.

     * The server now relies on storage engines to clean up
       temporary tables left from previous server runs. InnoDB
       does this by discarding the temporary tablespace on
       restart. MyISAM and other similar storage engines still
       rely on scanning the temporary directory to detect
       leftover tables, by looking for files belonging to these
       engines with a certain name pattern.

     * The deprecated mysql_shutdown() C API function and
       corresponding COM_SHUTDOWN client/server protocol command
       have been removed. Instead, use mysql_query() to execute
       a SHUTDOWN statement.

     * Work was done to clean up the source code base,
       including: Removal of unneeded CMake checks; removing
       unused macros from source files; reorganizing header
       files to reduce the number of dependencies and make them
       more modular, removal of function declarations without

     * The server no longer performs conversion of pre-MySQL 5.1
       database names containing special characters to 5.1
       format with the addition of a #mysql50# prefix. Because
       these conversions are no longer performed, the
       --fix-db-names and --fix-table-names options for
       mysqlcheck, the UPGRADE DATA DIRECTORY NAME clause for
       the ALTER DATABASE statement, and the
       Com_alter_db_upgrade status variable have been removed.
       Upgrades are supported only from one major version to
       another (for example, 5.0 to 5.1, or 5.1 to 5.5), so
       there should be little remaining need for conversion of
       older 5.0 database names to current versions of MySQL. As
       a workaround, upgrade a MySQL 5.0 installation to MySQL
       5.1 before upgrading to a more recent release.


MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql