Key takeaways
- Oracle compatibility mode helps MariaDB Enterprise Server interpret many Oracle-style SQL statements.
- MariaDB MaxScale can rewrite specific parts of live queries before they reach the MariaDB server.
- Regex filters can address targeted syntax differences without modifying or redeploying the application.
- The MariaDB Migration Portal can help teams assess and plan an Oracle-to-MariaDB migration before testing begins.
Migrating from Oracle to MariaDB can reduce the amount of application SQL that needs to be rewritten by using MariaDB’s Oracle compatibility mode and MaxScale query rewriting. MariaDB provides compatibility features that can simplify migrations from Oracle, SQL Server, PostgreSQL and other database systems.
The setup in this article uses MariaDB Enterprise Platform, which includes MariaDB Enterprise Server and MariaDB MaxScale.
This guide walks through a typical migration from Oracle to MariaDB. It demonstrates how MariaDB MaxScale, MariaDB’s smart database proxy, can rewrite Oracle-style syntax in live queries, together with the Oracle compatibility mode available in MariaDB Enterprise Server.
Compatibility mode is available for on-premises deployments.
Prerequisites
This migration process relies on MariaDB Enterprise Server and MariaDB MaxScale. MaxScale configuration is created through the MaxScale GUI.
Because installation requirements vary, this guide does not cover the full installation process. Refer to the MariaDB Enterprise documentation for:
- Install MariaDB Enterprise Server and Enterprise Backup
- Install MariaDB MaxScale
- Getting started with the MariaDB MaxScale GUI
- SQL_MODE — MariaDB Enterprise documentation
Before digging into the data and structure, MariaDB recommends running a migration assessment in the “Migration Portal” within the MariaDB customer portal. The Migration Portal has a variety of resources to assist you in migrating to MariaDB.
In the “New Migration” section, select the database you are migrating from to receive a customized assessment and planning resources.
Step 1: Verify the current SQL MODE value
Because variables vary and can be adjusted by external software, it’s worth double checking that SQL_MODE is running.
Verify the current SQL mode from a Linux terminal:
sudo mariadb -e "show global variables like 'sql_mode';"
In our example, it is showing the default configuration of the SQL_MODE variable:
+---------------+-------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +---------------+-------------------------------------------------------------------------------------------+
Step 2: Test an Oracle-style table definition
Next, try to create a table using Oracle syntax while the default SQL_MODE is active:
MariaDB [oracle_migration]> CREATE TABLE "customers" (
"CUST_ID" NUMBER(8,0),
"FIRST_NAME" VARCHAR2(50 BYTE),
"LAST_NAME" VARCHAR2(50 BYTE),
"ADDRESS1" VARCHAR2(128 BYTE)
);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near '"customers" ("CUST_ID" NUMBER(8,0), "FIRST_NAME" VARCHAR2(50 BYTE), "LAST_NAM...' at line 1This syntax can’t be recognized by default on the MariaDB client. Many items prevent this creation, for example, the double quotes.
Step 3: Enable Oracle compatibility mode
Change session SQL_MODE to Oracle compatibility mode:
SET SESSION sql_mode='ORACLE'; Query OK, 0 rows affected (0.035 sec)
Try and create the same table again:
MariaDB [oracle_migration]> CREATE TABLE "customers" ( "CUST_ID" NUMBER(8,0), "FIRST_NAME" VARCHAR2(50 BYTE), "LAST_NAME" VARCHAR2(50 BYTE), "ADDRESS1" VARCHAR2(128 BYTE) ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BYTE), "LAST_NAME" VARCHAR2(50 BYTE), "ADDRESS1" VARCHAR2(128 BYTE))' at line 1
This time, MariaDB returns a different error. The remaining issue is the BYTE keyword used.
So what can you do about the BYTE keyword?
We can rely on MariaDB MaxScale capabilities to remove and update the syntax on live queries.
MaxScale 2.5 includes a smart GUI that enables easily monitoring and configuring MaxScale on live traffic. The next step will be about how to use the MariaDB MaxScale GUI.
Step 4: Access the MaxScale GUI
MaxScale allows you to define a rule that rewrites a live query before it reaches a MariaDB server. This is useful when queries need to be adjusted during migration without changing the application.
These filters can also help correct typos in application queries without requiring a deployment or affecting the production environment.

