pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to retrieve value of auto-generated technical key …

Error message

Unable to retrieve value of auto-generated technical key : unexpected error:

What it means

DimensionLookup's dimInsert() wraps every failure that occurs while reading back the auto-generated technical key (surrogate key) after inserting a new dimension row into a KettleDatabaseException with this message. The library expects the database/driver to return a generated key for a freshly inserted row; when the JDBC getReturnedKeys call throws any exception or the key cannot be converted to an integer, this error is thrown. It means the insert probably happened but the new surrogate key value could not be retrieved.

Solutions

Check the database connection's 'Specify fields for the technical key' / auto-increment settings and ensure the key field is a numeric auto-increment column
Verify the JDBC driver version supports getGeneratedKeys for your database; upgrade the driver if not
If the driver needs it, set the option to return the generated key by column name in the database meta options
Inspect the wrapped cause exception (getCause()) to see the underlying driver error
As a workaround, switch the technical key strategy from auto-increment to a sequence or table-max lookup supported by the driver

Example fix

// before: key field defined as plain INTEGER with no auto-increment
// after: define the technical key column as auto-increment in the step's key fields
keyField = "dim_customer_tk";  // column must be AUTO_INCREMENT / IDENTITY in the DB
// e.g. MySQL:
// CREATE TABLE dim_customer (dim_customer_tk BIGINT AUTO_INCREMENT PRIMARY KEY, ...)

Defensive patterns

Strategy: try-catch

Validate before calling

// Java: verify the DB supports generated keys before running the step
DatabaseMetaData dmd = connection.getMetaData();
boolean supportsKeys = dmd.supportsGetGeneratedKeys();
if (!supportsKeys) throw new IllegalStateException("Driver does not support getGeneratedKeys; use sequence/table-max key strategy");

Type guard

// check the retrieved key row before use
Object[] keysData = keys.getData();
if (keysData == null || keysData.length == 0 || keysData[0] == null || !(keysData[0] instanceof Number)) {
  throw new KettleDatabaseException("No valid numeric auto-generated key returned");
}

Try / catch

try {
  dimInsert(...);
} catch (KettleDatabaseException e) {
  if (e.getMessage().contains("auto-generated technical key")) {
    logError("Generated-key retrieval failed: " + e.getCause(), e);
    // fallback: re-query by natural key or switch key generation strategy
  } else throw e;
}

Prevention

Confirm the technical key column is a numeric auto-increment/identity column
Use a JDBC driver version known to support getGeneratedKeys for your database
Test the insert+key-retrieval flow on a copy of the table before production
Log and inspect the cause exception rather than only the top-level message

When it happens

Trigger: The step inserted a new dimension version and called data.db.getGeneratedKeys()/retrieveAutoGeneratedKey; the JDBC driver threw while fetching generated keys, or keys row metadata was not an integer type, or the driver does not support Statement.RETURN_GENERATED_KEYS for this table.

Common situations: Using a database or JDBC driver that does not support auto-generated key retrieval (or needs a column-name hint); auto-increment/sequence not configured correctly in the database tab of the step; driver quirks with prepared statements and RETURN_GENERATED_KEYS; table uses a trigger/UUID instead of numeric auto-increment.

Related errors

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java:1182

    // INSERT NEW VALUE!
    data.db.setValues( data.insertRowMeta, insertRow, data.prepStatementInsert );
    data.db.insertRow( data.prepStatementInsert );

    if ( isDebug() ) {
      logDebug( "Row inserted!" );
    }
    if ( technicalKey == null && databaseMeta.supportsAutoGeneratedKeys() ) {
      try {
        RowMetaAndData keys = data.db.getGeneratedKeys( data.prepStatementInsert );
        if ( keys.getRowMeta().size() > 0 ) {
          technicalKey = keys.getRowMeta().getInteger( keys.getData(), 0 );
        } else {
          throw new KettleDatabaseException(
            "Unable to retrieve value of auto-generated technical key : no value found!" );
        }
      } catch ( Exception e ) {
        throw new KettleDatabaseException(
          "Unable to retrieve value of auto-generated technical key : unexpected error: ", e );
      }
    }

    if ( !newEntry ) { // we have to update the previous version in the dimension!
      /*
       * UPDATE d_customer SET dateto = val_datfrom , last_updated = <now> , last_version = false WHERE keylookup[] =
       * keynrs[] AND versionfield = val_version - 1 ;
       */
      Object[] updateRow = new Object[ data.updateRowMeta.size() ];
      int updateIndex = 0;

      switch ( data.startDateChoice ) {
        case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE:
          updateRow[ updateIndex++ ] = dateFrom;
          break;
        case DimensionLookupMeta.START_DATE_ALTERNATIVE_SYSDATE:
          updateRow[ updateIndex++ ] = new Date();

View on GitHub (pinned to f3058517a1)