MariaDB maintains high levels of compatibility with MySQL, and most applications that use MySQL will work seamlessly with MariaDB. However, take note of the following incompatibilities and feature differences between MariaDB 10.3 and MySQL 5.7
In addition to the standard , , , , , , and storage engines, the following are also available with MariaDB 10.3:
, a storage engine with great compression
, MyISAM replacement with better caching.
(drop-in replacement for Federated)
Many optimizer enhancements. are more usable. The complete list and a comparison with MySQL is .
Faster and safer replication:. This makes many setups that use replication and lots of updates .
We've added a lot of . If a patch or feature is useful, safe, and stable — we make every effort to include it in MariaDB. The most notable features are:
is a standard part of MariaDB Server.
(also known as AS OF)
, allowing instances, databases or tables to be rolled back to an old snapshot.
When upgrading from MySQL 5.7 to , please take note of the following incompatibilities:
For a list of function differences, see
For a list of system variable differences, see
MariaDB binaries (mysqld, etc.) give a warning if one uses a unique prefix of an option (such as --big-table instead of --big-tables). MySQL binaries require the full option name.
for Innodb asynchronous IO subsystem on Windows.
for MyISAM. Can speed up MyISAM tables with up to 4x
for MyISAM and Aria. This can greatly improve shutdown time (from hours to minutes) if you are using a lot of MyISAM/Aria tables with delayed keys.
is faster.
We improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvement is 1-5 % (according to sql-bench) but can be higher for big result sets with all characters between 0x00-0x7f.
allows MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
Lots of speed improvements when a client connects to MariaDB.
There are some improvements to the DBUG code to make its execution faster when debug is compiled in but not used.
Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The storage engine is used for internal temporary tables, which should give a speedup when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn't have to write the temporary rows to disk.
The test suite has been extended and faster than before, even though it tests more things.
merged into the server
and .
Number of supported decimals in has increased from 30 to 38
New statement. WITH is a common table expression that allows you to refer to a subquery expression many times in a query.
, including DEFAULT for and
Added catchall for list partitions
Oracle-style statement
Lots of new
. This makes replication notably faster!
The binary log in MariaDB .
Progress reporting for and
Faster joins and subqueries
and faster calls
support
gives the EXPLAIN plan for a query running in another thread. MySQL introduced the EXPLAIN FOR CONNECTION syntax to do the same thing.
(including )
OR REPLACE syntax for statements, such as , , etc.
MariaDB in the DEFAULT clause, while MySQL does not.
MariaDB than MySQL, including NO PAD collations.
MariaDB supports Spatial Reference System IDs for spatial data type columns with .
MariaDB's GTID is not compatible with MySQL's. This means that one can't have MySQL 5.7 as a slave for MariaDB 10.3. However MariaDB 10.3 can be a slave of MySQL 5.7 or any earlier MySQL/MariaDB version. Note that MariaDB and MySQL also have different , so these need to be adjusted when migrating.
To make CREATE TABLE ... SELECT work the same way in statement based and row based replication it's by default executed as on the slave. One benefit of this is that if the slave dies in the middle of CREATE ... SELECT it will be able to continue.
One can use the variable to specify how CREATE TABLE and DROP TABLE is replicated.
MySQL has the enabled by default. For performance reasons MariaDB 10.3 has it disabled by default. You can enable it by starting mysqld with the option --performance-schema.
MySQL 5.7 features a new implementation of the performance_schema and a sys schema wrapper. These are not yet supported in MariaDB.
MariaDB 10.3 implements in a different way to MySQL 5.7.
MariaDB 10.3 does not support for InnoDB.
The OVER, ROWS and RECURSIVE keywords are in MariaDB 10.3, but not in MySQL 5.7. Note that in MySQL 8.0 these are also reserved words.
MariaDB stores as true text, not in binary format as MySQL. MariaDB's JSON functions are much faster than MySQL's so there is no need to store in binary format, which would add complexity when manipulating JSON objects.
For the same reason, MariaDB's is an alias for . If you want to replicate JSON columns from MySQL to MariaDB, you should store JSON objects in MySQL in a TEXT or LONGTEXT column or use statement based replication. If you are using JSON columns and want to upgrade to MariaDB, you need to either convert them to TEXT or use to copy these tables to MariaDB.
In MySQL, JSON is compared according to json values. In MariaDB JSON strings are normal strings and compared as strings.
MariaDB 10.3 does not support MySQL's JSON operators (-> and ->>) (MDEV-13594)
MariaDB 10.3 supports the standard by producing null and a warning for when given invalid data, while MySQL produces an error.
MariaDB 10.3 does not support the ngram and MeCab full-text parser plugins - MDEV-10267, MDEV-10268.
MariaDB 10.3 does not support the MySQL X plugin.
MariaDB 10.3 does not support MySQL 5.7's “native” InnoDB partitioning handler.
MariaDB 10.3 does not support MySQL 5.7's ALTER TABLE...RENAME INDEX statements.
MySQL's implementation of aborting statements that exceed a certain time to execute can only kill SELECTs, while MariaDB's can kill any queries (excluding stored procedures).
MariaDB 10.3 does not support MySQL's SELECT MAX_STATEMENT_TIME = N ... for MySQL older than 5.7.8 or SELECT /*+ MAX_EXECUTION_TIME(n) */ ... for MySQL 5.7.8 and higher - see Aborting Statements that Exceed a Certain Time to Execute.
The MySQL version of is defined in millseconds, not seconds.
MariaDB 10.3 does not support the MySQL Memcached plugin. However, data stored using memcached can be retrieved because the data is stored as InnoDB tables. MariaDB is able to start successfully with an error message of not being able to find libmemcached.so library.
Users created with MySQL's SHA256 password algorithm cannot be used in MariaDB 10.3 - MDEV-9804.
MariaDB 10.3 doesn't support user or (MariaDB 10.4 does)
In MySQL, X'HHHH', the standard SQL syntax for binary string literals, erroneously works in the same way as 0xHHHH, which could work as a number or string depending on the context. In MariaDB, this has been fixed to behave as a string in all contexts (and never as a number). See and for more details and examples.
In MariaDB 10.3, does not quote the DEFAULT value of an integer. Older versions of MariaDB, and MySQL, do. Since MariaDB 10.3 can support defaults for and fields, while MySQL does not, will also append DEFAULT NULL where no default is explicitly provided to nullable BLOB or TEXT fields in MariaDB.
Since MariaDB in the DEFAULT clause, in MariaDB, the table contains extra fields, and also quotes the DEFAULT value of a string in the COLUMN_DEFAULT field in order to distinguish it from an expression.
Since MariaDB supports and , these are both and can't be used as an without being quoted.
As a result of implementing , the has been renamed to VALUE().
MariaDB does not support the optional init_vector argument for and or the block_encryption_mode variable - MDEV-9069
MariaDB does not support the --initialize option. Use instead. - MDEV-19010
Not all are across both MySQL and MariaDB. As of 10.3.24, MariaDB supports 40 character sets and 322 collations. As of 5.7.29, MySQL supports 41 character sets (gb18030 being the additional one) and 222 collations.
The MySQL binary log includes the thread_id, while MariaDB's does not. (MDEV-7850)