Incompatibilities and Feature Differences Between MariaDB 10.4 and MySQL 8.0

, allowing instances, databases or tables to be rolled back to an old snapshot.

Oracle compatibility mode

merged into the server

and

OR REPLACE syntax for statements, such as , , etc

syntax for setting the lock wait timeout.

Number of supported decimals in has increased from 30 to 38

Number of parts of an index increased from 16 to 32.

Added catchall for list partitions

Oracle-style statement

Lots of new

MariaDB than MySQL, including NO PAD collations.

command to reload SSL certificates without server restart.

IF NOT EXISTS clause added to and IF EXISTS clause added to and

. This makes replication notably faster!

New server command, , and a new option, are added to instruct the server to wait for the last binlog event to be sent to all connected slaves before shutting down.

allows one to implement very efficient backups with minimal locking.

Progress reporting for and

gives the EXPLAIN plan for a query running in another thread. MySQL introduced the EXPLAIN FOR CONNECTION syntax to do the same thing.

(including )

and faster calls

MySQL 8 does not support

MySQL 8 does not support the use of as an alias for

Not all are across both MySQL and MariaDB. As of 10.4.14, MariaDB supports 40 character sets and 322 collations (armscii8_general_nopad_ci, armscii8_nopad_bin, ascbig5_chinese_nopad_ci, big5_nopad_bin, iicp1250_general_nopad_ci, cp1250_nopad_bin, cp1250_general_nopad_ci, cp1250_nopad_bin, cp1251_general_nopad_ci, cp1251_nopad_bin, cp1256_general_nopad_ci, cp1256_nopad_bin, cp1257_general_nopad_ci, cp1257_nopad_bin, cp850_general_nopad_ci, cp850_nopad_bin, cp852_general_nopad_ci, cp852_nopad_bin, cp866_general_nopad_ci, cp866_nopad_bin, cp932_japanese_nopad_ci, cp932_nopad_bin, dec8_nopad_bin, dec8_swedish_nopad_ci, eucjpms_japanese_nopad_ci, eucjpms_nopad_bin, eucjpms_japanese_nopad_ci, eucjpms_nopad_bin, euckr_korean_nopad_ci, euckr_nopad_bin, gb2312_chinese_nopad_ci, gb2312_nopad_bin, gbk_chinese_nopad_ci, gbk_nopad_bin, geostd8_general_nopad_ci, geostd8_nopad_bin, greek_general_nopad_ci, greek_nopad_bin, hebrew_general_nopad_ci, hebrew_nopad_bin, hp8_english_nopad_ci, hp8_nopad_bin, keybcs2_general_nopad_ci, keybcs2_nopad_bin, koi8r_general_nopad_ci, koi8r_nopad_bin, koi8u_general_nopad_ci, koi8u_nopad_bin, latin1_nopad_bin, latin1_swedish_nopad_ci, latin2_general_nopad_ci, latin2_nopad_bin, latin5_nopad_bin, latin5_turkish_ci, latin5_turkish_nopad_ci, latin7_general_nopad_ci, latin7_nopad_bin, macce_general_nopad_ci, macce_nopad_bin, macroman_general_nopad_ci, macroman_nopad_bin, sjis_japanese_nopad_ci, sjis_nopad_bin, swe7_nopad_bin, tis620_thai_nopad_ci, tis620_nopad_bin, ucs2_croatian_mysql561_ci, ucs2_general_mysql500_ci, ucs2_general_nopad_ci, ucs2_myanmar_ci, ucs2_nopad_bin, ucs2_swedish_ci, ucs2_thai_520_w2, ucs2_unicode_ci, ucs2_unicode_nopad_ci, ujis_japanese_nopad_ci, ujis_nopad_bin, utf16le_general_nopad_ci, utf16le_nopad_bin, utf16_croatian_mysql561_ci, utf16_general_nopad_ci, utf16_myanmar_ci, utf16_nopad_bin, utf16_thai_520_w2, utf16_unicode_520_nopad_ci, utf16_unicode_nopad_ci, utf32_croatian_mysql561_ci, utf32_general_nopad_ci, utf32_myanmar_ci, utf32_nopad_bin, utf32_thai_520_w2, utf32_unicode_520_nopad_ci, utf32_unicode_nopad_ci, utf8mb4_general_nopad_ci, utf8mb4_myanmar_ci, utf8mb4_nopad_bin, utf8mb4_thai_520_w2, utf8mb4_unicode_520_nopad_ci, utf8mb4_unicode_nopad_ci, utf8_croatian_mysql561_ci, utf8_general_nopad_ci, utf8_myanmar_ci, utf8_nopad_bin, utf8_thai_520_w2, utf8_unicode_520_nopad_ci, utf8_unicode_ci and utf8_unicode_nopad_ci being the additional ones). As of 8.0.21, MySQL supports 41 character sets (gb18030 being the additional one) and 272 collations (gb18030_bin, gb18030_chinese_ci, gb18030_unicode_520_ci, utf8mb4_0900_ai_ci, utf8mb4_0900_as_ci, utf8mb4_0900_as_cs, utf8mb4_0900_bin, utf8mb4_cs_0900_ai_ci, utf8mb4_cs_0900_as_cs, utf8mb4_da_0900_ai_ci, utf8mb4_da_0900_as_cs, utf8mb4_de_pb_0900_ai_ci, utf8mb4_de_pb_0900_as_cs, utf8mb4_eo_0900_ai_ci, utf8mb4_eo_0900_as_cs, utf8mb4_es_0900_ai_ci, utf8mb4_es_0900_as_cs, utf8mb4_es_trad_0900_ai_ci, utf8mb4_es_trad_0900_as_cs, utf8mb4_et_0900_ai_ci, utf8mb4_et_0900_as_cs, utf8mb4_hr_0900_ai_ci, utf8mb4_hr_0900_as_cs, utf8mb4_hu_0900_ai_ci, utf8mb4_hu_0900_as_cs, utf8mb4_is_0900_ai_ci, utf8mb4_is_0900_as_cs, utf8mb4_ja_0900_as_cs, utf8mb4_ja_0900_as_cs_ks, utf8mb4_la_0900_ai_ci, utf8mb4_la_0900_as_cs, utf8mb4_lt_0900_ai_ci, utf8mb4_lt_0900_as_cs, utf8mb4_lv_0900_ai_ci, utf8mb4_lv_0900_as_cs, utf8mb4_pl_0900_ai_ci, utf8mb4_pl_0900_as_cs, utf8mb4_ro_0900_ai_ci, utf8mb4_ro_0900_as_cs, utf8mb4_ru_0900_ai_ci, utf8mb4_ru_0900_as_cs, utf8mb4_sk_0900_ai_ci, utf8mb4_sk_0900_as_cs, utf8mb4_sl_0900_ai_ci, utf8mb4_sl_0900_as_cs, utf8mb4_sv_0900_ai_ci, utf8mb4_sv_0900_as_cs, utf8mb4_tr_0900_ai_ci, utf8mb4_vi_0900_ai_ci, utf8mb4_vi_0900_as_cs, utf8mb4_zh_0900_as_cs being the additional ones).

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.

