MyCATApache/Mycat-Server · error · SQLNonTransientException

You have an error in your SQL syntax; check the manual that…

Error message

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '{expr}' at line 1

What it means

When parsing ADDDATE (and similar date functions), the handler validates argument structure; invalid arguments cause throwSyntaxError to raise a SQLNonTransientException that mimics MySQL's native syntax error, pointing at the offending expression. It signals the function call is malformed for MySQL semantics, not just unsupported.

Solutions

Correct the function call to MySQL's valid form: ADDDATE(date, INTERVAL n DAY) or ADDDATE(date, n)
Compute the date in the application and embed a literal value
Check the exact expression named in the error for syntax/argument errors

Example fix

// before
ADDDATE('2024-01-01')
// after
ADDDATE('2024-01-01', INTERVAL 1 DAY)

Defensive patterns

Strategy: validation

Validate before calling

// validate ADDDATE shape before parsing
if (args.length != 2 || !(args[1] instanceof SQLIntegerExpr || args[1] instanceof SQLIntervalExpr)) {
    throw new IllegalArgumentException("ADDDATE requires (date, integer|INTERVAL n unit)");
}

Try / catch

try { route(sql) } catch (SQLNonTransientException e) { if (e.getMessage().contains("for the right syntax to use near")) { logAndFixFunctionCall(e.getMessage()); } }

Prevention

Follow MySQL grammar exactly for date functions
Prefer ADDDATE(date, INTERVAL n DAY) form
Test generated SQL against a real MySQL before routing through Mycat

When it happens

Trigger: invokeAddDate is given an SQLMethodInvokeExpr whose arguments do not match the expected (expr, days) form — e.g. ADDDATE with wrong argument count, a non-numeric second argument, or nested unsupported expression — where the handler calls throwSyntaxError(expr).

Common situations: Typo'd date arithmetic like ADDDATE('2024-01-01'); passing a column/expression where an integer interval is required; generated SQL emitting INTERVAL clauses the parser doesn't model.

Related errors

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/a96482bf10e1068c. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/parser/druid/impl/MysqlMethodInvocationHandler.java:120

                } else if ("MINUTE".equals(unit)) {
                    result = DateUtils.addMinutes(date, delta);
                } else if ("SECOND".equals(unit)) {
                    result = DateUtils.addSeconds(date, delta);
                } else {
                    return null;
                }
                String ret = DateFormatUtils.format(result, "yyyy-MM-dd HH:mm:ss");
                return new SQLIdentifierExpr(ret);
            } catch (ParseException e) {
                LOGGER.error("",e);
            }
        }
        return null;
    }

    private void throwSyntaxError(SQLExpr expr) throws SQLNonTransientException {
        String errMsg = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '" + expr + "' at line 1";
        throw new SQLNonTransientException(errMsg);
    }
}

View on GitHub (pinned to 65f8d8beb7)