MariaDB’s Latest & Greatest

This Oracle compatibility is optional. Simply execute the command SET SQL_MODE='ORACLE' to enable it for specific SQL statement blocks. Where feasible, existing MariaDB functionalities will remain unaffected.

Many of MariaDB’s Oracle compatibility features primarily support Oracle SQL syntax, particularly in areas where it differs from ANSI SQL or standard MariaDB. For example, Oracle SQL permits calling stored procedures directly by their name, omitting the CALL keyword.

However, more recent enhancements to Oracle compatibility mode extend beyond syntax to fundamental behaviors. Beginning with MariaDB 12.0, a single trigger can now respond to multiple INSERT/UPDATE/DELETE events, a distinct Oracle SQL capability that is challenging to implement by hand.

The MariaDB company additionally provides a migration utility. This tool examines an Oracle SQL DDL export file (containing only data definitions, not actual data) to evaluate its performance within MariaDB’s Oracle compatibility mode.

AI Capabilities: MCP Servers and Vector Data Types

AI functionalities are increasingly integrating into nearly all data-focused software products (some might even call it an ‘infestation’). MariaDB is part of this trend, but its AI features appear to be genuinely useful tools, not merely superficial marketing additions.

MariaDB 11.8 introduced a native VECTOR data type, enabling the embedding of text similarity information directly within the database. This allows MariaDB to power applications such as natural language queries, recommendation engines, RAG implementations, and various machine learning workloads. A significant portion of these demanding tasks can be executed within the database itself, reducing the need for costly external application interactions.

A further recent AI enhancement positions MariaDB as an extension for existing AI agents. The Model Context Protocol (MCP) establishes a standardized method for Large Language Models (LLMs) to interact with external tools, such as performing web searches. MariaDB now includes its own MCP server components, simplifying LLM queries against MariaDB instances. Furthermore, embeddings (from services like OpenAI, Gemini, or open Huggingface models) can be leveraged for semantic searches on documents housed within the database.

Enhanced JSON Capabilities

NoSQL databases empowered developers with flexible, open-ended schemas, often using JSON documents for data storage. Traditional SQL databases have subsequently integrated native JSON features, offering a practical solution to combine NoSQL’s adaptability with SQL’s structured schema benefits.

MariaDB includes a JSON column type that stores data in JSON format and can be automatically validated using a JSON_VALID CHECK constraint. For strict data typing within your JSON data, you can implement constraints for individual keys (e.g., ensuring the ‘year’ key always holds a valid INTEGER).

SELECT statements allow data extraction from JSON columns based on specific keys:

SELECT id, name, JSON_QUERY(attr, '$.dates.release') AS release_year FROM movies

Filtering by values is also possible:

SELECT id, name FROM movies WHERE JSON_VALUE(attr, '$.dates.release') = 1981

To optimize JSON operations for better performance, you can define virtual columns that correspond to keys within the document and then create indexes on these virtual columns:

ALTER TABLE movies ADD COLUMN
    release_year SMALLINT AS (JSON_VALUE(attr, '$.dates.release'));
CREATE INDEX releaseyears ON movies(release_year);

Furthermore, data can be modified directly without extracting and re-manipulating the JSON. Functions like JSON_INSERT(), JSON_ARRAY_APPEND(), and JSON_REMOVE() enable these in-place transformations, offering greater speed and reliability compared to manual external data manipulation.

Enhanced Optimizer Hints

Seasoned database developers value precise control over query execution. MariaDB has historically provided hints, or ‘modifiers,’ for SELECT statements, including HIGH_PRIORITY and FORCE INDEX.

MariaDB 12.0 introduced new-style optimizer hints, offering the ability to apply optimizations to particular tables or indexes, in addition to global application. These hints are written as inline comments, enclosed by /*+ and */ delimiters.

Enhanced hints are not intended for routine application. They allow you to explicitly enable or disable behaviors that generally perform well by default, but which might benefit from direct control in specific situations. For example, an INDEX_MERGE hint instructs the query optimizer to utilize only designated indexes for scans across multiple columns. This can accelerate queries on tables with numerous indexes or where a suitable composite index is absent. However, it’s crucial not to assume theoretical performance gains will translate directly to real-world scenarios. Always employ an EXPLAIN statement alongside any query optimization to verify its actual impact on speed.

Additional new hints prove valuable for both performance tuning and troubleshooting. For example, the MAX_EXECUTION_TIME() hint can terminate a particular query if it exceeds a specified duration in milliseconds. This hint is effective in preventing ‘runaway’ queries from consuming excessive system resources, and it also serves as a useful tool during the debugging of queries prone to erratic behavior.

Introducing a New XML Data Type

MariaDB 12.3 brings a forward-thinking addition: a new XMLTYPE column type, though its capabilities are currently limited. Presently, it primarily accepts strings up to 4GB and facilitates selective XML replacement using the UPDATEXML function. While XML validation or schema enforcement isn’t yet supported, these functionalities are planned for later releases, aiming to enhance XMLTYPE’s compatibility with Oracle. This lays the groundwork for significant future advancements.

Remaining Gaps in Features

As MariaDB has evolved and diverged from MySQL’s development trajectory, it has introduced numerous features absent in MySQL, such as dynamic or virtual columns. Conversely, certain MySQL features are not yet available in MariaDB.

A significant missing feature, MySQL resource groups, has been proposed but still requires development for implementation. Other functionalities, such as support for MySQL’s ‘packed’ (binary) JSON storage format, are unlikely to ever be cross-compatible.

Due to distinct and incompatible global transaction ID mechanisms between MariaDB and MySQL, a MariaDB server can serve as a replica for a MySQL server, but not vice-versa. A positive outcome of this is the ability to leverage replication for migrating from MySQL to MariaDB. Simply ensure that you are utilizing compatible versions of both database systems.