Skip to main content
ANNOUNCEMENTMeet MariaDB Enterprise Platform 2027. Read more.

Menu

WEBINAR

Built for the Agentic Era: Introducing MariaDB Enterprise Platform 2027

Watch now

Product

Migrating from Oracle to MariaDB: A Practical Guide to SQL Mode and MaxScale

By Sebastien Giraud

8 min read
Share

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.
sudo mariadb -e "show global variables like 'sql_mode';"
 +---------------+-------------------------------------------------------------------------------------------+
 | Variable_name | Value                                                                                     |
 +---------------+-------------------------------------------------------------------------------------------+
 | sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 +---------------+-------------------------------------------------------------------------------------------+
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 1
SET SESSION sql_mode='ORACLE';
Query OK, 0 rows affected (0.035 sec)
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
MaxScale GUI
MaxScale GUI monitoring page
MaxScale Red-Write-Service
MaxScale Regex filter
Add filters rule to 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)]>
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
MariaDB [(none)]> SET SESSION sql_mode='ORACLE';
Query OK, 0 rows affected (0.004 sec)
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)

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.