1.4 What Is New in MySQL 9.3

This section summarizes what has been added to, deprecated in, changed, and removed from MySQL 9.3 since MySQL 9.2. A companion section lists MySQL server options and variables that have been added, deprecated, or removed in MySQL 9.3; see Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 9.3”.

• Features Added or Changed in MySQL 9.3

• Features Deprecated in MySQL 9.3

• Features Removed in MySQL 9.3

Features Added or Changed in MySQL 9.3

The following features have been added to MySQL 9.3:

• Logical dumps of user accounts with mysqldump.  mysqldump can now provide logical dumps of information about desired user accounts, in the form of CREATE USER and GRANT SQL statements. This is enabled using the --users command line option. To cause the CREATE USER statements to be preceded by DROP USER, include the --add-drop-user option as well.

  By itself, the --users option causes mysqldump to include information about all user accounts in the dump. You can cause mysqldump to be more selective by specifying either of the following two additional options:

  • --include-user=user@host: Include information for the user@host account only. Use the option multiple times to include multiple user accounts.

  • --exclude-user=user@host: Do not include information for the user@host account. Use the option multiple times to exclude multiple user accounts.

  All of the options mentioned here which relate to dumping of user accounts were added in MySQL 9.3.0.

  For more information and examples, see Filtering Options, as well as Invocation Syntax, in Section 6.5.4, “mysqldump — A Database Backup Program”.

• MLE interface for clearing stderr and stdout.  MLE, available as part of MySQL Enterprise Edition, augments the existing built-in mle_session_reset() function with an optional string argument for which it accepts the values "stderr", "stdout", or "output" to clear stderr, stdout, or both, respectively.

  If no argument is supplied, mle_session_reset() clears both stderr and stdout, as if it had been called with "output", and in addition clears the stack trace and resets the session time zone, removing any observable output from mle_session_state(); this is exactly the same behavior as it had in versions of MySQL prior to 9.3.0.

  See Section 27.3.5, “JavaScript Stored Programs—Session Information and Options”, for more information.

• DECIMAL type support in JavaScript programs.  The MySQL DECIMAL type (including its alias NUMERIC) is fully supported by JavaScript stored programs in MySQL 9.3.0 and later. Such values can now be used with JavaScript programs as input arguments, output arguments, bind() parameters for prepared statements, and return values.

  To safeguard the precision of the original value, MySQL DECIMAL is converted to JavaScript String by default. You can override this behavior, and cause such a value to be converted to Number instead by calling SELECT mle_set_session_state('{"decimal_type":"Number"}'). It is also possible to use one of the Session methods setOptions(), sql(), runSql(), or prepare() to perform this task on the routine or statement level.

  JavaScript Boolean, Number, String, and BigInt values can be converted to DECIMAL (NUMERIC); an attempt to convert any other JavaScript type to a MySQL decimal type is rejected with an error.

  See Section 27.3.4, “JavaScript Stored Program Data Types and Argument Handling”, for more information and examples.

  JavaScript stored program support requires the Multilingual Engine Component (MLE), available with MySQL Enterprise Edition. See Section 7.5.7, “Multilingual Engine Component (MLE)”.

• JavaScript l11n and i18n support.  MySQL JavaScript stored programs using the Multilingual Engine Component (MLE), available with MySQL Enterprise Edition, now support MySQL locale settings using the JavaScript Internationalization API (Intl global object). You can obtain the name of the MySQL locale currently in effect within a given JavaScript stored program by checking the value of Intl.DateTimeFormat().resolvedOptions().locale.

  MySQL locale names map to JavaScript locale names by replacing underscores with dashes. For example, if you set lc_time_names to sv_SE, JavaScript reports the locale as sv-SE. Within a stored program, call an object's toLocaleString() method with a locale name (using dashes) to override the default locale.

  Unless overridden, the session locale which is in effect the first time a JavaScript stored program is executed during a given session remains in effect within that stored program until the session is reset, even if the session locale setting is changed. In such cases, call mle_session_reset() after updating lc_time_names to cause existing stored programs to use the new locale setting by default.

  See JavaScript Localization and Internationalization, for more information and examples.

