alibaba/canal · error · RuntimeException

Not found primary key field in main table

Error message

Not found primary key field in main table

What it means

ESSyncUtil.pkConditionSql() builds a WHERE clause from the configured ES document id field(s). It collects the id field's underlying ColumnItems that belong to the main table (matching by owner/alias); if none of the id field's columns are owned by the main table, no primary-key condition can be built and it throws.

Source

Thrown at client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/support/ESSyncUtil.java:300

     * @param mapping
     * @param data
     * @return
     */
    public static String pkConditionSql(ESMapping mapping, Map<String, Object> data) {
        Set<ColumnItem> idColumns = new LinkedHashSet<>();
        SchemaItem schemaItem = mapping.getSchemaItem();

        TableItem mainTable = schemaItem.getMainTable();

        for (ColumnItem idColumnItem : schemaItem.getIdFieldItem(mapping).getColumnItems()) {
            if ((mainTable.getAlias() == null && idColumnItem.getOwner() == null)
                || (mainTable.getAlias() != null && mainTable.getAlias().equals(idColumnItem.getOwner()))) {
                idColumns.add(idColumnItem);
            }
        }

        if (idColumns.isEmpty()) {
            throw new RuntimeException("Not found primary key field in main table");
        }

        // 拼接condition
        StringBuilder condition = new StringBuilder(" ");
        for (ColumnItem idColumn : idColumns) {
            Object idVal = data.get(idColumn.getColumnName());
            if (mainTable.getAlias() != null) condition.append(mainTable.getAlias()).append(".");
            condition.append(idColumn.getColumnName()).append("=");
            if (idVal instanceof String) {
                condition.append("'").append(idVal).append("' AND ");
            } else {
                condition.append(idVal).append(" AND ");
            }
        }

        if (condition.toString().endsWith("AND ")) {
            int len2 = condition.length();
            condition.delete(len2 - 4, len2);

View on GitHub (pinned to 87be50e876)

Solutions

1. Ensure the ES id field maps to at least one column that belongs to the main table (the first FROM table, matching its alias).
2. If the SQL uses an alias, qualify the id column with that alias (e.g. 'a.id') so its owner equals mainTable.getAlias().
3. For a flat mapping, reference the primary-key column directly without an owner prefix.
4. Confirm the mapping's 'esMapping._id' points to a real main-table primary-key column.

Example fix

-- before (id only on joined table)
SELECT b.uid as _id ... FROM main a JOIN user b ON a.uid=b.uid

-- after (id on main table)
SELECT a.id as _id ... FROM main a JOIN user b ON a.uid=b.uid

// Before sync, verify the ES _id field maps to a main-table column
ESMapping mapping = ...;
SchemaItem si = mapping.getSchemaItem();
TableItem main = si.getMainTable();
Set<ColumnItem> mainIdCols = si.getIdFieldItem(mapping).getColumnItems().stream()
    .filter(c -> (main.getAlias()==null && c.getOwner()==null)
              || (main.getAlias()!=null && main.getAlias().equals(c.getOwner())))
    .collect(Collectors.toSet());
if (mainIdCols.isEmpty()) {
    throw new IllegalStateException("ES _id must reference a column on the main table " + main.getTableName());
}

try {
    ESSyncUtil.pkConditionSql(mapping, data);
} catch (RuntimeException e) {
    if ("Not found primary key field in main table".equals(e.getMessage())) {
        logger.error("Map the ES _id to a main-table primary-key column");
    }
    throw e;
}

When it happens

Trigger: An ES mapping whose '_id' field is derived entirely from a non-main (joined/sub) table column, or where the main table alias in the SQL does not match the owner recorded on the id ColumnItem; also when schemaItem.getIdFieldItem(mapping) returns columns whose owner differs from mainTable.getAlias().

Common situations: Setting the ES _id to a field that only exists in the joined table; changing the main table alias in the SQL without updating the id mapping; a flat single-table mapping where the id column lost its owner.

Related errors

• No clickhouse adapter found for config key: {}
• Target column: {} not matched
• Parse sql error
• Unsupported for complex of on-condition
• empty rowKey

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