{"record":{"id":"ce96bc287c4abf74","repo":"pentaho/pentaho-kettle","slug":"unable-to-retrieve-key-s-from-auto-increment-field-s","errorCode":null,"errorMessage":"Unable to retrieve key(s) from auto-increment field(s)","messagePattern":"Unable to retrieve key\\(s\\) from auto-increment field\\(s\\)","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"core/src/main/java/org/pentaho/di/core/database/Database.java","lineNumber":1446,"sourceCode":"   * @throws KettleDatabaseException in case something goes wrong retrieving the keys.\n   */\n  public RowMetaAndData getGeneratedKeys( PreparedStatement ps ) throws KettleDatabaseException {\n    try ( ResultSet keys = ps.getGeneratedKeys() ) {\n      ResultSetMetaData resultSetMetaData = keys.getMetaData();\n      if ( resultSetMetaData == null ) {\n        resultSetMetaData = ps.getMetaData();\n      }\n      RowMetaInterface rowMetaInterface;\n      if ( resultSetMetaData == null ) {\n        rowMetaInterface = new RowMeta();\n        rowMetaInterface.addValueMeta( new ValueMetaInteger( \"ai-key\" ) );\n      } else {\n        rowMetaInterface = getRowInfo( resultSetMetaData, false, false );\n      }\n\n      return new RowMetaAndData( rowMetaInterface, getRow( keys, resultSetMetaData, rowMetaInterface ) );\n    } catch ( Exception ex ) {\n      throw new KettleDatabaseException( \"Unable to retrieve key(s) from auto-increment field(s)\", ex );\n    }\n  }\n\n  public Long getNextSequenceValue( String sequenceName, String keyfield ) throws KettleDatabaseException {\n    return getNextSequenceValue( null, sequenceName, keyfield );\n  }\n\n  public Long getNextSequenceValue( String schemaName, String sequenceName, String keyfield )\n    throws KettleDatabaseException {\n    Long retval = null;\n\n    String schemaSequence = databaseMeta.getQuotedSchemaTableCombination( schemaName, sequenceName );\n\n    try {\n      if ( pstmtSeq == null ) {\n        pstmtSeq =\n          connection.prepareStatement( databaseMeta.getSeqNextvalSQL( databaseMeta.stripCR( schemaSequence ) ) );\n      }","sourceCodeStart":1428,"sourceCodeEnd":1464,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/core/src/main/java/org/pentaho/di/core/database/Database.java#L1428-L1464","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Check the cause exception for the underlying SQLException from getGeneratedKeys()","Upgrade the JDBC driver to one that supports Statement.RETURN_GENERATED_KEYS","Disable the auto-increment/key retrieval option in the table output step if keys aren't needed","Verify the target table actually has an auto-increment/identity column","Use a database-specific sequence instead of auto-increment (getNextSequenceValue)"],"exampleFix":null,"handlingStrategy":"fallback","validationCode":"// check driver capability up front\nDatabaseMetaData md = database.getDatabaseMetaData();\nboolean supportsKeys = false;\ntry (java.sql.Statement s = database.getConnection().createStatement()) {\n  s.execute(\"SELECT 1\"); // connection alive\n  supportsKeys = md.getDatabaseProductName() != null;\n} catch (SQLException e) { /* handle */ }","typeGuard":null,"tryCatchPattern":"try {\n  row = database.insertRow(..., true /* autoIncrement */);\n} catch (KettleDatabaseException e) {\n  // fall back to reading keys via explicit query\n  Long id = database.getNextSequenceValue(\"seq_mytable\", \"id\");\n}","preventionTips":["Use a JDBC driver known to support getGeneratedKeys","Only request generated keys when the table has an auto-increment column","Prefer explicit sequences where supported","Test key retrieval per target database type"],"tags":["jdbc","auto-increment","generated-keys"],"backgroundTag":"database-query-failed","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}