{"record":{"id":"043a6978ebf6aa8e","repo":"alibaba/canal","slug":"not-found-primary-key-field-in-main-table","errorCode":null,"errorMessage":"Not found primary key field in main table","messagePattern":"Not found primary key field in main table","errorType":"validation","errorClass":"RuntimeException","httpStatus":null,"severity":"error","filePath":"client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/support/ESSyncUtil.java","lineNumber":300,"sourceCode":"     * @param mapping\n     * @param data\n     * @return\n     */\n    public static String pkConditionSql(ESMapping mapping, Map<String, Object> data) {\n        Set<ColumnItem> idColumns = new LinkedHashSet<>();\n        SchemaItem schemaItem = mapping.getSchemaItem();\n\n        TableItem mainTable = schemaItem.getMainTable();\n\n        for (ColumnItem idColumnItem : schemaItem.getIdFieldItem(mapping).getColumnItems()) {\n            if ((mainTable.getAlias() == null && idColumnItem.getOwner() == null)\n                || (mainTable.getAlias() != null && mainTable.getAlias().equals(idColumnItem.getOwner()))) {\n                idColumns.add(idColumnItem);\n            }\n        }\n\n        if (idColumns.isEmpty()) {\n            throw new RuntimeException(\"Not found primary key field in main table\");\n        }\n\n        // 拼接condition\n        StringBuilder condition = new StringBuilder(\" \");\n        for (ColumnItem idColumn : idColumns) {\n            Object idVal = data.get(idColumn.getColumnName());\n            if (mainTable.getAlias() != null) condition.append(mainTable.getAlias()).append(\".\");\n            condition.append(idColumn.getColumnName()).append(\"=\");\n            if (idVal instanceof String) {\n                condition.append(\"'\").append(idVal).append(\"' AND \");\n            } else {\n                condition.append(idVal).append(\" AND \");\n            }\n        }\n\n        if (condition.toString().endsWith(\"AND \")) {\n            int len2 = condition.length();\n            condition.delete(len2 - 4, len2);","sourceCodeStart":282,"sourceCodeEnd":318,"githubUrl":"https://github.com/alibaba/canal/blob/87be50e87686a3e8af08c368d0e1ffd1f59eb04a/client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/support/ESSyncUtil.java#L282-L318","documentation":"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.","triggerScenarios":"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().","commonSituations":"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.","solutions":["Ensure the ES id field maps to at least one column that belongs to the main table (the first FROM table, matching its alias).","If the SQL uses an alias, qualify the id column with that alias (e.g. 'a.id') so its owner equals mainTable.getAlias().","For a flat mapping, reference the primary-key column directly without an owner prefix.","Confirm the mapping's 'esMapping._id' points to a real main-table primary-key column."],"exampleFix":"-- before (id only on joined table)\nSELECT b.uid as _id ... FROM main a JOIN user b ON a.uid=b.uid\n\n-- after (id on main table)\nSELECT a.id as _id ... FROM main a JOIN user b ON a.uid=b.uid","handlingStrategy":"validation","validationCode":"// Before sync, verify the ES _id field maps to a main-table column\nESMapping mapping = ...;\nSchemaItem si = mapping.getSchemaItem();\nTableItem main = si.getMainTable();\nSet<ColumnItem> mainIdCols = si.getIdFieldItem(mapping).getColumnItems().stream()\n    .filter(c -> (main.getAlias()==null && c.getOwner()==null)\n              || (main.getAlias()!=null && main.getAlias().equals(c.getOwner())))\n    .collect(Collectors.toSet());\nif (mainIdCols.isEmpty()) {\n    throw new IllegalStateException(\"ES _id must reference a column on the main table \" + main.getTableName());\n}","typeGuard":null,"tryCatchPattern":"try {\n    ESSyncUtil.pkConditionSql(mapping, data);\n} catch (RuntimeException e) {\n    if (\"Not found primary key field in main table\".equals(e.getMessage())) {\n        logger.error(\"Map the ES _id to a main-table primary-key column\");\n    }\n    throw e;\n}","preventionTips":["Set esMapping._id to a real primary-key column of the first FROM table.","If the SQL aliases the main table, qualify the id column with that alias.","Add a config test that id ColumnItems intersect the main table's columns."],"tags":["es-adapter","primary-key","mapping","sync"],"backgroundTag":null,"analyzedSha":"87be50e87686a3e8af08c368d0e1ffd1f59eb04a","analyzedAt":"2026-08-14T04:30:11.918Z","schemaVersion":2},"datasetVersion":"2026-08-14T05:17:29.042Z"}