{"record":{"id":"05a742516ae1018e","repo":"pentaho/pentaho-kettle","slug":"unable-to-empty-ps-and-commit-connection","errorCode":null,"errorMessage":"Unable to empty ps and commit connection.","messagePattern":"Unable to empty ps and commit connection\\.","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"critical","filePath":"core/src/main/java/org/pentaho/di/core/database/Database.java","lineNumber":1719,"sourceCode":"            ps.executeBatch();\n            commit();\n            ps.clearBatch();\n          } else {\n            commit();\n          }\n        }\n\n        // Let's not forget to close the prepared statement.\n        //\n        ps.close();\n      }\n    } catch ( BatchUpdateException ex ) {\n      throw createKettleDatabaseBatchException( \"Error updating batch\", ex );\n    } catch ( SQLException ex ) {\n      if ( isBatchUpdate ) {\n        throw createKettleDatabaseBatchException( \"Error updating batch\", ex );\n      } else {\n        throw new KettleDatabaseException( \"Unable to empty ps and commit connection.\", ex );\n      }\n    }\n  }\n\n  public static KettleDatabaseBatchException createKettleDatabaseBatchException( String message, SQLException ex ) {\n    KettleDatabaseBatchException kdbe = new KettleDatabaseBatchException( message, ex );\n    if ( ex instanceof BatchUpdateException ) {\n      kdbe.setUpdateCounts( ( (BatchUpdateException) ex ).getUpdateCounts() );\n    } else {\n      // Null update count forces rollback of batch\n      kdbe.setUpdateCounts( null );\n    }\n    List<Exception> exceptions = new ArrayList<>();\n    SQLException nextException = ex.getNextException();\n    SQLException oldException = null;\n\n    // This construction is specifically done for some JDBC drivers, these\n    // drivers","sourceCodeStart":1701,"sourceCodeEnd":1737,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/core/src/main/java/org/pentaho/di/core/database/Database.java#L1701-L1737","documentation":"insertFinished empties the prepared insert statement (executeBatch/executeUpdate) and commits the connection; a non-batch SQLException in that phase is wrapped as 'Unable to empty ps and commit connection.' This means pending buffered inserts could not be flushed and/or the final commit failed, so rows may not have been persisted.","triggerScenarios":"Calling Database.insertFinished(prepStatementInsert, batch) where flushing the last buffered rows or commit() throws SQLException with isBatchUpdate=false — constraint violation in a buffered row, commit failing on a broken connection, or transaction rollback imposed by the DB.","commonSituations":"Connection dropped before final commit so buffered rows are lost; last batched rows violate constraints (only detected at flush time); transaction marked rollback-only by an earlier error; DB-side deadlock or undo segment issues at commit.","solutions":["Check the chained SQLException for the driver error at flush/commit time","If the connection was lost, the buffered rows are gone — reprocess the transformation/partition from the last checkpoint","Fix constraint violations in buffered rows (duplicates, nulls) detected only at final flush","Reduce batch buffer size so errors surface earlier with smaller row counts","Check for earlier exceptions that may have marked the transaction rollback-only"],"exampleFix":null,"handlingStrategy":"retry","validationCode":"if (!database.getConnection().isValid(5)) {\n  // reconnect and replay the partition from the last committed checkpoint\n}","typeGuard":null,"tryCatchPattern":"try {\n  database.insertFinished(prepStatementInsert, true);\n} catch (KettleDatabaseException e) {\n  log.logError(\"Final flush/commit failed; rows in this batch were NOT persisted: \" + e.getMessage(), e);\n  // trigger restart-from-checkpoint logic\n  throw e;\n}","preventionTips":["Commit frequently so little data is lost on final flush failure","Keep connection timeouts longer than step duration","Resolve constraint violations before the final batch by validating data","Design transformations to be restartable from checkpoints"],"tags":["jdbc","commit","batch","transaction"],"backgroundTag":"database-write-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"}