What causes "No generated keys while "return generated keys" is active!" in pentaho/pentaho-kettle?

Ensure the target table has an auto-increment/identity primary key column.

pentaho/pentaho-kettle · error · KettleStepException

No generated keys while "return generated keys" is active!

Error message

No generated keys while "return generated keys" is active!

What it means

The 'Return auto-generated key' option is enabled, but after inserting, no generated key rows were returned by the driver (extraKeys is null or contains no rows). The step must output a key to keep hop row-type consistency, so it throws instead of emitting null of unknown type.

Solutions

Ensure the target table has an auto-increment/identity primary key column.
Disable 'Return auto-generated key' in the step if you don't need the key downstream.
Use a JDBC driver version that supports getGeneratedKeys for your database.
For Oracle, configure sequence/returning-key support or use a Sequence step instead.

Example fix

// before
meta.setReturnGeneratedKeys( true ); // table has no auto-increment column
// after
meta.setReturnGeneratedKeys( false ); // or ALTER TABLE target ADD id BIGINT AUTO_INCREMENT PRIMARY KEY

Defensive patterns

Strategy: validation

Validate before calling

// Ensure the target table has an auto-increment PK before enabling generated keys
DatabaseMetaData md = connection.getMetaData();
try ( ResultSet cols = md.getColumns( null, null, tableName, "id" ) ) {
  if ( !cols.next() ) throw new IllegalStateException(
    "No auto-increment 'id' column; disable 'Return generated keys'" );
}

Try / catch

try { processRow(); } catch ( KettleStepException e ) {
  if ( e.getMessage().contains( "No generated keys" ) ) {
    // disable return-generated-keys or fix the table/driver
  }
}

Prevention

Only enable 'Return auto-generated key' when the table has an auto-increment/identity column
Use drivers known to implement getGeneratedKeys for your database
Test key return with a small sample load before production runs

When it happens

Trigger: meta.isReturnGeneratedKeys() is true but Statement.getGeneratedKeys() yields an empty ResultSet — typically because the JDBC driver/table does not support generated keys (e.g. Oracle without config, MySQL without rewriteBatchedStatements/auto-generated PK, or table has no auto-increment column).

Common situations: Target table has no auto-increment/identity column; driver doesn't implement getGeneratedKeys; inserting with explicit PK values; batch mode with drivers that don't return keys per batch row.

Understand the failure class

Background: EmptyResultError / "no results found": when an API or scraper succeeds but returns zero rows — this error's family across 9 libraries.

Related errors

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/tableoutput/TableOutput.java:321

        data.commitCounterMap.put( tableName, Integer.valueOf( 0 ) );
        rowIsSafe = true;
      } else {
        rowIsSafe = false;
      }

      // See if we need to get back the keys as well...
      if ( meta.isReturningGeneratedKeys() ) {
        RowMetaAndData extraKeys = data.db.getGeneratedKeys( insertStatement );

        if ( extraKeys.getRowMeta().size() > 0 ) {
          // Send out the good word!
          // Only 1 key at the moment. (should be enough for now :-)
          generatedKey = extraKeys.getRowMeta().getInteger( extraKeys.getData(), 0 );
        } else {
          // we have to throw something here, else we don't know what the
          // type is of the returned key(s) and we would violate our own rule
          // that a hop should always contain rows of the same type.
          throw new KettleStepException( "No generated keys while \"return generated keys\" is active!" );
        }
      }
    } catch ( KettleDatabaseBatchException be ) {
      errorMessage = be.toString();
      batchProblem = true;
      sendToErrorRow = true;
      updateCounts = be.getUpdateCounts();
      exceptionsList = be.getExceptionsList();

      if ( getStepMeta().isDoingErrorHandling() ) {
        data.db.clearBatch( insertStatement );
        data.db.commit( true );
      } else {
        data.db.clearBatch( insertStatement );
        data.db.rollback();
        StringBuilder msg = new StringBuilder( "Error batch inserting rows into table [" + tableName + "]." );
        msg.append( Const.CR );
        msg.append( "Errors encountered (first 10):" ).append( Const.CR );

View on GitHub (pinned to f3058517a1)