Incompatibilities and Feature Differences Between MariaDB 10.2 and MySQL 5.7

The following is a list of incompatibilities and feature differences between MariaDB 10.2 and MySQL 5.7

Storage Engines

In addition to the standard MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE storage engines, the following are also available with MariaDB 10.2:

• MyRocks, a storage engine with great compression
• Aria, MyISAM replacement with better caching.
• TokuDB
• CONNECT
• SEQUENCE
• SphinxSE
• Spider
• FederatedX (drop-in replacement for Federated)
• OQGRAPH
• Cassandra

Speed Improvements

• Many optimizer enhancements. Subqueries are more usable. The complete list and a comparison with MySQL is here. A benchmark can be found here.
• Faster and safer replication: Group commit for the binary log. This makes many setups that use replication and lots of updates more than 2x times faster.
• Parallel replication
• Improvements for Innodb asynchronous IO subsystem on Windows.
• Segmented Key Cache for MyISAM. Can speed up MyISAM tables with up to 4x
• Adjustable hash size 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.
• CHECKSUM TABLE 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.
• MariaDB Thread pool 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 Aria 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.

Extensions and New Features

We've added a lot of new features to MariaDB. If a patch or feature is useful, safe, and stable — we make every effort to include it in MariaDB. The most notable features are:

• Window functions
• Number of supported decimals in DECIMAL has increased from 30 to 38
• Recursive Common Table Expressions
• New WITH statement. WITH is a common table expression that allows you to refer to a subquery expression many times in a query.
• CHECK CONSTRAINT
• DEFAULT expression, including DEFAULT for BLOB and TEXT
• Added catchall for list partitions
• Oracle-style EXECUTE IMMEDIATE statement
• Lots of new JSON functions
• Microsecond Precision in Processlist
• Table Elimination
• Virtual Columns
• Extended User Statistics
• KILL all queries for a user
• KILL QUERY ID - terminates the query by query_id, leaving the connection intact
• Pluggable Authentication
• Storage-engine-specific CREATE TABLE
• Enhancements to INFORMATION SCHEMA.PLUGINS table
• Group commit for the binary log. This makes replication notably faster!
• Added --rewrite-db mysqlbinlog option to change the used database
• Progress reporting for ALTER TABLE and LOAD DATA INFILE
• Faster joins and subqueries
• HandlerSocket and faster HANDLER calls
• Dynamic Columns support
• SHOW EXPLAIN gives the EXPLAIN plan for a query running in another thread.
• Roles
• PCRE Regular Expressions (including REGEXP_REPLACE())
• DELETE ... RETURNING

Incompatibilities

When upgrading from MySQL 5.7 to MariaDB 10.2, please take note of the following incompatibilities:

• For a list of function differences, see Function Differences Between MariaDB 10.2 and MySQL 5.7
• For a list of system variable differences, see System Variable Differences Between MariaDB 10.2 and MySQL 5.7
• MySQL binaries (mysqld, myisamchk etc.) give a warning if one uses a prefix of an option (such as --big-table instead of --big-tables). MariaDB binaries work in the same way as most other Unix commands and don't give warnings when using unique prefixes.
• 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.2. However MariaDB 10.2 can be a slave of MySQL 5.7 or any earlier MySQL/MariaDB version. Note that MariaDB and MySQL also have different GTID system variables, 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 CREATE OR REPLACE TABLE 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 slave-ddl-exec-mode variable to specify how CREATE TABLE and DROP TABLE is replicated.
• MySQL has the performance schema enabled by default. For performance reasons MariaDB 10.2 has it disabled by default. You can enable it by starting mysqld with the option --performance-schema.
• MariaDB 10.2 implements InnoDB encryption in a different way to MySQL 5.7.
• MariaDB 10.2 does not support the ngram and MeCab full-text parser plugins - MDEV-10267, MDEV-10268.
• MariaDB 10.2 does not support CREATE TABLESPACE for InnoDB.
• The ROWS and RECURSIVE keywords are reserved words in MariaDB 10.2, but not in MySQL 5.7.
• MariaDB stores JSON 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 JSON data type is an alias for LONGTEXT. 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 use mysqldump to copy these tables to MariaDB.
• MariaDB 10.2 does not support MySQL's JSON operators (-> and ->>).
• MariaDB 10.2 supports the standard by producing null and a warning for JSON_SEARCH when given invalid data, while MySQL produces an error.
• MariaDB 10.2 does not support the ngram and MeCab full-text parser plugins - MDEV-10267, MDEV-10268.
• MariaDB 10.2 does not support the MySQL X plugin.
• MariaDB 10.2 does not support the MySQL general table spaces.
• MariaDB 10.2 does not support MySQL 5.7's “native” InnoDB partitioning handler.
• 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.2 does not support MySQL's SELECT MAX_STATEMENT_TIME = N ... syntax - see Aborting Statements that Exceed a Certain Time to Execute.
• The MySQL version of max_statement_time is defined in millseconds, not seconds.
• Also see Incompatibilities between MariaDB 10.1 and MySQL 5.7.