1.4 What Is New in MySQL 9.1

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

• Features Added or Changed in MySQL 9.1

• Features Deprecated in MySQL 9.1

• Features Removed in MySQL 9.1

Features Added or Changed in MySQL 9.1

The following features have been added to MySQL 9.1:

• Triggers no longer loaded by read-only operations.  Previously, for a table having one or more triggers, the triggers were fully parsed and loaded into memory every time the table was accessed. This was problematic for two reasons:

  1. Cached trigger objects consumed space in the table cache, even when they were not actually used because the statement accessing the table did not change any data. For example, this occurred when the table was accessed by a SELECT statement.

  2. Parsing and caching of triggers which were not used added unnecessary processing time to read-only operations such as SELECT or TABLE statements.

  These issues often resulted in unexpectedly high resource usage and unreasonably long execution times in scenarios involving many tables, complex triggers, or both, in which no data was changed. They also led to unnecessary resource usage on read-only replicas.

  The problems just described have been solved in MySQL 9.1 as follows:

  • The process of parsing and loading triggers has been divided into two phases: In the first phase, only the trigger metadata is read from the data dictionary, since this can be stored once and shared between different instances of the same trigger. Parsing and execution of the trigger is done in the second phase.

  • The second phase described in the previous item is performed only for statements that modify table data. This eliminates consumption of resources by parsing and caching triggers which are not actually used.

  • Memory usage is further improved by replacing static allocation of buffers for trigger errors with the use of variably sized buffers which are allocated on demand, instead.

  This feature is implemented by tracking tables having triggers in the table cache separately from tables which do not. The maximum number of open tables which have triggers and which are held in the table cache at any given time is determined by the value of the server system variable table_open_cache_triggers, which is added in MySQL 9.1.0. Leaving this variable at its default value means that no tables are evicted from the cache based on whether they have fully loaded trigger, which is the same as in previous versions of MySQL. The following server status variables which track cache usage for tables having triggers are also added in this release:

  • Table_open_cache_triggers_hits: The number of cache hits for open tables with fully loaded triggers.

  • Table_open_cache_triggers_misses: The number of cache misses for open tables with fully loaded triggers.

  • Table_open_cache_triggers_overflows: The number of cache overflows for open tables with fully loaded triggers.

  In addition, as part of this work, SHOW CREATE TRIGGER, which reads but does not modify data, now displays the complete definition of all triggers, even in cases where trigger bodies cannot be parsed. Such triggers might be present in the data dictionary after upgrading the MySQL server to a version that no longer supports the syntax used in those triggers.

  For more information, see the descriptions of the server variables mentioned previously. For general information about the table cache, see Section 10.4.3.1, “How MySQL Opens and Closes Tables”.

• EXPLAIN output enhancements.  A number of improvements have been made in the output of EXPLAIN. Information about these changes follows in the next few paragraphs.

  EXPLAIN output now includes information about multi-range reads in the following two cases:

  • When explain_json_format_version=2, the output from EXPLAIN FORMAT=JSON now includes "multi_range_read": true whenever multi-range read and index range scan access paths are used.

  • (Multi-Range Read) now appears in the operation field in EXPLAIN FORMAT=TREE output for index range scans using multi-range reads.

  EXPLAIN FORMAT=TREE also now includes information about the semijoin strategy employed, if any.

  In addition, for consistency of output, spaces have been added before and after the = sign in the condition shown for lookup_condition by EXPLAIN FORMAT=JSON when explain_json_format_version=2, as well as those shown for operation when using EXPLAIN FORMAT=TREE.

  For more information about these optimizations, see Section 10.2.1.11, “Multi-Range Read Optimization”, and Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations”.

