What causes "Trans.Exception.ErrorCommittingUniqueConnection" in pentaho/pentaho-kettle?

Read the chained cause: if it's a constraint violation, fix the data; if connection loss, fix network/timeout settings

pentaho/pentaho-kettle · error · KettleDatabaseException

Trans.Exception.ErrorCommittingUniqueConnection

Error message

Trans.Exception.ErrorCommittingUniqueConnection

What it means

At transformation end with unique connections, each database's transaction is committed when no step errors occurred; this is thrown when database.commit(true) fails. The failing connection name is embedded in the message and the driver error is chained as cause.

Solutions

Read the chained cause: if it's a constraint violation, fix the data; if connection loss, fix network/timeout settings
Check for deferred constraints or triggers on the target tables that fail at commit time
Break very large transactions into smaller batches to avoid DB resource limits at commit
Verify the DB did not kill the session (look at server logs for OOM kill, idle timeouts)

Example fix

// before: deferred FK fails at commit
INSERT child rows with missing parent in same unique connection
// after: insert parents first or add FK constraint as DEFERRABLE INITIALLY DEFERRED with valid data

Defensive patterns

Strategy: try-catch

Validate before calling

// validate data / constraints before large unique-connection transactions
for (DatabaseMeta dbMeta : transMeta.getUsedDatabaseConnections()) {
  if (!dbMeta.testConnectionSuccess()) throw new IllegalStateException("DB unreachable: " + dbMeta.getName());
}

Try / catch

try {
  trans.waitUntilFinished();
} catch (KettleDatabaseException e) {
  if (e.getMessage().contains("ErrorCommittingUniqueConnection")) {
    Throwable cause = e.getCause();
    if (cause instanceof SQLException && ((SQLException) cause).getErrorCode() == <constraint-violation-code>) {
      fixDataAndRetry();
    }
  } else { throw e; }
}

Prevention

Inspect chained cause for constraint violations — clean data before running
Avoid one giant transaction across unique connections; batch the work
Check DB server logs after the error (commit-time OOM, session kill, deferred constraint reports)
Test target tables' constraints/triggers under load before production runs

When it happens

Trigger: Transformation configured with unique connections completes without step errors, but database.commit(true) throws (connection lost, constraint violation deferred to commit, DB crash).

Common situations: Network drop between step execution and commit, deferred constraint/unique-index violations surfacing only at COMMIT, DB-side resource limits hitting during commit of a huge transaction.

Related errors

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/Trans.java:3030

            // This database connection belongs to this transformation.
            // Let's roll it back if there is an error...
            //
            if ( result.getNrErrors() > 0 ) {
              try {
                database.rollback( true );
                log.logBasic( BaseMessages.getString( PKG, "Trans.Exception.TransactionsRolledBackOnConnection",
                  database.toString() ) );
              } catch ( Exception e ) {
                throw new KettleDatabaseException( BaseMessages.getString( PKG,
                  "Trans.Exception.ErrorRollingBackUniqueConnection", database.toString() ), e );
              }
            } else {
              try {
                database.commit( true );
                log.logBasic( BaseMessages.getString( PKG, "Trans.Exception.TransactionsCommittedOnConnection", database
                  .toString() ) );
              } catch ( Exception e ) {
                throw new KettleDatabaseException( BaseMessages.getString( PKG,
                  "Trans.Exception.ErrorCommittingUniqueConnection", database.toString() ), e );
              }
            }
          } catch ( Exception e ) {
            log.logError( BaseMessages.getString( PKG, "Trans.Exception.ErrorHandlingTransformationTransaction",
              database.toString() ), e );
            result.setNrErrors( result.getNrErrors() + 1 );
          } finally {
            try {
              // This database connection belongs to this transformation.
              database.closeConnectionOnly();
            } catch ( Exception e ) {
              log.logError( BaseMessages.getString( PKG, "Trans.Exception.ErrorHandlingTransformationTransaction",
                database.toString() ), e );
              result.setNrErrors( result.getNrErrors() + 1 );
            } finally {
              // Remove the database from the list...
              //

View on GitHub (pinned to f3058517a1)