pentaho/pentaho-kettle · critical · KettleDatabaseException

Unable to empty ps and commit connection.

Error message

Unable to empty ps and commit connection.

What it means

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.

Solutions

1. Check the chained SQLException for the driver error at flush/commit time
2. If the connection was lost, the buffered rows are gone — reprocess the transformation/partition from the last checkpoint
3. Fix constraint violations in buffered rows (duplicates, nulls) detected only at final flush
4. Reduce batch buffer size so errors surface earlier with smaller row counts
5. Check for earlier exceptions that may have marked the transaction rollback-only

if (!database.getConnection().isValid(5)) {
  // reconnect and replay the partition from the last committed checkpoint
}

try {
  database.insertFinished(prepStatementInsert, true);
} catch (KettleDatabaseException e) {
  log.logError("Final flush/commit failed; rows in this batch were NOT persisted: " + e.getMessage(), e);
  // trigger restart-from-checkpoint logic
  throw e;
}

When it happens

Trigger: 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.

Common situations: 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.

Related errors

• Error comitting connection
• Unable to commit connection after having inserted rows.
• Database.Exception.UnableToDisableAutoCommit
• Database.Exception.UnableToEnableAutoCommit
• Database.Exception.UnableToReleaseSavepoint

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

Appendix: source

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

            ps.executeBatch();
            commit();
            ps.clearBatch();
          } else {
            commit();
          }
        }

        // Let's not forget to close the prepared statement.
        //
        ps.close();
      }
    } catch ( BatchUpdateException ex ) {
      throw createKettleDatabaseBatchException( "Error updating batch", ex );
    } catch ( SQLException ex ) {
      if ( isBatchUpdate ) {
        throw createKettleDatabaseBatchException( "Error updating batch", ex );
      } else {
        throw new KettleDatabaseException( "Unable to empty ps and commit connection.", ex );
      }
    }
  }

  public static KettleDatabaseBatchException createKettleDatabaseBatchException( String message, SQLException ex ) {
    KettleDatabaseBatchException kdbe = new KettleDatabaseBatchException( message, ex );
    if ( ex instanceof BatchUpdateException ) {
      kdbe.setUpdateCounts( ( (BatchUpdateException) ex ).getUpdateCounts() );
    } else {
      // Null update count forces rollback of batch
      kdbe.setUpdateCounts( null );
    }
    List<Exception> exceptions = new ArrayList<>();
    SQLException nextException = ex.getNextException();
    SQLException oldException = null;

    // This construction is specifically done for some JDBC drivers, these
    // drivers

View on GitHub (pinned to f3058517a1)