After a year of development and three preliminary releases, the first stable release of the new branch of the MariaDB database management system, version 10.6, has been published. This version is a fork of MySQL that maintains backward compatibility and features the integration of additional storage engines and enhanced capabilities. Support for this branch will be provided for 5 years, until July 2026.
The development of MariaDB is overseen by the independent organization MariaDB Foundation, following a completely open and transparent development process that is not dependent on individual vendors. MariaDB is used instead of MySQL in many Linux distributions (RHEL, SUSE, Fedora, openSUSE, Slackware, OpenMandriva, ROSA, Arch Linux, Debian) and is implemented in large projects such as Wikipedia, Google Cloud SQL, and Nimbuzz.
Key improvements in MariaDB 10.6:
- Atomic execution has been ensured for the statements 'CREATE TABLE|VIEW|SEQUENCE|TRIGGER', 'ALTER TABLE|SEQUENCE', 'RENAME TABLE|TABLES', 'DROP TABLE|VIEW|TRIGGER|DATABASE' (either the entire statement is completed, or everything is reverted to its original state). In the case of 'DROP TABLE' operations that delete multiple tables at once, atomicity is ensured at the individual table level. The goal of this change is to maintain integrity in case of a crash during the execution of an operation. Previously, after a crash, temporary tables and files could remain, synchronization between tables in storage engines and frm files could be disrupted, and individual tables might remain unrenamed when multiple tables were renamed at once. Integrity is maintained through a state recovery log, the path to which can be defined via the new option '--log-ddl-recovery=file' (default is ddl-recovery.log). server A construct defined in the SQL 2008 standard, 'SELECT … OFFSET … FETCH', has been implemented, allowing a specified number of rows to be output starting from a specified offset, with the possibility of using the 'WITH TIES' parameter to attach one additional subsequent value. For example, the statement 'SELECT i FROM t1 ORDER BY i ASC OFFSET 1 ROWS FETCH FIRST 3 ROWS WITH TIES' differs from the construct 'SELECT i FROM t1 ORDER BY i ASC LIMIT 3 OFFSET 1' by outputting an additional element that follows (instead of 3, 4 rows will be output).
- For the InnoDB engine, the syntax 'SELECT … SKIP LOCKED' has been implemented, allowing exclusion of rows for which a lock cannot be applied ('LOCK IN SHARE MODE' or 'FOR UPDATE').
- For the InnoDB engine, the syntax 'SELECT … SKIP LOCKED' has been implemented, allowing exclusion of rows for which a lock cannot be applied ('LOCK IN SHARE MODE' or 'FOR UPDATE').
- The option to ignore indexes has been implemented (in MySQL 8, this functionality is referred to as "invisible indexes"). An index is marked for ignoring using the IGNORED flag in the "ALTER TABLE" statement; after that, the index remains visible and updatable but is not used by the optimizer.
- The JSON_TABLE() function has been added, designed to convert JSON data into a relational format. For example, a JSON document can be converted for use in the context of a table, which can be specified within the FROM block of a SELECT statement.
- Compatibility with Oracle DBMS has been improved: support for anonymous subqueries within the FROM block has been added. The MINUS construct (equivalent to EXCEPT) is now implemented. Functions ADD_MONTHS(), TO_CHAR(), SYS_GUID(), and ROWNUM() have been added.
- In the InnoDB engine, insertion into empty tables has been accelerated. The COMPRESSED row format by default has been switched to read-only mode. The SYS_TABLESPACES schema replaces SYS_DATAFILES and reflects the state in the filesystem directly. Delayed writing support has been provided for the temporary tablespace. Support for the old checksum algorithm, maintained for compatibility with MariaDB 5.5, has been discontinued.
- In the replication system, the size of the master_host parameter has been increased from 60 to 255 characters, and master_user to 128. The binlog_expire_logs_seconds variable has been added to configure the expiration time of the binary log in seconds (previously, the expiration time was defined only in days via the expire_logs_days variable).
- In the synchronous multi-master replication mechanism of Galera, the wsrep_mode variable has been implemented to configure the API WSREP (Write Set REPlication) parameters. The transition from unencrypted communications to TLS has been allowed without stopping the cluster.
- A sys-schema has been implemented, which collects a collection of views, functions, and procedures for analyzing database operations.
- System tables for analyzing replication performance have been added.
- The set of information tables has been expanded with the INFORMATION_SCHEMA.KEYWORDS and INFORMATION_SCHEMA.SQL_FUNCTIONS views, which output a list of available keywords and functions.
- The TokuDB and CassandraSE storages have been removed.
- The utf8 encoding has been switched from a four-byte representation utf8mb4 (U+0000..U+10FFFF) to a three-byte utf8mb3 (covering the Unicode range U+0000..U+FFFF).
- Support for socket activation in systemd has been added.
- The GSSAPI plugin has been enhanced to support Active Directory group names and SIDs.
- A check has been added for the configuration file $MARIADB_HOME/my.cnf in addition to $MYSQL_HOME/my.cnf.
- New system variables binlog_expire_logs_seconds, innodb_deadlock_report, innodb_read_only_compressed, wsrep_mode, and Innodb_buffer_pool_pages_lru_freed have been implemented.
Source: opennet.ru
