Meet the 11.8

About me

• Sergei Golubchik

• Chief Architect MariaDB
• MariaDB Foundation, board member
• MySQL core developer: 1998-2009
• MariaDB core developer: 2010-…

11.8

• Upcoming new MariaDB LTS
• RC early February
• GA early May
• ≈60 new features (big and small) compared to 11.4

MariaDB rolling

• Continuously maintained
• New features and bug fixes quarterly
• 11.5 → 11.6 → 11.7 → 11.8

MariaDB Enterprise Server

• 11.4 launched in January
• first release was 11.4.4-2
• Vector Search comes in 11.4.5-3

Vector search

• VECTOR data type
• VECTOR index type
• Fast approximate nearest neighbor search
• Text/Image/Video/Audio/Hybrid search, RAG, etc

• Dedicated session at 15:45

Optimizer

• 'cset_narrowing' optimization, enabled by default

SELECT * FROM orders, users
WHERE orders.user_name_mb3=users.user_name_mb4;

• cost-based choice of subquery strategies in UPDATE/DELETE
• optimizer support for indexes on virtual columns
• use index for SUBSTR(col, 1, n)='const'
• index condition pushdown on partitioned tables

Future-proof: TIMESTAMP

• 32-bit TIMESTAMP only good to 2038
• but 64-bit TIMESTAMP is 2x size, incompatible with 32-bit

• unsigned 32-bit TIMESTAMP — good to 2106
• same storage requirements
• fully compatible with old TIMESTAMP for values up to 2038

Compatibility: SQL Standard

• SESSION_USER() shows session owner

CREATE PROCEDURE demo() SQL SECURITY DEFINER
  SELECT CURRENT_USER(), SESSION_USER();

• correlation column list for subqueries in the FROM clause

SELECT * FROM (SELECT 1,2,3 AS dt(a,b,c));

• column list in the trigger definition

CREATE TRIGGER trg BEFORE UPDATE
  OF col1, col2 ON t1 FOR EACH ROW

Compatibility: UUID

• UUID() since forever — version 1
  • time based, but not good for indexing
  • except since MariaDB 10.9
• UUID_v4() fully random
• UUID_v7() time based, good for indexing

Compatibility: Oracle

• NOCOPY keyword

CREATE PROCEDURE smth(p IN NOCOPY VARCHAR(255))

• RECORD type in stored routines

DECLARE TYPE DeptRecTyp IS RECORD (
  dept_id   NUMBER(4) NOT NULL := 10,
  dept_name VARCHAR2(30) NOT NULL := 'Sales',
  mgr_id    NUMBER(6) := 200,
  loc_id    NUMBER(4) := 1700
);

Compatibility: Percona and MySQL

• system variable @@slow_query_log_always_write_time
• more columns in userstat tables
• missing tables for query_response_time plugin
• table aliases in single-table DELETE

DELETE FROM product xy WHERE id_product=123123;

Security

• PARSEC plugin
• INFORMATION_SCHEMA.USERS table
  • password expiration
  • failed login attempts
• unix_socket plugin user mapping

CREATE USER root IDENTIFIED VIA unix_socket
  AS 'joe'

Backup

• mariadb-dump --dir to dump many databases in parallel
• mariadb-import --dir to load many databases in parallel
• mariadb-import --innodb-optimize-keys

• mariadb-backup -p to not specify the password on the command line

Replication

• instant of binlogging of large transactions
• extend GTID binlog events with thread id
• --slave-abort-blocking-timeout option

Convenience features

• Limit size of created disk temporary files and tables
  • @@max_tmp_session_space_usage
  • @@max_tmp_total_space_usage
• Send initial values of system variables in first OK packet
• Set thread names for MariaDB Server threads
• FLUSH GLOBAL STATUS

More Convenience features

• Skipping a row operation from a trigger
• SHOW CREATE SERVER
• CURRENT_TIMESTAMP returns a TIMESTAMP
• uca1400_ai_ci collation by default
• converting a versioned table from implicit to explicit row_start/row_end columns