MySQL to MariaDB Migration

SQLines tools can help you assess and convert database schema (DDL), queries and SQL scripts for MySQL to MariaDB migration.

  • SQLines SQL Converter - SQL scripts assessment and conversion tool

Databases:

  • MySQL 8.x, 6.x and 5.x
  • MariaDB 10.x and 5.x

Migration Reference

SQL Language Elements

Operators:

MySQL MariaDB
1 col->"$.path" Extract JSON value Not supported
2 col->>"$.path" Extract JSON value and unquote Not supported

Data Types

JSON data:

MySQL MariaDB
1 JSON JSON data LONGTEXT JSON is an alias for LONGTEXT, can be used in CREATE TABLE

Built-in SQL Functions

Regular expression functions:

MySQL MariaDB
1 REGEXP_LIKE(str, pattern [,mode]) Check for common JSON keys in json1 and json2 Not supported

JSON functions:

MySQL MariaDB
1 JSON_OVERLAPS(json1, json2) Check for common JSON keys in json1 and json2 Not supported
2 JSON_PRETTY(json) Format JSON value Not supported
3 JSON_STORAGE_FREE(json) Get the freed JSON storafe after update Not supported
4 JSON_STORAGE_SIZE(json) Get the size of binary representation of JSON Not supported
5 JSON_TABLE(columns_definition) Produce tabular data from JSON data Not supported

JSON expressions:

MySQL MariaDB
1 CAST('null' AS JSON) Null JSON document Not allowed, requires CAST('null' AS LONGTEXT)

UUID functions:

MySQL MariaDB
1 BIN_TO_UUID(uuid_binary) Convert binary UUID to string Not supported
2 IS_UUID(uuid_string) Check for valid string-format UUID Not supported
3 UUID_TO_BIN(uuid_string) Convert string UUID to binary Not supported

Spatial functions:

MySQL MariaDB
1 DISTANCE(p1, p2) Get the distance between p1 and p2 ST_DISTANCE(p1, p2)
2 MBRCOVEREDBY(p1, p2) Check coverage Not supported
3 ST_BUFFER_STRATEGY(strategy, points) Buffer strategy Not supported
4 ST_DISTANCE_SPHERE(g1, g2 [, radius]) Get the minimum spherical distance Not supported
5 ST_GEOHASH(long, lat, length) Returns a geo hash string Not supported
6 ST_ISVALID(param) Check if param is geometrically valid Not supported
7 ST_LATFROMGEOHASH(geohash) Return the latitude from a geo hash string Not supported
8 ST_LONGFROMGEOHASH(geohash) Return the longitude from a geo hash string Not supported
9 ST_POINTFROMGEOHASH(geohash, id) Return POINT from a geo hash string Not supported
10 ST_SIMPLIFY(g, max_distance) Simplify a geometry Not supported
11 ST_VALIDATE(g) Validate a geometry Not supported

Replication related functions:

MySQL MariaDB
1 GTID_SUBSET(set1, set2) Checks GTIDs in bots sets Not supported
2 GTID_SUBTRACT(set1, set2) Get GTIDs from set1 that not exist in set2 Not supported
3 WAIT_FOR_EXECUTED_GTID_SET(set) Wait until all GTIDs are applied Not supported
4 WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(set) Not supported

Miscellaneous functions:

MySQL MariaDB
1 ANY_VALUE(col) Allow selecting columns that are not in GROUP BY Not supported
2 GROUPING(col) Grouping with GROUP BY WITH ROLLUP Not supported
3 RANDOM_BYTES(length) Generate random length bytes Not supported
4 VALIDATE_PASSWORD_STRENGTH(pwd) Validate plaintext password strength Not supported

SELECT Statement

Converting SQL SELECT statement:

MySQL MariaDB
1 /*+ MAX_EXECUTION_TIME(n) */ Max execution time in milliseconds, query hint Not supported

SQL Statements

Converting SQL statements:

MySQL MariaDB
1 CREATE TABLESPACE name Create a tablespace Not supported
2 CREATE USER name Create an user CREATE USER name … see notes below

CREATE USER statement:

MySQL MariaDB
1 IDENTIFIED WITH mysql_native_password User authentication plugin Not supported
IDENTIFIED WITH caching_sha2_password

System Variables

System variables shown by SHOW GLOBAL VARIABLES statement:

MySQL MariaDB
1 max_execution_time Max execution time for SELECT in milliseconds max_statement_time Time in seconds