Compatibility with MySQL and other DBMS

Each MariaDB tree uses a MySQL tree as a codebase. For example, MariaDB 5.5 is based on MySQL 5.5. When the MySQL tree is updated, MariaDB imports the bug fixes and new features. MariaDB should be fully compatible with the corresponding MySQL tree. This means that all the SQL statements, API calls, and configuration settings that work with MySQL will produce the same results on MariaDB. If an undocumented compatibility is found, it is treated as a bug. Of course, MariaDB develops new features on top of the MySQL codebase; thus what works with MariaDB will not work on MySQL if the MariaDB-specific features are used.

In a replication environment, it is safe to replicate MySQL on a compatible version of MariaDB. The opposite is only safe if the queries do not use MariaDB-specific features.

MariaDB also imports several features from the Percona Server, which also uses MySQL as a codebase. This means that programs that use features specific to the Percona Server can work with the corresponding tree of MariaDB.

The following table shows the correspondence between MySQL and MariaDB trees:

MariaDB tree	MySQL tree
5.1	5.1
5.2	5.1
5.3	5.1
5.5	5.5
10.0	5.5, partly 5.6

Each MariaDB tree till 5.5 is compatible with the MySQL tree having the same number, or (if such a tree does not exist) with the latest version having a lower number. MariaDB 10.0 breaks this pattern because it just implements a part of the MySQL 5.6 features; thus, it is not fully compatible. The complete list of incompatibilities is available in MariaDB Knowledge Base. Most users should not be affected, unless they want to use MySQL 5.6 and MariaDB 10.0 in the same replication environment. Also, MySQL 5.6 allows InnoDB tables to be used as a bridge to memcache, but this feature is not currently available in MariaDB.

MariaDB and MySQL use a syntax called executable comments to improve compatibility with other DBMS. Executable comments can be used to execute a part of a SQL statement on MySQL and MariaDB, but not on other DBMS; or just on MariaDB but not on MySQL; or again, on recent versions of MariaDB but not on older versions.

The most generic executable comment allows a part of a query to be executed on MariaDB and MySQL, shown as follows:

SELECT 1 /*! , VERSION() */;

By adding M, the comment will only be executed on MariaDB:

SELECT 1 /*M! , 'You are using MariaDB!' */;

It is possible to specify a minimum version number. This number must consist of five or six digits in the following form: the first number or the first two numbers are the major version, the following two numbers are the minor version, and the final two numbers are the patch number. For example:

SELECT 1
  /*!50510 , 'MySQL 5.5.10 or newer' */
  /*M!100006 , 'MariaDB 10.0.6 or newer' */;

Note that MariaDB 10.0 always executes executable comments for MySQL 5.6, which is mostly compatible, but it ignores executable comments for MySQL 5.7.

Also, the SQL_MODE system variable has some flags that make the general syntax more compatible with older versions of MySQL, or with other database systems.