The Problem: Valid SQL Fails on MariaDB 11.8 but Works on 10.6

When migrating database schemas from older MariaDB versions (like 10.6) to newer releases (such as MariaDB 11.8 Enterprise), you might encounter a baffling syntax error when deploying stored functions. Even though your SQL syntax is standard and runs perfectly on your development/SIT environments, running it in UAT or Production throws an error like this:

java.sql.SQLSyntaxErrorException: (conn=103289) 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 'RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN LENGTH(p_NodeKey) - LENGTH(...' at line 4

This error occurs even when running the script manually in database clients like HeidiSQL, DBeaver, or command-line clients. What changed in MariaDB 11.8 to break this code?

The Root Cause: sql_mode = 'ORACLE'

The parser error near 'RETURNS INT' points directly to how the SQL parser is interpreting your code. In standard MariaDB/MySQL syntax, RETURNS (plural) is the correct keyword to define a function's return type.

However, in MariaDB Enterprise and newer community versions, there is an Oracle compatibility mode (sql_mode=ORACLE) that changes the SQL parser rules. In Oracle PL/SQL syntax:

  • The keyword to define a return type is RETURN (singular), not RETURNS.
  • The parser expects Oracle PL/SQL structure (e.g., using AS or IS instead of LANGUAGE SQL).

If your target MariaDB 11.8 server has sql_mode set to include ORACLE (which is common in enterprise environments migrating from legacy systems), your standard MariaDB function syntax will fail with a syntax error at the RETURNS keyword.

How to Verify Your SQL Mode

To confirm if this is the issue, run the following query on your MariaDB 11.8 server:

SELECT @@sql_mode;

If the output contains ORACLE, the database is running in Oracle compatibility mode, which is why your standard script is failing.

The Solutions

Solution 1: Temporarily Reset sql_mode in Your Script (Recommended)

If you want to keep your standard MariaDB syntax and ensure portability across different environments, the cleanest approach is to temporarily change the sql_mode for the active session inside your Liquibase script or SQL tool.

Update your Liquibase changeset to look like this:

--changeset test:20260120_fn_GetLevel splitStatements:false endDelimiter://
--comment: Create fn_GetLevel function safely

/* Save current SQL mode and temporarily switch to standard MariaDB mode */
SET @old_sql_mode = @@sql_mode;
SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

CREATE FUNCTION `fn_GetLevel`(
    p_NodeKey VARCHAR(1000)
)
RETURNS INT
DETERMINISTIC
NO SQL
BEGIN
    RETURN LENGTH(p_NodeKey) - LENGTH(REPLACE(p_NodeKey, '/', '')) - 1;
END //

/* Restore the original SQL mode */
SET sql_mode = @old_sql_mode;//
--rollback DROP FUNCTION IF EXISTS `fn_GetLevel`;

Note: Ensure your endDelimiter is set correctly (e.g., //) so Liquibase doesn't split the statements on semicolons prematurely.

Solution 2: Rewrite the Function in Oracle PL/SQL Syntax

If your organization requires the database to run strictly in ORACLE mode and you want your codebase to match, you must rewrite the function using Oracle-compatible syntax:

--changeset test:20260120_fn_GetLevel_oracle splitStatements:false endDelimiter:/
CREATE OR REPLACE FUNCTION `fn_GetLevel`(
    p_NodeKey VARCHAR2
)
RETURN INTEGER
AS
BEGIN
    RETURN LENGTH(p_NodeKey) - LENGTH(REPLACE(p_NodeKey, '/', '')) - 1;
END;
/

Solution 3: Permanently Change the Server Configuration

If the ORACLE SQL mode was enabled by accident during the migration to MariaDB 11.8, you should modify the server configuration file (my.cnf or my.ini) to remove ORACLE from the sql_mode directive and restart the service:

[mariadb]
sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Summary

When migrating to newer MariaDB versions, unexpected syntax errors on functions are almost always caused by a mismatch in sql_mode configurations between environments. By temporarily resetting the session's sql_mode during deployment, you can ensure your Liquibase migrations remain robust, backward-compatible, and environment-agnostic.