Once configured, the MaxScale GUI is available on port 8989 by default. Open the following address: https://127.0.0.1:8989.
After signing in, open the MaxScale monitoring page and select the relevant service.

MaxScale configuration is made of several modules. All these modules are linked in what we call “Services”. We can see on the previous screenshot that a service is linking several servers, a monitoring system, a listening port, a routing rule, and potentially many filters.
In this case, only one service is defined: the “Read-Write Service”. Select its name to open the service definition.

Step 5: Create a regex filter
Using a Regex filter on MariaDB MaxScale allows you to rewrite live queries.
First, go back to the MaxScale overview screen, click on + Create New, then configure a new filter:

Choose regexfilter as the filter type. Give the filter a name that describes its purpose.
In the match parameter, replace the originated value with: BYTE\)
To match a special character like parenthesis, remember to escape it by adding a backslash as shown.
Complete the replace box with the desired value. In this case, it is parenthesis without escaping the special character.
Validate the new rule after entering the values.
Step 6: Associate the filter with the service
Now that the new filter has been created, go back to the service definition page and click on + Add Filters button on the Filters section.

On the Add filters box, enable the newly created filter to associate it with the current service and listener.
Once done, we can test our CREATE TABLE command again.
Step 7: Test the new rule
Use the mariadb command line to access the MaxScale service.
$ mariadb -hmaxscale1.example.com -P4006 -utest -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 10.5.9-6-MariaDB-enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Once well connected through the MaxScale interface, we can now test the newly created rewrite rule.
Let’s use the previously created database:
MariaDB [(none)]> use oracle_migration Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
Enable Oracle compatibility mode for the session:
MariaDB [(none)]> SET SESSION sql_mode='ORACLE'; Query OK, 0 rows affected (0.004 sec)
Create this table with the exact same syntax:
MariaDB [oracle_migration]> CREATE TABLE "customers" ( "CUST_ID" NUMBER(8,0), "FIRST_NAME" VARCHAR2(50 BYTE), "LAST_NAME" VARCHAR2(50 BYTE), "ADDRESS1" VARCHAR2(128 BYTE) ); Query OK, 0 rows affected (0.044 sec)
The command completes successfully:
Query OK, 0 rows affected (0.044 sec)
The application was not modified. MaxScale rewrote the query before it reached MariaDB, allowing the table to be created successfully.
What this migration approach demonstrates
- Oracle compatibility mode and MaxScale address different parts of the migration process:
- Compatibility mode helps MariaDB interpret Oracle-style SQL.
- MaxScale regex filters rewrite specific syntax differences in live traffic.
- The application can continue sending its existing query while migration work continues.
Additional filters can be created for other syntax differences identified during testing. Each rule should be tested carefully against representative queries before being used in a production migration.
Next steps for your Oracle-to-MariaDB migration
A successful migration starts with understanding which Oracle features your application uses and where syntax differences may affect execution. Run a migration assessment, enable Oracle compatibility mode, and use targeted MaxScale filters to test how existing queries behave before planning the final cutover.
Frequently asked questions
In some cases, yes. MariaDB’s Oracle compatibility mode and MaxScale query filters can reduce the number of application changes required, although the level of compatibility depends on the application’s SQL, database features and implementation.
Oracle compatibility mode changes how MariaDB interprets certain SQL syntax and database behaviors so that more Oracle-style statements can run on MariaDB.
MariaDB MaxScale is a database proxy that can route and modify database traffic between applications and MariaDB servers. In this article, it is used to rewrite Oracle-specific SQL syntax before it reaches MariaDB.
MaxScale can rewrite targeted SQL syntax in live queries, which can reduce the need to modify application code for specific compatibility issues.
No. Regex filters are best suited to targeted, well-understood query changes. Test each rule against representative queries and consider making permanent application or schema changes where that provides a clearer long-term solution.