1.4 What Is New in MySQL 9.2

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

• Features Added or Changed in MySQL 9.2

• Features Deprecated in MySQL 9.2

• Features Removed in MySQL 9.2

Features Added or Changed in MySQL 9.2

The following features have been added to MySQL 9.2:

• CREATE_SPATIAL_REFERENCE_SYSTEM privilege.  MySQL 9.2.0 introduces the CREATE_SPATIAL_REFERENCE_SYSTEM privilege, which allows the user to execute any of the following statements:

  • CREATE SPATIAL REFERENCE SYSTEM

  • CREATE OR REPLACE SPATIAL REFERENCE SYSTEM

  • DROP SPATIAL REFERENCE SYSTEM

  Trying to execute any of the statements just listed without having this privilege (or the SUPER privilege) now raises the error ER_CMD_NEED_SUPER_OR_CREATE_SPATIAL_REFERENCE_SYSTEM. Use of the SUPER privilege for this purpose should be considered deprecated.

  See Section 8.2.2, “Privileges Provided by MySQL”, for more information.

• JavaScript libraries.  The MLE component (see Section 7.5.8, “Multilingual Engine Component (MLE)”) now supports reusable JavaScript libraries containing functions which can be called from other JavaScript stored programs. Such functions must be marked as importable using the export keyword. Libraries can be managed using the CREATE LIBRARY and DROP LIBRARY SQL statements added in MySQL 9.2.0; they can be included in other stored JavaScript programs with the USING clause added in the same release to CREATE FUNCTION and CREATE PROCEDURE; USING supports a list of one or more library names.

  The CREATE LIBRARY statement creates a new JavaScript library in a given database given the code for one or more JavaScript functions. JavaScript code is parsed and checked for validity at creation time; CREATE LIBRARY is rejected if the code contains any errors. DROP LIBRARY drops a given JavaScript library. Library functions can be referred to in other JavaScript stored programs using library_name.function_name notation. Libraries can be aliased as part of the USING clause with CREATE FUNCTION or CREATE PROCEDURE.

  You can obtain the code contained in an existing JavaScript library using the SHOW CREATE LIBRARY statement, also implemented in MySQL 9.2.0. Additional information about JavaScript libraries can be obtained from two Information Schema tables added in MySQL 9.2.0: The LIBRARIES table provides information about JavaScript libraries; the ROUTINE_LIBRARIES provides information about stored routines using JavaScript libraries.

  Counts of CREATE LIBRARY, DROP LIBRARY, and SHOW CREATE LIBRARY statements which have been issued on the server can be obtained as the status variables Com_create_library, Com_drop_library, and Com_show_create_library, respectively.

  For more information, see Section 27.3.8, “Using JavaScript Libraries”.

• SQL stored routine and session variable API for JavaScript.  The MLE Component in MySQL 9.2.0 and later supports access from JavaScript routines to user-defined functions, procedures, and variables.

  MySQL stored functions and procedures can now be accessed using the Schema methods getFunction() and getProcedure(). Each of these functions returns a Function object which can be invoked with arguments. A stored function argument or IN parameter of a stored procedure can be passed directly; an OUT or INOUT parameter of a stored procedure requires a placeholder (an Argument object) created using mysql.arg().

  In addition, MySQL user variables can now be accessed directly as properties of the JavaScript global Session object. See Accessing Session Variables from JavaScript, for more information and examples.

  The 9.2.0 release also adds support for direct access to several MySQL builtin functions, listed here:

  • rand(): Equivalent to MySQL RAND()

  • sleep(): Equivalent to MySQL SLEEP()

  • uuid(): Equivalent to MySQL UUID()

  • isUUID(): Equivalent to MySQL IS_UUID()

  All of these functions can be called as methods of the global Mysql object.

  For additional information, see Section 27.3.6.8, “Stored Routine API”, and Section 27.3.6.10, “MySQL Functions”, as well as Section 7.5.8, “Multilingual Engine Component (MLE)”.

• JavaScript Transactional API.  Beginning with MySQL 9.2.0, the MLE component provides a JavaScript MySQL transaction API which performs the actions of most MySQL transactional SQL statements, such as START TRANSACTION, COMMIT, ROLLBACK, and SET AUTOCOMMIT. Support for savepoints is also included.

  This work also implements an SqlError object.

  For more information, see Section 27.3.6.9, “JavaScript Transaction API”, and Section 27.3.6.11, “SqlError Object”.

