If you are migrating a database from MariaDB to MySQL HeatWave on OCI, you must use the MySQL Shell (mysqlsh) utilities.

The Catch: MariaDB allows inline JSON array defaults (like DEFAULT []), but MySQL strictly requires functional expressions (DEFAULT (json_array())). If your MariaDB database uses JSON columns, the raw export will fail to import into HeatWave.

Here is the exact process to install the tools, export the database, patch the JSON syntax, and import it.

Step 0: Install MySQL Shell (mysqlsh)

If you do not have MySQL Shell installed on your server, install it via your package manager.

For Ubuntu/Debian:

sudo apt update  
sudo apt install mysql-shell -y

For CentOS/RHEL/Oracle Linux:

Bash

sudo yum install mysql-shell -y

Step 1: Export the MariaDB Instance

Connect to your source server and open the MySQL Shell. Run the dump utility using the ocimds: true flag to automatically apply basic cloud compatibility fixes.

sudo mysqlsh --no-defaults
\connect local_user@localhost:3306
util.dumpInstance('/data/mysqlheatwave-export', {
 threads: 8,
 ocimds: true,
 users: false,
 compatibility: ['strip_restricted_grants', 'strip_definers', 'force_innodb', 'create_invisible_pks']
})

Step 2: Fix the JSON Array Syntax

Exit the MySQL shell and run these sed commands directly on your Linux terminal. This searches your exported SQL files and converts the MariaDB JSON syntax into the format MySQL requires.

# Wrap existing json_array functions in parentheses

sudo find /data/mysqlheatwave-export/ -type f -exec sed -i 's/DEFAULT json_array()/DEFAULT (json_array())/g' {} +
# Convert literal string arrays to the json_array() function

sudo find /data/mysqlheatwave-export/ -type f -exec sed -i "s/NOT NULL DEFAULT '\\\\'\[\]\\''/NOT NULL DEFAULT (json_array())/g" {} +

Important Note: If your import still throws an error specifically pointing to a DEFAULT [] constraint, the sed commands may have missed a custom formatting edge case. You will need to manually open the specific .sql file mentioned in the error, search for DEFAULT [], and either remove it entirely or replace it with DEFAULT (json_array()).

Step 3: Import to MySQL HeatWave

Open the MySQL Shell again and connect to your HeatWave instance's private IP to load the patched dump.

\connect remote_user@remote_host:3306

util.loadDump('/data/mysqlheatwave-export/', {
 threads: 8,
 ignoreVersion: true,
 ignoreExistingObjects: true
})

Migrating mission-critical databases and securing compromised environments requires precision. If your team needs hands-on support navigating complex cloud infrastructure, disaster recovery, or database migrations, the DevSecOps engineers at NextMark can help you architect a secure, high-availability system.