Incompatibilities and Feature Differences Between MariaDB 10.2 and MySQL 5.7

You are viewing an old version of this article. View the current version here.

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:

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:

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.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.