• CREATE VIEW IF NOT EXISTS.  MySQL 9.1 adds support for an IF NOT EXISTS clause for the CREATE VIEW statement. If the view named in the statement does not exist, it is created as though the clause were not used. If the view already exists, the statement appears to succeed but does not change the view definition, and yields a warning, as shown here:

  mysql> CREATE VIEW IF NOT EXISTS v1 AS SELECT c1, c3 FROM t1;
  Query OK, 0 rows affected, 1 warning (0.01 sec)
  
  mysql> SHOW WARNINGS;
  +-------+------+---------------------------+
  | Level | Code | Message                   |
  +-------+------+---------------------------+
  | Note  | 1050 | Table 'v1' already exists |
  +-------+------+---------------------------+
  1 row in set (0.00 sec)

  IF NOT EXISTS cannot be used together with OR REPLACE; attempting to do so causes the CREATE VIEW statement to be rejected with a syntax error.

  See Section 15.1.23, “CREATE VIEW Statement”, for more information.

• Crash-safe database DDL statements.  CREATE DATABASE and DROP DATABASE are now fully atomic and crash-safe, as long as all tables in the named database employ a storage engine supporting atomic DDL such as InnoDB. This resolves the following issues:

  • When CREATE DATABASE failed after the database directory was created but before the operation was actually committed, the data dictionary had no record of the database's existence, but the database directory itself was still present in the file system, requiring manual cleanup.

  • When removal of the database directory as part of DROP DATABASE was unsuccessful due to a file system error or an unexpected shutdown, the transaction containing DROP DATABASE was not always rolled back; manual intervention was necessary in such cases to rectify the problem.

  See Section 15.1.1, “Atomic Data Definition Statement Support”, for more information.

• VECTOR support in JavaScript programs.  The VECTOR type is fully supported by JavaScript stored programs in MySQL 9.1 and later. VECTOR values can now be used with JavaScript programs as input arguments, output arguments, prepared statement bind() parameters, and return values.

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

• Replication applier statistics tables.  MySQL Enterprise: The Replication Applier Metrics component (component_replication_applier_metrics), added in MySQL 9.1.0, provides users with statistical information about the replication applier in the form of the two Performance Schema tables listed here:

  • replication_applier_metrics: This table provides replication applier metrics for a given replication channel.

  • replication_applier_progress_by_worker: This table provides replication applier metrics for a specific worker.

  This component and the Performance Schema tables it implements enhance observability of replication by gathering statistics from the entire replication pipeline. For MySQL Enterprise users, this unifies the collection and presentation of statistics by replacing the subtoptimal logging of applier metrics into the error log as done in previous versions of MySQL. This facilitates observability, troubleshooting and forensics when operating a MySQL service. In addition, some metrics which were not helpful have been replaced with more useful ones.

  See Section 7.5.6, “Replication Applier Metrics Component”, for more information.

• OpenID Connect Support.  MySQL 9.1 adds support for OpenID Connect authentication with MySQL Enterprise Edition using the authentication_openid_connect server-side authentication plugin.

  See Section 8.4.1.9, “OpenID Connect Pluggable Authentication”, for more information.

• Option Tracker component.  The Option Tracker component (component_option_tracker) added in MySQL 9.1.0 provides information about options (features) present in the MySQL server as well as those in components and plugins. This component is available as part of MySQL Enterprise Edition.

  Option information is available from two tables installed by installing the Option Tracker component, listed here:

  • The Performance Schema mysql_option table contains information about all options installed on the server, including name, component name (or MySQL Server), and whether the option is currently enabled. This table is read-only.

  • The option_usage table, in the mysql_option database installed by the Option Tracker component, shows usage information for each option on the system; this information is presented in JSON format. If the server acts as a node in a Group Replication cluster, its server ID and cluster ID are also shown in this table. This table can be updated.

  For a given option, the values of the OPTION_NAME column of the mysql_option table and the column having the same name in the option_usage table are the same; you can use these as the join columns in queries against both tables together.

  For more information, see Section 7.5.8, “Option Tracker Component”, as well as Section 29.12.22.6, “The mysql_option Table”.

Features Deprecated in MySQL 9.1

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

Features Removed in MySQL 9.1

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

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