MySQL, a widely adopted open-source RDBMS, has launched its second update to the 9.x series, version 9.2, bringing several fresh features while deprecating and removing certain others.
One of the release’s highlights is the new CREATE_SPATIAL_REFERENCE_SYSTEM privilege, which grants users the ability to execute statements such as “CREATE SPATIAL REFERENCE SYSTEM,” “CREATE OR REPLACE SPATIAL REFERENCE SYSTEM,” and “DROP SPATIAL REFERENCE SYSTEM.”
However, running these statements without this privilege (or the SUPER privilege) generates an error. Although the SUPER privilege can still be used for these statements, developers should treat that usage deprecated, and plan transitions accordingly.
On the JavaScript libraries side, there are a lot of changes, with the most striking one being MySQL’s Multilingual Engine Component (MLE), which now supports reusable JavaScript libraries, making sharing and managing JavaScript functions easier across different stored programs.
In other words, you can now efficiently organize your code into reusable modules with CREATE LIBRARY and DROP LIBRARY statements.
Additionally, JavaScript libraries can be included in other stored programs through the new USING clause within “CREATE FUNCTION” or “CREATE PROCEDURE.” Moreover, MySQL 9.2.0 provides a handy “SHOW CREATE LIBRARY” statement for retrieving the code of existing libraries, as well as two new Information Schema tables—LIBRARIES and ROUTINE_LIBRARIES—to track and manage these JavaScript resources.
The MLE component extends its reach by allowing JavaScript routines to call MySQL-stored functions, procedures, and user-defined session variables. Now, JavaScript can invoke MySQL functions or procedures via methods such as getFunction() and getProcedure().
It’s also worth noting that session variables can now be accessed directly through the global Session object in JavaScript. The 9.2 release also introduces direct JavaScript access to MySQL built-in functions like rand(), sleep(), uuid(), and isUUID() via the global Mysql object.
Transaction support is another exciting addition: MySQL 9.2 provides a JavaScript Transactional API that corresponds to MySQL’s standard SQL commands, such as START TRANSACTION, COMMIT, ROLLBACK, and SET AUTOCOMMIT. Savepoints are also included. Accompanying this transaction functionality is the SqlError object, enabling robust error handling within JavaScript routines.
Furthermore, MySQL’s ENUM and SET data types are now recognized in JavaScript stored routines, allowing for seamless conversion back and forth. So, if your application relies on these types, you can work directly with them in JavaScript without awkward conversions.
MySQL 9.2 introduces format version information to the output of EXPLAIN FORMAT=JSON when the system variable “explain_json_format_version” is set to 2. This addition helps developers distinguish between different JSON output formats and adapt tooling or scripts accordingly. When the format version is 1, the EXPLAIN result omits this version information to maintain compatibility with older processes.
Besides the new additions mentioned above, MySQL 9.2 flags a few features as deprecated. The FLUSH PRIVILEGES statement, long used for reloading the grant tables, is now deprecated. The Version Tokens plugin is also slated for removal in a future release.
At the same time, the new release fully removes certain items previously available in version 9.1, so developers should be aware of their absence when upgrading or replicating. For instance, the BINLOG keyword is no longer available for unquoted usage within stored routines.
For more in-depth details, check out the release notes or look here to review all the changes that MySQL 9.2 brings.
