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”.
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:Trying to execute any of the statements just listed without having this privilege (or the
SUPER
privilege) now raises the errorER_CMD_NEED_SUPER_OR_CREATE_SPATIAL_REFERENCE_SYSTEM
. Use of theSUPER
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 theCREATE LIBRARY
andDROP LIBRARY
SQL statements added in MySQL 9.2.0; they can be included in other stored JavaScript programs with theUSING
clause added in the same release toCREATE FUNCTION
andCREATE 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 usinglibrary_name.function_name
notation. Libraries can be aliased as part of theUSING
clause withCREATE FUNCTION
orCREATE 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: TheLIBRARIES
table provides information about JavaScript libraries; theROUTINE_LIBRARIES
provides information about stored routines using JavaScript libraries.Counts of
CREATE LIBRARY
,DROP LIBRARY
, andSHOW CREATE LIBRARY
statements which have been issued on the server can be obtained as the status variablesCom_create_library
,Com_drop_library
, andCom_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
methodsgetFunction()
andgetProcedure()
. Each of these functions returns aFunction
object which can be invoked with arguments. A stored function argument orIN
parameter of a stored procedure can be passed directly; anOUT
orINOUT
parameter of a stored procedure requires a placeholder (anArgument
object) created usingmysql.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:
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
, andSET 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
andSET
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”.
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 byFLUSH 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:
Granting the
VERSION_TOKEN_ADMIN
privilegeThe following server system variables:
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.