pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to retrieve key(s) from auto-increment field(s)

Error message

Unable to retrieve key(s) from auto-increment field(s)

What it means

Thrown when Database.getGeneratedKeys-style retrieval fails: after an insert with auto-increment/identity columns, reading the returned keys ResultSet and its metadata (getRowInfo/getRow) raised an exception, wrapped as 'Unable to retrieve key(s) from auto-increment field(s)'. Any failure in the key-retrieval path — SQLException, metadata introspection, or value conversion — is funneled here.

Solutions

1. Check the cause exception for the underlying SQLException from getGeneratedKeys()
2. Upgrade the JDBC driver to one that supports Statement.RETURN_GENERATED_KEYS
3. Disable the auto-increment/key retrieval option in the table output step if keys aren't needed
4. Verify the target table actually has an auto-increment/identity column
5. Use a database-specific sequence instead of auto-increment (getNextSequenceValue)

// check driver capability up front
DatabaseMetaData md = database.getDatabaseMetaData();
boolean supportsKeys = false;
try (java.sql.Statement s = database.getConnection().createStatement()) {
  s.execute("SELECT 1"); // connection alive
  supportsKeys = md.getDatabaseProductName() != null;
} catch (SQLException e) { /* handle */ }

try {
  row = database.insertRow(..., true /* autoIncrement */);
} catch (KettleDatabaseException e) {
  // fall back to reading keys via explicit query
  Long id = database.getNextSequenceValue("seq_mytable", "id");
}

When it happens

Trigger: Calling insertRow(..., autoIncrement=true) / getGeneratedKeys paths on a table with auto-increment columns where executeReturnKeys fails: driver does not support Statement.RETURN_GENERATED_KEYS, getGeneratedKeys() throws, or key column metadata cannot be read.

Common situations: Using a database/driver that doesn't return generated keys (older MySQL drivers, some ODBC bridges); inserting into a table without an auto-increment column while requesting keys; driver returns keys in unexpected shape causing conversion failure.

Understand the failure class

Background: Database query failed: Internal Server Error 500s wrapping SQL, Prisma, and connection failures — what to check first — this error's family across 16 libraries.

Related errors

• Unable to retrieve auto-increment of combi insert key : +…
• No generated keys while "return generated keys" is active!
• Unable to retrieve auto-increment of combi insert key : +…
• Unable to retrieve value of auto-generated technical key …
• AccessInputMeta.Exception.ErrorSavingToRepository

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

Appendix: source

Thrown at core/src/main/java/org/pentaho/di/core/database/Database.java:1446

   * @throws KettleDatabaseException in case something goes wrong retrieving the keys.
   */
  public RowMetaAndData getGeneratedKeys( PreparedStatement ps ) throws KettleDatabaseException {
    try ( ResultSet keys = ps.getGeneratedKeys() ) {
      ResultSetMetaData resultSetMetaData = keys.getMetaData();
      if ( resultSetMetaData == null ) {
        resultSetMetaData = ps.getMetaData();
      }
      RowMetaInterface rowMetaInterface;
      if ( resultSetMetaData == null ) {
        rowMetaInterface = new RowMeta();
        rowMetaInterface.addValueMeta( new ValueMetaInteger( "ai-key" ) );
      } else {
        rowMetaInterface = getRowInfo( resultSetMetaData, false, false );
      }

      return new RowMetaAndData( rowMetaInterface, getRow( keys, resultSetMetaData, rowMetaInterface ) );
    } catch ( Exception ex ) {
      throw new KettleDatabaseException( "Unable to retrieve key(s) from auto-increment field(s)", ex );
    }
  }

  public Long getNextSequenceValue( String sequenceName, String keyfield ) throws KettleDatabaseException {
    return getNextSequenceValue( null, sequenceName, keyfield );
  }

  public Long getNextSequenceValue( String schemaName, String sequenceName, String keyfield )
    throws KettleDatabaseException {
    Long retval = null;

    String schemaSequence = databaseMeta.getQuotedSchemaTableCombination( schemaName, sequenceName );

    try {
      if ( pstmtSeq == null ) {
        pstmtSeq =
          connection.prepareStatement( databaseMeta.getSeqNextvalSQL( databaseMeta.stripCR( schemaSequence ) ) );
      }

View on GitHub (pinned to f3058517a1)