What causes "Unable to perform delete with SQL: {sql}, ids={ids}" in pentaho/pentaho-kettle?

Copy the SQL and ids from the message and run it manually against the repository DB to see the real DB error

pentaho/pentaho-kettle · error · KettleException

Unable to perform delete with SQL

Error message

Unable to perform delete with SQL: {sql}, ids={ids}

What it means

Thrown by performDelete when executing a prepared DELETE statement against the repository raises a SQLException. The message includes the exact SQL and the ObjectId parameters so the failing delete can be reproduced manually. The underlying SQLException is attached as cause.

Solutions

Copy the SQL and ids from the message and run it manually against the repository DB to see the real DB error
Check for foreign key rows still referencing the deleted object and delete/fix children first
Retry after resolving lock contention from concurrent repository sessions
Verify repository connection health and re-run the operation

Example fix

// before
repository.connectionDelegate.performDelete("DELETE FROM R_ELEMENT_ATTRIBUTE WHERE ID_ATTRIBUTE = ?", id);
// after: delete dependents first
repository.connectionDelegate.performDelete("DELETE FROM R_ELEMENT WHERE ID_ELEMENT = ?", idElement);
repository.connectionDelegate.performDelete("DELETE FROM R_ELEMENT_ATTRIBUTE WHERE ID_ATTRIBUTE = ?", id);

Defensive patterns

Strategy: try-catch

Validate before calling

// check for referencing rows before deleting
ResultSet rs = stmt.executeQuery(
  "SELECT COUNT(*) FROM R_TRANS_ATTRIBUTE WHERE ID_ATTRIBUTE = " + id.getId());

Try / catch

try {
  delegate.performDelete(sql, ids);
} catch (KettleException e) {
  log.error("Delete failed: " + sql + " ids=" + Arrays.toString(ids), e.getCause());
  // resolve locks/constraints then retry
}

Prevention

Delete child/dependent rows before parent rows
Avoid concurrent repository sessions mutating the same objects
Reproduce failing deletes manually using the SQL in the message

When it happens

Trigger: Calling performDelete(sql, ids...) (e.g. deleting attributes, database attributes) where ps.execute() throws: lock timeout, FK constraint violation, connection lost, or malformed SQL against the specific DB dialect.

Common situations: Deleting an object still referenced by other repository rows (foreign keys); concurrent sessions locking the row; database connection dropped mid-delete; dialect-specific SQL incompatibility.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

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

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/repository/kdr/delegates/KettleDatabaseRepositoryConnectionDelegate.java:1992

  public RowMetaAndData getParameterMetaData( ObjectId... ids ) throws KettleException {
    RowMetaInterface parameterMeta = new RowMeta();
    Object[] parameterData = new Object[ ids.length ];
    for ( int i = 0; i < ids.length; i++ ) {
      parameterMeta.addValueMeta( new ValueMetaInteger( "id" + ( i + 1 ) ) );
      parameterData[ i ] = Long.valueOf( ids[ i ].getId() );
    }
    return new RowMetaAndData( parameterMeta, parameterData );
  }

  public void performDelete( String sql, ObjectId... ids ) throws KettleException {
    try {
      PreparedStatement ps = getPreparedStatement( sql );

      RowMetaAndData param = getParameterMetaData( ids );
      database.setValues( param, ps );
      ps.execute();
    } catch ( SQLException e ) {
      throw new KettleException( "Unable to perform delete with SQL: " + sql + ", ids=" + Arrays.toString( ids ), e );
    }
  }

  public void closeAttributeLookupPreparedStatements() throws KettleException {
    closeStepAttributeLookupPreparedStatement();
    closeTransAttributeLookupPreparedStatement();
    closeJobAttributeLookupPreparedStatement();
    closeLookupJobEntryAttribute();
  }

  /**
   * A MySQL InnoDB hack really... Doesn't like a lock in case there's been a read in another session. It considers it
   * an open transaction.
   *
   * @throws KettleDatabaseException
   */
  public void closeReadTransaction() throws KettleDatabaseException {
    if ( databaseMeta.isMySQLVariant() && !database.isAutoCommit() ) {

View on GitHub (pinned to f3058517a1)