MySQL to MariaDB Migration Compatibility

Connecting

All testing has been performed through a database connection. Both MySQL and MariaDB provide a command-line client and a range of language-specific connectors, in addition to a broader range of language-specific connectors and third-party clients available from the MySQL and MariaDB community.

Many community-sourced options, such as the database connector in the PHP language, have been community tested with both MySQL and MariaDB.

MariaDB Corporation provides vendor support for:

Additional information on MariaDB clients and connectors may be found in the MariaDB Enterprise Documentation.

Additional Observations

When moving from earlier MySQL releases to MySQL 8.0 there is a big change in the default user authorization
(caching_sha2_password) which can cause issues with scripts and programs. This often requires upgrades in the connection libraries and changes in apps to deal with it, since it does require an SSL connection. Some people instead take steps to change either the default password authentication or each user’s password authenticator to mysql_native_password. Customers may wish to evaluate whether the client and connector compatibility with MariaDB presents a reduced migration burden.

SQL Modes

In MariaDB and MySQL, SQL Modes are used to override default database server behavior in areas such as query processing, error handling, and data handling. The default SQL Mode differs between products and versions. Specific customer testing should occur based on the SQL Modes they currently use, with an understanding of how these may need to change when changing database products and versions.

We observed the following SQL_MODE defaults:

Additional Observations

MySQL 5.7 has deprecated various SQL_MODE strings that are being combined, such as making strict table settings also imply strict dates and strict division-by-0. This may impact customers whose applications need to use STRICT_TRANS_TABLES while also using ‘0000-00-00’ dates. In MySQL 5.7, this generates a bunch of warnings, which for MySQL 8.0 have turned into errors since the deprecated strings were removed.
It is our further understanding that NO_AUTO_CREATE_USER is the default behavior in MySQL 8.0, but that this SQL_MODE is no longer recognized.

Customers may wish to evaluate whether the fine-grained SQL_MODE behavior available in MariaDB presents a reduced migration burden.

For a full list of MariaDB Server SQL Modes, see the MariaDB Public Knowledge Base.
For a full list of MySQL Database SQL Modes, see the MySQL Documentation.

Data Import

We observed the following differences while testing data imports in default SQL Mode:

• MySQL 8.0 has deprecated ZEROFILL.
• MySQL 8.0 has deprecated specifying precision in FLOAT, DOUBLE, and DECIMAL column specifications.
• MySQL 8.0 has a new default (compiled-in) character set of utf8mb4 and a collation of utf8mb4_0900_ai_ci (instead of the old defaults of latin1 and latin1_swedish_ci).
• MariaDB Enterprise Server has character-set default of latin1 and collation default of latin1_swedish_ci,
  though some distribution-specific packages ship with overrides in the config files.
• MySQL 5.7 and above have an explicit JSON field type whereas MariaDB Enterprise Server translates a JSON field request into a LONGTEXT with extra options. MariaDB Enterprise Server 10.4 and above support check constraints on JSON fields.
• MySQL 8.0 deprecates the “&&”, “!”, and “||” (the OR alias) operators.
• MySQL 8.0 warns users about using NATIONAL, NCHAR, and NVARCHAR types because those types currently imply a CHAR or VARCHAR field with a character set of utf8mb3 while a future release is going to change them to imply the utf8mb4 character set.
• MySQL’s DATE_ADD() function turns 0000-01-01 into 0000-00-00 while MariaDB turns it into NULL. Other
  invalid dates are turned into NULL on both.

Schema Display

The same method was used for both MySQL and MariaDB to import data, and the same method was used for both platforms to view schema. Schema viewing is a common action when validating that the behavior of two platforms is the same.

We observed several differences in default SQL Mode:

• MySQL 8.0 has removed integer field digit sizes, so if you specify an INT, an INT1 … INT8, etc. with or without a “(NUM)” suffix, the resulting field has no “(NUM)” suffix when displaying the schema
• MySQL doesn’t show the “DEFAULT NULL” qualifier for various TEXT, BLOB, and JSON column types even when it is in effect. While this appears cosmetic, it may cause confusion for someone trying to compare schema.
• MySQL puts extra parens into the display of a MULTIPOINT() value in a geometry field whereas MariaDB does not (e.g., “MULTIPOINT((0 0),(1 1))”). This differs from the output of other objects that contain multiple
  points, such as POLYGON(), which displays without the extra parentheses in both.
• MySQL renders a timestamp field with a current-time default as: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP while MariaDB renders it using functions: DEFAULT current_timestamp() ON UPDATE current_timestamp().
• In the Northwind extended example data there is a table that MySQL dumps in a different order from MariaDB: EmployeeTerritories. MariaDB dumps it in its primary key order (the int + varchar in the table) whereas MySQL dumps it in the order of a foreign key, FK_EmployeeTerritories_Territories (as revealed by
  EXPLAIN).
• MySQL quotes numbers in default values, so a schema

Character Sets and Collation

Character sets allow users to store characters from many different languages in the database. Some character sets also support special characters like currency symbols, mathematical symbols, musical symbols and emoji.

Using names as an example, the character set defines which characters can appear in each name. The collation defines the order in which the names would be sorted, and whether two names would be equal:

• In a case-insensitive collation, “Mueller” and “mueller” would be considered the same name.
• In a case-sensitive collation, “Mueller” and “mueller” would be considered different names.
• In a German collation, “Mueller” and “Müller” would be considered the same name.
• In an English collation, “Mueller” and “Müller” would be considered different names.
• In a collation that ignores padding, “Mueller” and “Mueller ” would be considered the same name.
• In a no-pad collation, “Mueller” and “Mueller ” would be considered different names.

Character set and collation may be overridden. Differences in default behavior should be a migration consideration.

Data Types

MariaDB Enterprise Server and MySQL Community Edition support a wide range of data types.

Numeric Data Types

Character String Types

LOB Data Types

Data and Time

GIS (Geometric) Data Types

SET and ENUM

Operators