{"record":{"id":"856c8c57c223c431","repo":"pentaho/pentaho-kettle","slug":"unable-to-perform-delete-with-sql-sql-ids-ids","errorCode":null,"errorMessage":"Unable to perform delete with SQL: {sql}, ids={ids}","messagePattern":"Unable to perform delete with SQL: (.+?), ids=(.+?)","errorType":"exception","errorClass":"KettleException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/repository/kdr/delegates/KettleDatabaseRepositoryConnectionDelegate.java","lineNumber":1992,"sourceCode":"  public RowMetaAndData getParameterMetaData( ObjectId... ids ) throws KettleException {\n    RowMetaInterface parameterMeta = new RowMeta();\n    Object[] parameterData = new Object[ ids.length ];\n    for ( int i = 0; i < ids.length; i++ ) {\n      parameterMeta.addValueMeta( new ValueMetaInteger( \"id\" + ( i + 1 ) ) );\n      parameterData[ i ] = Long.valueOf( ids[ i ].getId() );\n    }\n    return new RowMetaAndData( parameterMeta, parameterData );\n  }\n\n  public void performDelete( String sql, ObjectId... ids ) throws KettleException {\n    try {\n      PreparedStatement ps = getPreparedStatement( sql );\n\n      RowMetaAndData param = getParameterMetaData( ids );\n      database.setValues( param, ps );\n      ps.execute();\n    } catch ( SQLException e ) {\n      throw new KettleException( \"Unable to perform delete with SQL: \" + sql + \", ids=\" + Arrays.toString( ids ), e );\n    }\n  }\n\n  public void closeAttributeLookupPreparedStatements() throws KettleException {\n    closeStepAttributeLookupPreparedStatement();\n    closeTransAttributeLookupPreparedStatement();\n    closeJobAttributeLookupPreparedStatement();\n    closeLookupJobEntryAttribute();\n  }\n\n  /**\n   * A MySQL InnoDB hack really... Doesn't like a lock in case there's been a read in another session. It considers it\n   * an open transaction.\n   *\n   * @throws KettleDatabaseException\n   */\n  public void closeReadTransaction() throws KettleDatabaseException {\n    if ( databaseMeta.isMySQLVariant() && !database.isAutoCommit() ) {","sourceCodeStart":1974,"sourceCodeEnd":2010,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/repository/kdr/delegates/KettleDatabaseRepositoryConnectionDelegate.java#L1974-L2010","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before\nrepository.connectionDelegate.performDelete(\"DELETE FROM R_ELEMENT_ATTRIBUTE WHERE ID_ATTRIBUTE = ?\", id);\n// after: delete dependents first\nrepository.connectionDelegate.performDelete(\"DELETE FROM R_ELEMENT WHERE ID_ELEMENT = ?\", idElement);\nrepository.connectionDelegate.performDelete(\"DELETE FROM R_ELEMENT_ATTRIBUTE WHERE ID_ATTRIBUTE = ?\", id);","handlingStrategy":"try-catch","validationCode":"// check for referencing rows before deleting\nResultSet rs = stmt.executeQuery(\n  \"SELECT COUNT(*) FROM R_TRANS_ATTRIBUTE WHERE ID_ATTRIBUTE = \" + id.getId());","typeGuard":null,"tryCatchPattern":"try {\n  delegate.performDelete(sql, ids);\n} catch (KettleException e) {\n  log.error(\"Delete failed: \" + sql + \" ids=\" + Arrays.toString(ids), e.getCause());\n  // resolve locks/constraints then retry\n}","preventionTips":["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"],"tags":["database","delete","sql","repository"],"backgroundTag":"sql-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"}