alibaba/canal · error · ParserException

Parse sql error

Error message

Parse sql error

What it means

Thrown by SqlParser.parse() when the ES-adapter SQL fails to yield at least one alias table item and at least one select field after Druid parsing. It is a deliberately generic ParserException; note the surrounding catch(Exception) at line 70 re-wraps EVERY failure (including Druid parse errors, ClassCastExceptions, and the empty-fields check itself) into a message-less ParserException, so the on-screen text 'Parse sql error' usually masks the real cause.

Source

Thrown at client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/config/SqlParser.java:67

        try {
            SQLStatementParser parser = new MySqlStatementParser(sql);
            SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();
            MySqlSelectQueryBlock sqlSelectQueryBlock = (MySqlSelectQueryBlock) statement.getSelect().getQuery();

            SchemaItem schemaItem = new SchemaItem();
            schemaItem.setSql(SQLUtils.toMySqlString(sqlSelectQueryBlock));
            SQLTableSource sqlTableSource = sqlSelectQueryBlock.getFrom();
            List<TableItem> tableItems = new ArrayList<>();
            SqlParser.visitSelectTable(schemaItem, sqlTableSource, tableItems, null);
            tableItems.forEach(tableItem -> schemaItem.getAliasTableItems().put(tableItem.getAlias(), tableItem));

            List<FieldItem> fieldItems = collectSelectQueryFields(sqlSelectQueryBlock);
            fieldItems.forEach(fieldItem -> schemaItem.getSelectFields().put(fieldItem.getFieldName(), fieldItem));

            schemaItem.init();

            if (schemaItem.getAliasTableItems().isEmpty() || schemaItem.getSelectFields().isEmpty()) {
                throw new ParserException("Parse sql error");
            }
            return schemaItem;
        } catch (Exception e) {
            throw new ParserException();
        }
    }

    /**
     * 归集字段
     *
     * @param sqlSelectQueryBlock sqlSelectQueryBlock
     * @return 字段属性列表
     */
    private static List<FieldItem> collectSelectQueryFields(MySqlSelectQueryBlock sqlSelectQueryBlock) {
        return sqlSelectQueryBlock.getSelectList().stream().map(selectItem -> {
            FieldItem fieldItem = new FieldItem();
            fieldItem.setFieldName(selectItem.getAlias());
            fieldItem.setExpr(selectItem.toString());

View on GitHub (pinned to 87be50e876)

Solutions

1. Enable Druid debug logging and reproduce SqlParser.parse locally with the exact SQL to read the real underlying exception before it is re-wrapped.
2. Validate the SQL is a single flat MySQL SELECT with a FROM table/join that the parser understands; simplify joins to plain 'a JOIN b ON a.id=b.aid' until it parses.
3. Ensure the SELECT list is non-empty and every selected field resolves to a column, property, method, or supported CASE expression.
4. If you must diagnose production, temporarily patch the catch block to chain 'e' (throw new ParserException(e.getMessage(), e)) so the root cause surfaces.
5. Run the same SQL through a MySQL client to confirm syntax validity before feeding it to the adapter.

Example fix

// before
try {
    ...
} catch (Exception e) {
    throw new ParserException();  // swallows real cause
}

// after
} catch (Exception e) {
    throw new ParserException("Parse sql error: " + e.getMessage(), e);
}

// Pre-validate the SQL is a single MySQL SELECT before parsing
String sql = mapping.getSql();
if (sql == null || sql.trim().isEmpty() || !sql.trim().toUpperCase().startsWith("SELECT")) {
    throw new IllegalArgumentException("esMapping.sql must be a non-empty SELECT statement");
}
// Optionally test-parse with Druid standalone
MySqlStatementParser p = new MySqlStatementParser(sql);
SQLStatement stmt = p.parseStatement(); // throws ParserException with real message
if (!(stmt instanceof SQLSelectStatement)) {
    throw new IllegalArgumentException("esMapping.sql must be a SELECT");
}

try {
    SchemaItem item = SqlParser.parse(sql);
} catch (ParserException e) {
    // SqlParser.parse swallows the root cause; re-parse with Druid directly to surface it
    logger.error("ES SQL parse failed; re-parsing for root cause:");
    try { new MySqlStatementParser(sql).parseStatement(); }
    catch (Exception real) { logger.error("Root cause: ", real); }
    throw e;
}

When it happens

Trigger: Calling SqlParser.parse(sql) with a query that (a) is not a single MySQL SELECT (UNION/subquery-only, DDL, multi-statement), (b) has a FROM clause the parser cannot turn into an ExprTableSource/Join/Subquery, (c) selects zero columns, or (d) triggers any Druid ClassCastException inside the try block.

Common situations: ES yml mapping 'esMapping.sql' is malformed, uses a non-SELECT statement, omits the FROM clause, or references a subquery in a way visitSelectTable cannot walk; upgrading the Druid dependency so AST node types change; a typo in the SQL string.

Related errors

• Unsupported for complex of on-condition
• Not found primary key field in main table
• ERROR # The kafka kerberos configuration file does not exist
• Pulsar Consumer subscriptName required
• error

AI-assisted analysis of alibaba/canal@87be50e876 (2026-08-14). Data as JSON: /api/errors/49cb8be406861bf3. Report an issue: GitHub.