• JavaScript ENUM and SET support.  The MySQL ENUM and SET types are supported for arguments of JavaScript stored routines in MySQL 9.2.0 and later.

  For more detailed information, including rules for conversion between these MySQL types and JavaScript types, see Conversion to and from MySQL ENUM and SET.

• EXPLAIN FORMAT=JSON version information.  MySQL 9.2.0 adds format version information to the output of EXPLAIN FORMAT=JSON when the JSON format version is set to 2.

  To set the format version, issue SET explain_json_format_version=2 or similar. You can then see the version information in the output like this:

  mysql> EXPLAIN FORMAT=JSON SELECT 1\G
  *************************** 1. row ***************************
  EXPLAIN: {
    "query": "/* select#1 */ select 1 AS `1`",
    "query_plan": {
      "operation": "Rows fetched before execution",
      "access_type": "rows_fetched_before_execution",
      "estimated_rows": 1.0,
      "estimated_total_cost": 0.0,
      "estimated_first_row_cost": 0.0
    },
    "query_type": "select",
    "json_schema_version": "2.0"
  }
  1 row in set (0.00 sec)

  The output of this statement does not contain any format version information when explain_json_format_version is 1, as shown here:

  mysql> SET explain_json_format_version=1;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> SELECT @@explain_json_format_version;
  +-------------------------------+
  | @@explain_json_format_version |
  +-------------------------------+
  |                             1 |
  +-------------------------------+
  1 row in set (0.00 sec)
  
  mysql> EXPLAIN FORMAT=JSON SELECT 1\G
  *************************** 1. row ***************************
  EXPLAIN: {
    "query_block": {
      "select_id": 1,
      "message": "No tables used"
    }
  }
  1 row in set, 1 warning (0.00 sec)

  For more information, see Obtaining Execution Plan Information, as well as the description of the explain_json_format_version system variable.

• Option Tracker replication support.  The MySQL Option Tracker component, available as part of MySQL Enterprise Edition, now provides information about the binary log, the Group replication plugin, and the use of the server as a replica.

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

Features Deprecated in MySQL 9.2

The following features are deprecated in MySQL 9.2 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.2 that have been removed in a later MySQL version, statements may fail when replicated from a MySQL 9.2 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.2 should be revised to avoid them and use alternatives when possible.

• FLUSH PRIVILEGES deprecated.  The FLUSH PRIVILEGES statement is deprecated as of MySQL 9.2.0, and causes a warning when issued. You should expect this statement to be removed in a future MySQL release.

  The following constructs are also deprecated in MySQL 9.2.0:

  • The FLUSH_PRIVILEGES privilege; granting this privilege causes a warning.

  • mysqladmin flush-privileges; issuing this command causes a warning.

  • mysqladmin reload; issuing this command causes a warning.

  In addition, the following features do not cause any warnings but should be considered deprecated:

  • Flushing of privileges by SIGHUP

  • Flushing of the caching_sha2 cache by FLUSH PRIVILEGES

  • Flushing of privileges by mysqladmin refresh

  For more information, see Section 15.7.8.3, “FLUSH Statement”.

• Version Tokens plugin.  The Version Tokens plugin is deprecated as of MySQL 9.2.0, and subject to removal in a future MySQL release. Attempting to install the version_tokens plugin, or to start the server when the plugin is installed, causes a deprecation warning to be issued. The following related features are also deprecated in MySQL 9.2.0, and raise deprecation warnings whenever they are invoked:

  • The functions listed here:

    • version_tokens_delete()

    • version_tokens_edit()

    • version_tokens_lock_exclusive()

    • version_tokens_lock_shared()

    • version_tokens_set()

    • version_tokens_show()

    • version_tokens_unlock()

  • Granting the VERSION_TOKEN_ADMIN privilege

  • The following server system variables:

    • version_tokens_session

    • version_tokens_session_number

Features Removed in MySQL 9.2

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

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

• BINLOG keyword now restricted.  The BINLOG keyword is now restricted, and can no longer be used unquoted as a label in a MySQL stored routine or stored function. You should update any affected applications accordingly, before upgrading to MySQL 9.2.

  See Section 11.3, “Keywords and Reserved Words”, for more information.