• JavaScript library enhancements.  MySQL 9.3.0 makes a number of changes and additions relating to JavaScript library SQL:

  ALTER PROCEDURE and ALTER FUNCTION can now modify the list of libraries imported by a given JavaScript stored program, employing the USING clause already supported by CREATE PROCEDURE and CREATE FUNCTION.

  In addition, the following two SQL statements are added in this release:

  • ALTER LIBRARY can be used to add, change, or remove an SQL comment for the library.

  • SHOW LIBRARY STATUS provides basic information about one or more JavaScript libraries, similarly to SHOW FUNCTION STATUS and SHOW PROCEDURE STATUS. Like these two statements, SHOW LIBRARY STATUS supports LIKE and WHERE clauses for filtering of the results.

  MySQL also adds two status variables, Com_alter_library and Com_show_library_status, to provide counts of ALTER LIBRARY and SHOW LIBRARY STATUS statements, respectively.

  See the descriptions of the statements indicated for more information and examples. For general information about JavaScript stored programs, see Section 27.3, “JavaScript Stored Programs”. For more information about the Multilingual Engine component, a part of MySQL Enterprise Edition which provides JavaScript stored program support, see Section 7.5.7, “Multilingual Engine Component (MLE)”.

• JavaScript programs: dynamic library loading.  Dynamic import of JavaScript libraries is now supported using the await keyword. This means you can use constructs such as that shown here to insure that libraries are loaded before using them:

  Press CTRL+C to copy
   
  let module = await import(`/db1/lib_${object_type}`)
  
  return module.default.print()   // assume this method is defined for each lib

  See Section 27.3.8, “Using JavaScript Libraries”, for more information and examples.

• Group Replication Primary Election component.  This component works with the Group Replication plugin at failover to choose the secondary that is the most up to date as the new primary; that is, to promote the secondary that is the fewest transactions behind the old primary. In the event that no one secondary is the most up to date, the secondary from among these which has the greatest weight is used. This is also done if the component is not installed or available (group_replication_elect_prefers_most_updated.enabled = ON) on all members of the group.

  When a new primary is elected, the component records this in the log. This information includes a timestamp, the UUID of the promoted secondary, and the determining factor (most up to date, and how many transactions behind the primary, or weighting, and the weight assigned to the secondary chosen).

  This component also provides two status variables for monitoring purposes. Gr_latest_primary_election_by_most_uptodate_members_trx_delta was the difference in the number of transactions between the new primary and the secondary most up to date with it, when the most-up-to-date primary selection method was last used. Gr_latest_primary_election_by_most_uptodate_member_timestamp provides a timestamp for the most recent election of a new primary using the most-up-to-date selection method.

  For more information about this component, see Section 7.5.6.4, “Group Replication Primary Election Component”.

  This component is part of MySQL Enterprise Edition, a commercial offering. See Chapter 32, MySQL Enterprise Edition, for more information about MySQL Enterprise Edition.

• Transformation of quantified comparison predicates.  Previously, with the subquery_to_derived optimization enabled, MySQL supported transformation into an inner or outer join with a derived table corresponding to a subquery using one of the quantified comparison operations =ANY (equivalent to IN) or <>ALL (equivalent to NOT IN) in the WHERE clause of a query. MySQL 9.3.0 extends this functionality in two ways:

  1. All such comparisons are now supported. These include:

     • >ANY, >=ANY, <ANY, and <=ANY

     • >ALL, >=ALL, <ALL, and <=ALL.

     These are in addition to =ANY and <>ALL.

  2. Transformation to derived tables of such comparisons is now supported in the SELECT clause as well as in the WHERE clause.

  For more information, see Section 10.2.2.6, “Optimizing ANY and ALL Subqueries”.

• Optimizer support in Option Tracker.  The Option Tracker component, part of MySQL Enterprise Edition, now supports two MySQL features—the traditional MySQL Optimizer, and the MySQL Hypergraph Optimizer (MySQL HeatWave only).

  For more information, see Section 7.5.8.2, “Option Tracker Supported Components”.

Features Deprecated in MySQL 9.3

The following features are deprecated in MySQL 9.3 and may be removed in a future series. Where alternatives are shown, applications should be updated to use them.

For applications that use features deprecated in MySQL 9.3 that have been removed in a later MySQL version, statements may fail when replicated from a MySQL 9.3 source to a replica running a later version, or may have different effects on source and replica. To avoid such problems, applications that use features deprecated in 9.3 should be revised to avoid them and use alternatives when possible.

• title.  item

Features Removed in MySQL 9.3

The following items are obsolete and have been removed in MySQL 9.3. Where alternatives are shown, applications should be updated to use them.

For MySQL 9.2 applications that use features removed in MySQL 9.3, statements may fail when replicated from a MySQL 9.2 source to a MySQL 9.3 replica, or may have different effects on source and replica. To avoid such problems, applications that use features removed in MySQL 9.3 should be revised to avoid them and use alternatives when possible.

• Version Tokens plugin.  The Version Tokens plugin, which was deprecated in MySQL 9.2.0, has been removed in the 9.3.0 release.

• replica_parallel_workers changes.  As of the MySQL 9.3.0 release, the replica_parallel_workers system variable can no longer be set to 0; the minimum permitted value is now 1.