The release of database management system MariaDB 12.3.2 has been announced, marked as the first stable release of the 12.3 branch. The MariaDB 12.3 branch is classified as a long-term support release and will be supported until June 2029. Alongside it, the release of MariaDB 13.0.1 is available, which has release candidate status.
The MariaDB project develops a fork from MySQL, preserving backward compatibility and distinguished by the integration of additional storage engines and enhanced capabilities. The development of MariaDB is overseen by the independent MariaDB Foundation in accordance with an open and transparent development process that is not dependent on individual vendors. MariaDB is supplied instead of MySQL in many Linux distributions (RHEL, SUSE, Fedora, openSUSE, Slackware, OpenMandriva, ROSA, Arch Linux, Debian) and is implemented in major projects such as Wikipedia, Google Cloud SQL, and Nimbuzz.
Among the changes in the MariaDB 12.3 branch compared to the previous LTS release MariaDB 11.8:
- The binary log (binlog) implementation has been completely reworked, with the main components for managing it transferred to the InnoDB engine. This transfer has allowed for the elimination of outdated synchronization logic, speeding up replication and enhancing the performance of data writing operations. Testing the new implementation on high-load systems has shown performance gains of up to 4 times during high write operation counts. The configuration option 'binlog_storage_engine=innodb' is suggested for enabling this feature.
- The speed of vector search using the VECTOR type, utilized in machine learning systems, has been increased by 30-50%. This acceleration is achieved through the optimization of computations when searching for similarities between vectors in multidimensional spaces and the implementation of a new extrapolation technique based on nested vector representations (matryoshka embedding).
- New features have been added to simplify migration from Oracle database management systems. In compatibility mode with Oracle, functions such as TO_NUMBER (converting a string to a number), TO_DATE (converting a string to a date), and TRUNC (trimming a date, for instance, to the hour, day, month, or year) are implemented, along with support for associative arrays (INDEX BY) and the '( + )' syntax for defining outer joins.
- To improve compatibility with MySQL, a caching_sha2_password authentication plugin has been added, which uses the SHA2 hashing algorithm instead of SHA1, and is compatible with the similarly named plugin from MySQL 9.
- A new XMLTYPE type has been added for storing data in XML format.
- The use of cursors with parameterized queries (prepared statements) has been implemented.
- The 'SET PATH' expression has been added to specify the order of schema component search when referring to them without specifying the schema name.
- Support for the 'IS JSON' operation has been implemented to check the type of JSON expression, as defined in the SQL:2023 standard.
- The expression "PARTITION BY KEY" supports hashing algorithms MYSQL51, MYSQL55, BASE31, CRC32C, XXH32, and XXH3.
- A segmented key cache has been implemented in the Aria storage engine, where keys are divided into groups stored in separate cache segments. The new cache has improved the performance of parallel query execution by different users. The number of segments is set via the aria_pagecache_segments variable, which can take values from 1 (default) to 128.
- The ability to use functional indexes to improve the performance of GROUP/ORDER BY operations has been added.
- Support for the "CREATE GLOBAL TEMPORARY TABLE" expression has been implemented for creating global temporary tables. Such temporary tables are deleted when the session ends and are visible to all users, but contain data unique to each user.
- Support added SSL-keys protected by passwords. The password for unlocking SSL certificates can be set through the ssl_passphrase variable or manually at startup. server.
- The expression "SET SESSION AUTHORIZATION" has been added to perform actions as another user when the appropriate privileges are set (this expression can be viewed as analogous to "sudo" for database management systems).
- Support for SHA-2 hashes has been added to the file_key_management.so plugin.
- Support for weak cursor variables of the predefined type SYS_REFCURSOR has been added, allowing cursors to be returned from stored procedures (in the OUT parameter) or from functions (in the RETURN expression). A system variable "max_open_cursors" has been added to limit the maximum number of simultaneously open cursors.
- The TO_CHAR type has added support for the FM (Fill Mode) format to exclude additional padding. For example, the query
"SELECT CONCAT(‘/’, TO_CHAR(‘2020-01-06 10:11:12’, ‘FMDAY’), ‘/’);" will return "\/Monday\/" instead of "\/Monday \/". - Support for tables from the SEQUENCE engine has been added to the mariadb-check utility and the CHECK TABLE expression.
- The optimizer has added support for options (hints) that affect the generation of optimization plans: QB_NAME, NO_RANGE_OPTIMIZATION, NO_ICP, MRR, NO_MRR, BKA, NO_BKA, BNL, NO_BNL, SEMIJOIN, SUBQUERY, JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX, MAX_EXECUTION_TIME, [NO_]JOIN_INDEX, [NO_]GROUP_INDEX, [NO_]ORDER_INDEX, [NO_]INDEX, [NO_]SPLIT_MATERIALIZED, NO_ROWID_FILTER, INDEX_MERGE/NO_INDEX_MERGE, [NO_]DERIVED_CONDITION_PUSHDOWN, and [NO_]MERGE. Options are inserted into the query in a format compatible with MySQL, for example:
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE …;
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE …; - Performance of unordered expressions "LEFT JOIN" and ordered scanning has been improved when using the "PARTITION BY RANGE" expression.
- The optimizer trace results now show definitions
of tables and views. - An optimization has been implemented during table merge operations (JOIN), taking into account the uniqueness of rows in subqueries with the 'GROUP BY' expression for more accurate forecasting of the number of resulting rows (out_rows) and correct index application.
- Scalability of MDL (Metadata Lock) locks has been improved.
- Support for new GIS (Geographic Information System) functions has been added:
ST_Validate, MBRCoveredBy, ST_Simplif, ST_GeoHash, ST_LatFromGeoHash, ST_LongFromGeoHash, ST_PointFromGeoHash, ST_IsValid, ST_Collect. - Support for triggers firing on multiple events has been added. The CREATE TRIGGER expression can now list the events it applies to using the syntax "{ event [ OR … ] }".
- The audit log plugin has added support for buffering write operations to the log. The buffer size is specified through the server_audit_file_buffer_size variable.
Audit log now records information about network ports for incoming connections (previously, only the host was reflected). - The mariadb utility has added the option "--script-dir" to specify an alternative directory for searching scripts executed by the SOURCE command.
- Parallel replication is allowed in asynchronous data replication between two Galera clusters.
- The mariadb-dump utility has been enhanced with the ability to specify patterns (e.g., 'database_*') using the '-L' (—wildcards) option.
- The requirement for unique foreign key identifiers in the context of the entire database has been removed (uniqueness at the table level is sufficient).
- The limitations on the level of nesting of structures in functions for working with JSON have been lifted (the JSON_DEPTH_LIMIT, which previously had a value of 32, has been removed).
- A table TRIGGERED_UPDATE_COLUMNS has been added to the INFORMATION_SCHEMA schema (INFORMATION_SCHEMA.TRIGGERED_UPDATE_COLUMNS), showing the columns modified when a trigger is fired.
- In the PARAMETERS table within the INFORMATION_SCHEMA schema, a PARAMETER_DEFAULT column has been implemented, containing default values for stored procedure parameters.
- The variables big_tables, large_page_size, and storage_engine have been removed as they were previously marked deprecated.
Source: opennet.ru
