pentaho/pentaho-kettle · error · KettleDatabaseException

SynchronizeAfterMerge.Exception.KeyCouldNotFound

SynchronizeAfterMerge.Exception.KeyCouldNotFound

Error message

SynchronizeAfterMerge.Exception.KeyCouldNotFound

What it means

Thrown in lookupValues() when the step executes the lookup SELECT for the key lookup (used to decide update vs insert) and no matching row is returned, while the operation is update/delete. data.lookupFailure is set and a KettleDatabaseException with the lookup key values is raised, because the step cannot perform an update/delete on a record that does not exist.

Solutions

  1. Verify the key field mappings (table field <-> stream field) in the step's 'Lookup keys' grid match the real target table columns.
  2. Normalize data types/whitespace of key values upstream (Trim strings, type conversions) so the lookup SELECT matches.
  3. Route such rows through a different path: use the step's error handling or filter, or change operation to 'insert' for missing keys.
  4. Enable the step's error handling (isDoingErrorHandling) so lookup failures go to the error stream instead of failing the transformation.

Example fix

// before: key column CHAR(10) padded, lookup compares unpadded value
SELECT ... WHERE code = ?
// after: trim upstream before the step
RTrim(code) AS code FROM source;
Defensive patterns

Strategy: try-catch

Validate before calling

// Validate key existence before choosing update/delete
boolean keyExists = lookupKeyInTarget(keyValues); // SELECT 1 FROM target WHERE key = ?
if (!keyExists && operation.equals("update")) {
  throw new ValidationException("Update requested but key not found: " + Arrays.toString(keyValues));
}

Try / catch

catch (KettleDatabaseException e) {
  if (e.getMessage().contains("KeyCouldNotFound")) {
    // route row to insert path or error stream
  } else { throw e; }
}

Prevention

When it happens

Trigger: performUpdate or performDelete is true; the db lookup query built from the configured key/lookup fields returns an empty ResultSet; the code enters the 'else' branch for update/delete without a found row and throws.

Common situations: Key values differ in type or padding (trailing spaces, CHAR vs VARCHAR) between source and target; case-sensitive comparison; key column values truncated; lookup keys misconfigured in the dialog; row was deleted by a concurrent process.

Understand the failure class

Background: Record Not Found Errors: "not found", RecordNotFound, and "was not found" — what they mean and how to fix them — this error's family across 28 libraries.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/b3f22796d6814fdc. Report an issue: GitHub.

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/synchronizeaftermerge/SynchronizeAfterMerge.java:200

          incrementLinesInput();

          if ( add == null ) {
            // nothing was found:

            if ( data.stringErrorKeyNotFound == null ) {
              data.stringErrorKeyNotFound =
                BaseMessages.getString( PKG, "SynchronizeAfterMerge.Exception.KeyCouldNotFound" )
                  + data.lookupParameterRowMeta.getString( lookupRow );
              data.stringFieldnames = "";
              for (int i = 0; i < data.lookupParameterRowMeta.size(); i++) {
                if ( i > 0 ) {
                  data.stringFieldnames += ", ";
                }
                data.stringFieldnames += data.lookupParameterRowMeta.getValueMeta( i ).getName();
              }
            }
            data.lookupFailure = true;
            throw new KettleDatabaseException( BaseMessages.getString( PKG,
              "SynchronizeAfterMerge.Exception.KeyCouldNotFound", data.lookupParameterRowMeta.getString(
                lookupRow ) ) );
          } else {
            if ( log.isRowLevel() ) {
              logRowlevel( BaseMessages.getString( PKG, "SynchronizeAfterMerge.Log.FoundRowForUpdate",
                data.insertRowMeta.getString( row ) ) );
            }

            for (int i = 0; i < data.valuenrs.length; i++) {
              if ( meta.getUpdate()[i].booleanValue() ) {
                ValueMetaInterface valueMeta = data.inputRowMeta.getValueMeta( data.valuenrs[i] );
                ValueMetaInterface retMeta = data.db.getReturnRowMeta().getValueMeta( i );

                Object rowvalue = row[data.valuenrs[i]];
                Object retvalue = add[i];

                if ( valueMeta.compare( rowvalue, retMeta, retvalue ) != 0 ) {
                  updateorDelete = true;

View on GitHub (pinned to f3058517a1)