Users created with MySQL's SHA256 password algorithm cannot be used in MariaDB 10.4 - MDEV-9804.

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.4 does not support MySQL's JSON operators (-> and ->>) - MDEV-13594

MariaDB 10.4 supports the standard by producing null and a warning for when given invalid data, while MySQL produces an error.

• MariaDB never allows authentication via roles, while MySQL permits this.

• MySQL permits activating multiple roles at the same time. MariaDB can achieve the same result by creating an intermediate aggregate role.

• In the , MySQL reports just the direct list of enabled roles, while MariaDB reports the enabled role, plus the effective inherited roles.

• MySQL extends the .

• MySQL includes the tables INFORMATION_SCHEMA.ROLE_TABLE_GRANTS, INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS, INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS, and INFORMATION_SCHEMA ADMINISTRABLE_ROLE_AUTHORIZATIONS.

MySQL has the enabled by default. For performance reasons MariaDB 10.4 has it disabled by default. You can enable it by starting mysqld with the option --performance-schema.

MySQL features a new implementation of the performance_schema and a sys schema wrapper. These are only supported in MariaDB 10.5.

In MariaDB 10.4, using without any table list will only close tables not in use, and tables not locked by the FLUSH TABLES connection. If there are no locked tables, FLUSH TABLES will be instant and will not cause any waits, as it no longer waits for tables in use. When a table list is provided, the server will wait for the end of any transactions that are using the tables. In MySQL, FLUSH TABLES only waits for the statements to complete.

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.

MariaDB 10.4 implements in a different way to MySQL 8.0.

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.4 does not support MySQL's SELECT /*+ MAX_EXECUTION_TIME(n) */ ... - see Aborting Statements that Exceed a Certain Time to Execute.

MySQL 8.0 does not support the .

MariaDB 10.4 does not support the MySQL Memcached plugin (which has been deprecated in MySQL 8.0). 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.

MariaDB 10.4 does not support MySQL 8.0's ALTER TABLE...RENAME INDEX statements (supported in MariaDB 10.5).

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.4, does not quote the DEFAULT value of an integer. MariaDB 10.2 and earlier, and MySQL, do. Since MariaDB 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 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's supports SELECT statements, LOCK TABLES and various DDL statements, while MySQL's NOWAIT only supports SELECT.

MariaDB's cannot be added on and while MySQL's can - MDEV-25247

MariaDB returns an ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction when unable to lock within the time, while MySQL returns ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set

MariaDB does not support table while it is write-locked - MDEV-30814

MariaDB 10.4 does not support Lateral Derived Tables - MDEV-19078.

MariaDB does not support the optional init_vector argument for and or the block_encryption_mode variable - MDEV-9069

MySQL supports SKIP LOCKED, while MariaDB doesn't.

MariaDB does not support the --initialize option. Use instead. - MDEV-19010

MariaDB 10.4 does not support the ngram and MeCab full-text parser plugins - MDEV-10267, MDEV-10268.

MariaDB 10.4 does not support the MySQL X plugin.

MariaDB 10.4 does not support MySQL 8's “native” InnoDB partitioning handler - MDEV-29253

MariaDB 10.4 does not support for InnoDB.

The MySQL 8.0 and MariaDB 10.4 table contain slightly different fields.

The MySQL binary log includes the thread_id, while MariaDB's does not - MDEV-7850

The MariaDB 10.1 syntax supporting Spatial Reference System IDs for spatial data type columns with is not supported by MySQL. MySQL 8 introduced CREATE SPATIAL REFERENCE SYSTEM, which is not supported by MariaDB - MDEV-29953

MariaDB 10.4 does not support the RESTART statement - MDEV-30813

uses a different compression algorithm, and different system variables to manage.

MariaDB 10.4 does not support the SELECT FOR UPDATE and FOR SHARE locks - MDEV-17514

Also see Incompatibilities between MariaDB 10.3 and MySQL 5.7 and Incompatibilities between MariaDB 10.2 and MySQL 5.7.