# 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](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) and MySQL 5.7

## Storage Engines

In addition to the standard [InnoDB](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/innodb), [MyISAM](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/myisam-storage-engine), [BLACKHOLE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/blackhole), [CSV](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/csv), [MEMORY](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/memory-storage-engine), [ARCHIVE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/archive), and [MERGE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/merge) storage engines, the following are also available with [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102):

* [MyRocks](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/myrocks), a storage engine with great compression
* [Aria](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/aria), MyISAM replacement with better caching.
* [TokuDB](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/legacy-storage-engines/tokudb)
* [CONNECT](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/connect)
* [SEQUENCE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/sequence-storage-engine)
* [SphinxSE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/sphinx-storage-engine)
* [Spider](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/spider)
* [FederatedX](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/federatedx-storage-engine) (drop-in replacement for Federated)
* [OQGRAPH](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/oqgraph-storage-engine)

## Speed Improvements

* Many optimizer enhancements. [Subqueries](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-manipulation/selecting-data/subqueries) are more usable.\
  The complete list and a comparison with MySQL is [here](https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/optimizer-feature-comparison-matrix).
* Faster and safer replication:[Group commit for the binary log](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-management/server-monitoring-logs/binary-log/group-commit-for-the-binary-log). This makes many setups that use replication and lots of updates [more than 2x times faster](https://www.facebook.com/note.php?note_id=10150211546215933).
* [Improvements](https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/broken-reference) for Innodb asynchronous IO subsystem on Windows.
* [Segmented Key Cache](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/ha-and-performance/optimization-and-tuning/system-variables/segmented-key-cache) for MyISAM. Can speed up MyISAM tables with up to 4x
* [Adjustable hash size](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/myisam-storage-engine/myisam-system-variables#key_cache_file_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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/table-statements/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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/ha-and-performance/optimization-and-tuning/buffers-caches-and-threads/thread-pool/thread-pool-in-mariadb) 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/storage-engines/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](https://github.com/mariadb-corporation/docs-release-notes/blob/test/kb/en/what-is-in-the-different-mariadb-releases/README.md). 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](https://github.com/mariadb-corporation/docs-release-notes/blob/test/kb/en/galera-cluster/README.md) is a standard part of MariaDB Server.
* [Window functions](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/special-functions/window-functions)
* Number of supported decimals in [DECIMAL](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/numeric-data-types/decimal) has increased from `30` to `38`
* [Recursive Common Table Expressions](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/recursive-common-table-expressions-overview)
* [DML-only flashback](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-management/server-monitoring-logs/binary-log/flashback), allowing instances, databases or tables to be rolled back to an old snapshot.
* New [WITH](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/with) statement. `WITH` is a common table expression that allows you to refer to a subquery expression many times in a query.
* [CHECK CONSTRAINT](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-definition/constraint)
* [DEFAULT expression](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/tables/create-table#default), including `DEFAULT` for [BLOB](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/blob) and [TEXT](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/text)
* Added catchall for [list partitions](https://github.com/mariadb-corporation/docs-server/blob/test/release-notes/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/broken-reference/README.md)
* Oracle-style [EXECUTE IMMEDIATE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/prepared-statements/execute-immediate) statement
* Lots of new [JSON functions](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/special-functions/json-functions)
* [Microsecond Precision in Processlist](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/system-tables/information-schema/time_ms-column-in-information_schemaprocesslist)
* [Table Elimination](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/ha-and-performance/optimization-and-tuning/query-optimizations/table-elimination)
* [Virtual Columns](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-definition/create/generated-columns)
* [Extended User Statistics](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/ha-and-performance/optimization-and-tuning/query-optimizations/statistics-for-optimizing-queries/user-statistics)
* [KILL all queries for a user](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/administrative-sql-statements/kill)
* [KILL QUERY ID - terminates the query by query\_id, leaving the connection intact](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/administrative-sql-statements/kill)
* [Pluggable Authentication](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/plugins/authentication-plugins/pluggable-authentication-overview)
* [Storage-engine-specific CREATE TABLE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/product-development/plugin-development/storage-engines-storage-engine-development/engine-defined-new-tablefieldindex-attributes)
* [Enhancements to INFORMATION SCHEMA.PLUGINS table](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/system-tables/information-schema/information-schema-tables/plugins-table-information-schema)
* [Group commit for the binary log](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-management/server-monitoring-logs/binary-log/group-commit-for-the-binary-log). This makes [replication notably faster!](https://www.facebook.com/note.php?note_id=10150261692455933)
* The binary log in MariaDB [can be compressed](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-management/server-monitoring-logs/binary-log/compressing-events-to-reduce-size-of-the-binary-log).
* [Progress reporting](https://github.com/mariadb-corporation/docs-server/blob/test/release-notes/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/broken-reference/README.md) for [ALTER TABLE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-definition/alter/alter-table) and [LOAD DATA INFILE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-manipulation/inserting-loading-data/load-data-into-tables-or-index/load-data-infile)
* Faster [joins and subqueries](https://github.com/mariadb-corporation/docs-server/blob/test/release-notes/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/broken-reference/README.md)
* [HandlerSocket](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-structure/nosql/handlersocket) and faster [HANDLER](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-structure/nosql/handler) calls
* [Dynamic Columns](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-structure/nosql/dynamic-columns) support
* [SHOW EXPLAIN](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/administrative-sql-statements/show/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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/security/user-account-management/roles)
* [PCRE Regular Expressions](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/string-functions/regular-expressions-functions/pcre) (including [REGEXP\_REPLACE()](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/string-functions/regular-expressions-functions/regexp_replace))
* [CREATE OR REPLACE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/tables/create-table#create-or-replace)
* [CREATE OR REPLACE DATABASE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-definition/create/create-database#or-replace)
* [DELETE ... RETURNING](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-manipulation/changing-deleting-data/delete)
* MariaDB [supports expressions](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/tables/create-table#default) in the DEFAULT clause, while MySQL does not.
* MariaDB [supports more collations](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/character-sets/supported-character-sets-and-collations) than MySQL, including `NO PAD` collations.
* MariaDB supports Spatial Reference System IDs for spatial data type columns with [REF\_SYSTEM\_ID](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/tables/create-table#ref_system_id).

## Incompatibilities

When upgrading from MySQL 5.7 to [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102), please take note of the following incompatibilities:

* For a list of function differences, see [Function Differences Between MariaDB 10.2 and MySQL 5.7](https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/function-differences-between-mariadb-and-mysql/function-differences-between-mariadb-and-mysql-unmaintained-series/function-differences-between-mariadb-102-and-mysql-57)
* For a list of system variable differences, see [System Variable Differences Between MariaDB 10.2 and MySQL 5.7](https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/system-variable-differences-between-mariadb-and-mysql/system-variable-differences-between-mariadb-and-mysql-unmaintained-series/system-variable-differences-between-mariadb-10-2-and-mysql-5-7)
* MariaDB binaries (`mysqld`, [myisamchk](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/clients-and-utilities/myisam-clients-and-utilities/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](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102). However [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/ha-and-performance/standard-replication/gtid#system-variables-for-global-transaction-id), 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/tables/create-table#create-or-replace) 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/ha-and-performance/standard-replication/replication-and-binary-log-system-variables) variable to specify how `CREATE TABLE` and `DROP TABLE` is replicated.
* MySQL has the [performance schema](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/system-tables/performance-schema) enabled by default. For performance reasons [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) has it disabled by default. You can enable it by starting `mysqld` with the option `--performance-schema`.
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) implements [InnoDB encryption](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/security/encryption/data-at-rest-encryption/data-at-rest-encryption-overview) in a different way to MySQL 5.7.
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support [CREATE TABLESPACE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-definition/create/create-tablespace) for InnoDB.
* The OVER, ROWS and RECURSIVE keywords are [reserved words](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-structure/sql-language-structure/reserved-words) in [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102), but not in MySQL 5.7. Note that in MySQL 8.0 these are also reserved words.
* MariaDB stores [JSON](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/special-functions/json-functions) 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/json) is an alias for [LONGTEXT](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/clients-and-utilities/legacy-clients-and-utilities/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](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support MySQL's JSON operators (`->` and `->>`) ([MDEV-13594](https://jira.mariadb.org/browse/MDEV-13594))
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) supports the standard by producing null and a warning for [JSON\_SEARCH](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/special-functions/json-functions/json_search) when given invalid data, while MySQL produces an error.
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support the ngram and MeCab full-text parser plugins - [MDEV-10267](https://jira.mariadb.org/browse/MDEV-10267), [MDEV-10268](https://jira.mariadb.org/browse/MDEV-10268).
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support the [MySQL X plugin](https://dev.mysql.com/doc/refman/5.7/en/x-plugin.html).
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support MySQL 5.7's “native” InnoDB partitioning handler.
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support MySQL 5.7's ALTER TABLE...RENAME INDEX statements ([MariaDB 10.5 does](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/data-definition/alter/alter-table#rename-indexkey)).
* MySQL's implementation of [aborting statements that exceed a certain time to execute](https://github.com/mariadb-corporation/docs-release-notes/blob/test/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/aborting-statement/README.md) can only kill SELECTs, while MariaDB's can kill any queries (excluding stored procedures).
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) does not support MySQL's `SELECT MAX_STATEMENT_TIME = N ...` syntax - see [Aborting Statements that Exceed a Certain Time to Execute](https://github.com/mariadb-corporation/docs-release-notes/blob/test/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/aborting-statement/README.md).
* The MySQL version of [max\_statement\_time](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-management/variables-and-modes/server-system-variables#max_statement_time) is defined in millseconds, not seconds.
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) 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](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) - [MDEV-9804](https://jira.mariadb.org/browse/MDEV-9804).
* [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) doesn't support user [ACCOUNT LOCKs](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/security/user-account-management/account-locking) or [PASSWORD EXPIRE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/security/user-account-management/user-password-expiry) ([MariaDB 10.4](https://github.com/mariadb-corporation/docs-server/blob/test/release-notes/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/broken-reference/README.md) 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/string-functions/cast) and [Hexadecimal Literals](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-structure/sql-language-structure/hexadecimal-literals) for more details and examples.
* In [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102), [SHOW CREATE TABLE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/administrative-sql-statements/show/show-create-table) does not quote the DEFAULT value of an integer. Older versions of MariaDB, and MySQL, do. Since [MariaDB 10.2](https://mariadb.com/docs/release-notes/community-server/old-releases/10.2/what-is-mariadb-102) can support defaults for [BLOB](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/blob) and [TEXT](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/text) fields, while MySQL does not, [SHOW CREATE TABLE](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-statements/administrative-sql-statements/show/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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/server-usage/tables/create-table#default) in the DEFAULT clause, in MariaDB, the [INFORMATION\_SCHEMA.COLUMNS](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/system-tables/information-schema/information-schema-tables/information-schema-columns-table) 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](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/secondary-functions/encryption-hashing-and-compression-functions/aes_encrypt) and [AES\_DECRYPT](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/sql-functions/secondary-functions/encryption-hashing-and-compression-functions/aes_decrypt) or the block\_encryption\_mode variable - [MDEV-9069](https://jira.mariadb.org/browse/MDEV-9069)
* MariaDB does not support the `--initialize` option. Use [mysql\_install\_db](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/clients-and-utilities/legacy-clients-and-utilities/mysql_install_db) instead. - [MDEV-19010](https://jira.mariadb.org/browse/MDEV-19010)
* Not all [character sets and collations](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/character-sets) are [supported](https://app.gitbook.com/s/SsmexDFPv2xG2OTyO5yV/reference/data-types/string-data-types/character-sets/supported-character-sets-and-collations) 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](https://github.com/mariadb-corporation/docs-server/blob/test/release-notes/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-and-mysql-unmaint/broken-reference/README.md).

{% @marketo/form formid="4316" formId="4316" %}