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 InnoDB, 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

Speed Improvements

• Many optimizer enhancements. Subqueries are more usable. The complete list and a comparison with MySQL is 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.
• 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:

• Galera is a standard part of MariaDB Server.
• Window functions
• Number of supported decimals in DECIMAL has increased from 30 to 38
• Recursive Common Table Expressions
• DML-only flashback, allowing instances, databases or tables to be rolled back to an old snapshot.
• 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!
• The binary log in MariaDB can be compressed.
• 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. MySQL introduced the EXPLAIN FOR CONNECTION syntax to do the same thing.
• Roles
• PCRE Regular Expressions (including REGEXP_REPLACE())
• CREATE OR REPLACE
• CREATE OR REPLACE DATABASE
• DELETE ... RETURNING
• MariaDB supports expressions in the DEFAULT clause, while MySQL does not.
• MariaDB supports more collations than MySQL, including NO PAD collations.
• MariaDB supports Spatial Reference System IDs for spatial data type columns with REF_SYSTEM_ID.

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
• MariaDB binaries (mysqld, myisamchk 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.
• 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 CREATE TABLESPACE for InnoDB.
• The OVER, ROWS and RECURSIVE keywords are reserved words in MariaDB 10.2, but not in MySQL 5.7. Note that in MySQL 8.0 these are also reserved words.
• 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 either convert them to TEXT or use mysqldump 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.2 does not support MySQL's JSON operators (-> and ->>) (MDEV-13594)
• 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 MySQL 5.7's “native” InnoDB partitioning handler.
• MariaDB 10.2 does not support MySQL 5.7's ALTER TABLE...RENAME INDEX statements (MariaDB 10.5 does).
• 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.
• MariaDB 10.2 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.2 - MDEV-9804.
• MariaDB 10.2 doesn't support user ACCOUNT LOCKs or PASSWORD EXPIRE (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 CAST and Hexadecimal Literals for more details and examples.
• In MariaDB 10.2, SHOW CREATE TABLE does not quote the DEFAULT value of an integer. Older versions of MariaDB, and MySQL, do. Since MariaDB 10.2 can support defaults for BLOB and TEXT fields, while MySQL does not, SHOW CREATE TABLE will also append DEFAULT NULL where no default is explicitly provided to nullable BLOB or TEXT fields in MariaDB.
• Since MariaDB supports expressions in the DEFAULT clause, in MariaDB, the INFORMATION_SCHEMA.COLUMNS 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.
• MariaDB does not support the optional init_vector argument for AES_ENCRYPT and AES_DECRYPT or the block_encryption_mode variable - MDEV-9069
• MariaDB does not support the --initialize option. Use mysql_install_db instead. - MDEV-19010
• Not all character sets and collations are supported across both MySQL and MariaDB. As of 10.2.27, 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.
• Also see Incompatibilities between MariaDB 10.1 and MySQL 5.7.