After a year of development and three pre-releases, the first stable release of the new MariaDB 10.6 DBMS branch has been published, within which a fork from MySQL is developed that maintains backward compatibility and is distinguished by the integration of additional storage engines and advanced features. The new branch will be supported for 5 years, until July 2026.
MariaDB development is overseen by the independent MariaDB Foundation, following a fully open and transparent development process independent of individual vendors. MariaDB is shipped in place of MySQL on many Linux distributions (RHEL, SUSE, Fedora, openSUSE, Slackware, OpenMandriva, ROSA, Arch Linux, Debian) and has been adopted by major projects such as Wikipedia, Google Cloud SQL, and Nimbuzz.
Key improvements in MariaDB 10.6:
- The atomicity of the execution of the expressions "CREATE TABLE|VIEW|SEQUENCE|TRIGGER", "ALTER TABLE|SEQUENCE", "RENAME TABLE|TABLES", "DROP TABLE|VIEW|VIEW|TRIGGER|DATABASE" is ensured (either the expression is completely completed, or everything is returned back to original state). In the case of DROP TABLE operations that drop multiple tables at once, atomicity is provided at the level of each individual table. The purpose of the change is to ensure integrity in the event of a server crash during an operation. Previously, after a crash, temporary tables and files could remain, synchronization of tables in storage engines and frm files could be disrupted, individual tables could remain unnamed when several tables were renamed at once. Integrity is ensured by state recovery logging, which can be pathed through the new option "--log-ddl-recovery=file" (default is ddl-recovery.log).
- The "SELECT ... OFFSET ... FETCH" construction defined in the SQL 2008 standard has been implemented, which allows you to display a certain number of rows, starting at a specified offset, with the ability to use the "WITH TIES" parameter to attach another next value. For example, the expression "SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS WITH TIES" differs from the construction "SELECT i FROM t1 ORDER BY i ASC LIMIT 3 OFFSET 1" by displaying one more element following in the tail (instead of 3 4 lines will be displayed).
- For the InnoDB engine, the "SELECT ... SKIP LOCKED" syntax is implemented, which allows you to exclude rows for which a lock cannot be set ("LOCK IN SHARE MODE" or "FOR UPDATE").
- Implemented the ability to ignore indexes (in MySQL 8, this functionality is called "invisible indexes"). An index is marked to be ignored using the IGNORED flag in the ALTER TABLE statement, after which the index remains visible and updatable, but is not used by the optimizer.
- Added JSON_TABLE() function to convert JSON data to relational form. For example, a JSON document can be converted to be used in the context of a table that can be specified inside a FROM clause in a SELECT statement.
- Improved compatibility with Oracle DBMS: Added support for anonymous subqueries inside the FROM block. The MINUS construct (equivalent to EXCEPT) has been implemented. Added ADD_MONTHS(), TO_CHAR(), SYS_GUID() and ROWNUM() functions.
- Faster insertion into empty tables in the InnoDB engine. The COMPRESSED string format is set to read-only by default. The SYS_TABLESPACES scheme has replaced SYS_DATAFILES and directly reflects the state in the file system. Lazy write support is provided for the temporary tablespace. Removed support for the old checksum algorithm, which was retained for compatibility with MariaDB 5.5.
- In the replication system, the size of the master_host parameter value has been increased from 60 to 255 characters, and master_user to 128 characters. The binlog_expire_logs_seconds variable has been added to set the binary log expiration time in seconds (previously, the reset time was determined only in days through the expire_logs_days variable).
- Galera's synchronous multi-master replication mechanism implements the wsrep_mode variable for setting WSREP (Write Set REPlication) API parameters. Allowed transition of Galera from unencrypted communications to TLS without stopping the cluster.
- The sys-schema schema has been implemented, which contains a collection of views, functions and procedures for analyzing database operations.
- Added service tables for replication performance analysis.
- The INFORMATION_SCHEMA.KEYWORDS and INFORMATION_SCHEMA.SQL_FUNCTIONS views have been added to the set of information tables, displaying a list of available keywords and functions.
- Removed TokuDB and CassandraSE repositories.
- The utf8 encoding has been changed from the four-byte representation utf8mb4 (U+0000..U+10FFFF) to the three-byte representation utf8mb3 (covers the Unicode range U+0000..U+FFFF).
- Added support for socket activation in systemd.
- Added support for Active Directory group names and SIDs in the GSSAPI plugin.
- Added check for the presence of the configuration file $MARIADB_HOME/my.cnf in addition to $MYSQL_HOME/my.cnf.
- Implemented new system variables binlog_expire_logs_seconds, innodb_deadlock_report, innodb_read_only_compressed, wsrep_mode and Innodb_buffer_pool_pages_lru_freed.
Source: opennet.ru