This is an automated email from the ASF dual-hosted git repository.
bamaer pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git
The following commit(s) were added to refs/heads/main by this push:
new 41d3fad1d9 Update: batch mode with an error hop reports the wrong row
on SQL Ser… (#9251)
41d3fad1d9 is described below
commit 41d3fad1d9817f679841e37095d34c31f623d553
Author: Hans Van Akelyen <[email protected]>
AuthorDate: Fri Nov 6 06:34:00 2026 +0100
Update: batch mode with an error hop reports the wrong row on SQL Ser…
(#9251)
* Update: batch mode with an error hop reports the wrong row on SQL Server
and Oracle, fixes #9248
* Keep order of rows
---
plugins/transforms/update/pom.xml | 11 +
.../hop/pipeline/transforms/update/Update.java | 280 ++++++++++++++++++-
.../hop/pipeline/transforms/update/UpdateData.java | 82 +++++-
.../update/messages/messages_en_US.properties | 2 +
.../transforms/update/UpdateBatchErrorTest.java | 272 ++++++++++++++++++
.../transforms/update/UpdateBatchH2Test.java | 310 +++++++++++++++++++++
6 files changed, 947 insertions(+), 10 deletions(-)
diff --git a/plugins/transforms/update/pom.xml
b/plugins/transforms/update/pom.xml
index ea80ba001c..71ce70f747 100644
--- a/plugins/transforms/update/pom.xml
+++ b/plugins/transforms/update/pom.xml
@@ -42,6 +42,17 @@
</dependencyManagement>
<dependencies>
+ <dependency>
+ <groupId>com.h2database</groupId>
+ <artifactId>h2</artifactId>
+ <scope>test</scope>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.hop</groupId>
+ <artifactId>hop-databases-h2</artifactId>
+ <version>${project.version}</version>
+ <scope>test</scope>
+ </dependency>
<dependency>
<groupId>org.apache.hop</groupId>
<artifactId>hop-databases-postgresql</artifactId>
diff --git
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
index 46c34b5858..32bdabeb61 100644
---
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
+++
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/Update.java
@@ -17,12 +17,17 @@
package org.apache.hop.pipeline.transforms.update;
+import com.google.common.annotations.VisibleForTesting;
+import java.sql.PreparedStatement;
import java.sql.SQLException;
+import java.sql.Statement;
import java.util.ArrayList;
+import java.util.List;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.hop.core.Const;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.exception.HopDatabaseBatchException;
import org.apache.hop.core.exception.HopDatabaseException;
import org.apache.hop.core.exception.HopException;
import org.apache.hop.core.exception.HopTransformException;
@@ -56,9 +61,13 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
super(transformMeta, meta, data, copyNr, pipelineMeta, pipeline);
}
+ /**
+ * @return the row for the output, or null when it goes to the error hop or
waits in the batch
+ */
private synchronized Object[] lookupValues(IRowMeta rowMeta, Object[] row)
throws HopException {
Object[] outputRow = row;
Object[] add;
+ boolean batched = false;
// Create the output row and copy the input values. add flag field!
if (!Utils.isEmpty(meta.getIgnoreFlagField())) {
@@ -187,8 +196,18 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
+ data.lookupParameterRowMeta.getString(lookupRow));
}
data.db.setValues(data.updateParameterRowMeta, updateRow,
data.prepStatementUpdate);
- data.db.insertRow(data.prepStatementUpdate, data.batchMode,
!data.useSavepoints);
- incrementLinesUpdated();
+ if (data.batchMode) {
+ // Only added to the batch: flushBatch() runs it, then passes the
row on to the output or
+ // to the error hop, depending on what the database says about this
row.
+ data.db.insertRow(data.prepStatementUpdate, true, false);
+ UpdateData.PendingRow pending = UpdateData.PendingRow.batched(row,
outputRow, updateRow);
+ data.batchBuffer.add(pending);
+ data.pendingRows.add(pending);
+ batched = true;
+ } else {
+ data.db.insertRow(data.prepStatementUpdate, false,
!data.useSavepoints);
+ incrementLinesUpdated();
+ }
} else {
incrementLinesSkipped();
}
@@ -199,7 +218,7 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
}
}
- return outputRow;
+ return batched ? null : outputRow;
}
@Override
@@ -210,7 +229,9 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
Object[] r = getRow(); // Get row from input rowset & set row busy!
if (r == null) { // no more input to be expected...
-
+ if (data.batchMode) {
+ flushBatchOrStop();
+ }
setOutputDone();
return false;
}
@@ -340,10 +361,10 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
commitEveryCommitSize();
}
if (data.keyNotFound) {
- putError(inputRowMeta, r, 1L, data.stringErrorKeyNotFound,
data.stringFieldnames, "UPD001");
+ putErrorInOrder(r, data.stringErrorKeyNotFound, data.stringFieldnames,
"UPD001");
}
if (outputRow != null) {
- putRow(data.outputRowMeta, outputRow); // copy non-ignored rows to
output rowset(s)
+ putRowInOrder(outputRow); // copy non-ignored rows to output rowset(s)
}
if (checkFeedback(getLinesRead()) && isBasic()) {
logBasic(BaseMessages.getString(PKG, "Update.Log.LineNumber") +
getLinesRead());
@@ -362,13 +383,242 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
if (sendToErrorRow) {
// Simply add this row to the error row
- putError(getInputRowMeta(), r, 1, errorMessage, null, "UPD001");
+ putErrorInOrder(r, errorMessage, null, "UPD001");
}
}
+ // Outside the try above: a failed batch is about the rows in it, not
about this row.
+ // The rows waiting behind the batch count as well, so that memory stays
at one commit size
+ // when few rows need an update: the batch then runs before it is full.
+ if (data.batchMode && data.pendingRows.size() >= data.commitSize &&
!flushBatchOrStop()) {
+ setOutputDone();
+ return false;
+ }
+
return true;
}
+ /**
+ * Passes a row on to the output, after the rows of a pending batch that
came in before it.
+ *
+ * @param outputRow the row for the output
+ */
+ private void putRowInOrder(Object[] outputRow) throws HopTransformException {
+ if (data.batchBuffer.isEmpty()) {
+ putRow(data.outputRowMeta, outputRow);
+ } else {
+ data.pendingRows.add(UpdateData.PendingRow.output(outputRow));
+ }
+ }
+
+ /**
+ * Passes a row on to the error hop, after the rows of a pending batch that
came in before it.
+ *
+ * @param inputRow the row as it came in
+ */
+ private void putErrorInOrder(
+ Object[] inputRow, String errorMessage, String errorFields, String
errorCode)
+ throws HopTransformException {
+ if (data.batchBuffer.isEmpty()) {
+ putError(getInputRowMeta(), inputRow, 1L, errorMessage, errorFields,
errorCode);
+ } else {
+ data.pendingRows.add(
+ UpdateData.PendingRow.error(inputRow, errorMessage, errorFields,
errorCode));
+ }
+ }
+
+ /**
+ * Passes every pending row on in the order the rows came in: a failed row
to the error hop, the
+ * others to the output. Empties the batch.
+ */
+ private void releasePendingRows() throws HopTransformException {
+ for (UpdateData.PendingRow row : data.pendingRows) {
+ if (row.isFailed()) {
+ putError(
+ getInputRowMeta(),
+ row.getInputRow(),
+ 1L,
+ row.getErrorMessage(),
+ row.getErrorFields(),
+ row.getErrorCode());
+ } else {
+ putRow(data.outputRowMeta, row.getOutputRow());
+ if (row.isBatched()) {
+ incrementLinesUpdated();
+ }
+ }
+ }
+ clearPendingRows();
+ }
+
+ private void clearPendingRows() {
+ data.pendingRows.clear();
+ data.batchBuffer.clear();
+ }
+
+ /**
+ * Runs the batch, stopping the transform when that fails without an error
hop.
+ *
+ * @return false when the transform was stopped
+ */
+ private boolean flushBatchOrStop() {
+ try {
+ flushBatch();
+ return true;
+ } catch (HopException e) {
+ logError(BaseMessages.getString(PKG, "Update.Log.ErrorInTransform"), e);
+ setErrors(1);
+ stopAll();
+ return false;
+ }
+ }
+
+ /**
+ * Runs and commits the JDBC batch, then passes every pending row on in the
order the rows came
+ * in: a row of the batch goes to the output when the database updated it
and to the error hop
+ * when it didn't; the rows that waited between them go where they were
already headed.
+ *
+ * <p>Before, a row went to the output as soon as it was added to the batch,
and a failed batch
+ * only surfaced on the row that happened to run it, at the commit size. So
the error hop got that
+ * row (100, 200, ...) instead of the one that failed (97, 194, ...), which
had already gone to
+ * the output.
+ *
+ * @throws HopException when the batch fails without an error hop: the
transaction is rolled back
+ */
+ @VisibleForTesting
+ void flushBatch() throws HopException {
+ if (data.batchBuffer.isEmpty()) {
+ return;
+ }
+ PreparedStatement statement = data.prepStatementUpdate;
+ try {
+ statement.executeBatch();
+ data.db.commit();
+ statement.clearBatch();
+ } catch (SQLException e) {
+ HopDatabaseBatchException be =
+ Database.createHopDatabaseBatchException(
+ BaseMessages.getString(PKG, "Update.Exception.BatchFailed",
data.schemaTable), e);
+ data.db.clearBatch(statement);
+ if (!getTransformMeta().isDoingErrorHandling()) {
+ data.db.rollback();
+ clearPendingRows();
+ throw be;
+ }
+ // Keep what the database did apply, then sort out the rows.
+ data.db.commit(true);
+ routeFailedBatch(be.toString(), be.getUpdateCounts(),
be.getExceptionsList());
+ return;
+ }
+ releasePendingRows();
+ }
+
+ /**
+ * Works out which rows of a failed batch the database refused, whatever
shape the update counts
+ * of the driver have, then passes every pending row on to exactly one
stream, in the order the
+ * rows came in.
+ *
+ * <ul>
+ * <li>One count per row, with {@link Statement#EXECUTE_FAILED} at the
rows that failed: SQL
+ * Server, MySQL, H2.
+ * <li>Fewer counts than rows: the driver stopped at the first failure.
The row at {@code
+ * counts.length} failed and the rows after it were never sent, so
they are sent again as a
+ * new batch, which can fail in its turn: Oracle, Derby. A failure on
the first row gives a
+ * zero-length array.
+ * <li>No counts: nothing says which rows were applied, and sending them
again could update a
+ * row twice, so they all go to the error hop.
+ * </ul>
+ *
+ * @throws HopException when there are more counts than rows: they can't
belong to this batch
+ */
+ @VisibleForTesting
+ void routeFailedBatch(String errorMessage, int[] updateCounts,
List<Exception> exceptionsList)
+ throws HopException {
+ List<UpdateData.PendingRow> buffer = data.batchBuffer;
+ int size = buffer.size();
+ if (updateCounts != null && updateCounts.length > size) {
+ clearPendingRows();
+ throw new HopException(
+ BaseMessages.getString(
+ PKG, "Update.Exception.BatchCountsMismatch",
updateCounts.length, size));
+ }
+
+ int from = 0;
+ int[] counts = updateCounts;
+ String message = errorMessage;
+ List<Exception> exceptions = exceptionsList == null ? List.of() :
exceptionsList;
+
+ while (from < size) {
+ int counted = counts == null ? 0 : counts.length;
+ int exceptionNr = 0;
+ for (int i = 0; i < counted; i++) {
+ // SUCCESS_NO_INFO and a count of zero (no row matched the key) are
not failures.
+ if (counts[i] == Statement.EXECUTE_FAILED) {
+ String rowMessage = message;
+ if (exceptionNr < exceptions.size()) {
+ rowMessage = exceptions.get(exceptionNr++).toString();
+ }
+ buffer.get(from + i).fail(rowMessage, null, "UPD002");
+ }
+ }
+ from += counted;
+ if (from >= size) {
+ break;
+ }
+
+ if (counts == null) {
+ for (int i = from; i < size; i++) {
+ buffer.get(i).fail(message, null, "UPD002");
+ }
+ break;
+ }
+
+ // The driver stopped here: this is the row it refused.
+ buffer.get(from).fail(message, null, "UPD002");
+ from++;
+ if (from >= size) {
+ break;
+ }
+
+ // The rows after it were never sent.
+ try {
+ counts = retryBatch(from, size);
+ message = errorMessage;
+ exceptions = List.of();
+ } catch (HopDatabaseBatchException be) {
+ counts = be.getUpdateCounts();
+ message = be.toString();
+ exceptions = be.getExceptionsList() == null ? List.of() :
be.getExceptionsList();
+ }
+ }
+ releasePendingRows();
+ }
+
+ /**
+ * Sends rows {@code [from, to)} of the batch again as one batch, and
commits them.
+ *
+ * @return the update counts, relative to {@code from}
+ */
+ private int[] retryBatch(int from, int to) throws HopException {
+ PreparedStatement statement = data.prepStatementUpdate;
+ try {
+ for (int i = from; i < to; i++) {
+ data.db.setValues(
+ data.updateParameterRowMeta,
data.batchBuffer.get(i).getBindValues(), statement);
+ statement.addBatch();
+ }
+ int[] counts = statement.executeBatch();
+ data.db.commit();
+ statement.clearBatch();
+ return counts;
+ } catch (SQLException e) {
+ data.db.clearBatch(statement);
+ data.db.commit(true);
+ throw Database.createHopDatabaseBatchException(
+ BaseMessages.getString(PKG, "Update.Exception.BatchFailed",
data.schemaTable), e);
+ }
+ }
+
/**
* The lookup keys drive the WHERE clause of both the lookup SELECT and the
UPDATE. Without a
* single key we would generate a statement ending in a dangling WHERE, so
fail with a
@@ -577,7 +827,11 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
data.useSavepoints =
data.db.isUsingRowSavepoints(getTransformMeta().isDoingErrorHandling());
// A failed row in a batch can't be undone on its own: with
savepoints, no batch.
- data.batchMode = meta.isUseBatchUpdate() && !data.useSavepoints;
+ data.batchMode =
+ meta.isUseBatchUpdate()
+ && !data.useSavepoints
+ && data.commitSize > 0
+ && data.db.getUseBatchInsert(true);
if (meta.isUseBatchUpdate() && data.useSavepoints) {
logBasic(BaseMessages.getString(PKG,
"Update.Log.BatchOffForErrorHandling"));
}
@@ -595,6 +849,9 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
@Override
public void batchComplete() throws HopException {
// This is needed to properly handle transactions when we are using
SingleThreadedExecutor
+ if (data.batchMode) {
+ flushBatch();
+ }
commitBatch(false);
}
@@ -611,7 +868,12 @@ public class Update extends BaseTransform<UpdateMeta,
UpdateData> {
if (!data.db.isAutoCommit()) {
if (getErrors() == 0) {
if (dispose) {
- data.db.emptyAndCommit(data.prepStatementUpdate, data.batchMode);
+ if (data.batchMode && !data.batchBuffer.isEmpty()) {
+ // Stopped before the end of the input: run what is left, as
before.
+ flushBatchOrStop();
+ }
+ // The batch is empty by now: only commit and close the
statement.
+ data.db.emptyAndCommit(data.prepStatementUpdate, false);
data.prepStatementUpdate = null;
} else {
data.db.commit();
diff --git
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
index 1266c2d8cb..c806050ba7 100644
---
a/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
+++
b/plugins/transforms/update/src/main/java/org/apache/hop/pipeline/transforms/update/UpdateData.java
@@ -18,6 +18,9 @@
package org.apache.hop.pipeline.transforms.update;
import java.sql.PreparedStatement;
+import java.util.ArrayList;
+import java.util.List;
+import lombok.Getter;
import org.apache.hop.core.database.Database;
import org.apache.hop.core.row.IRowMeta;
import org.apache.hop.pipeline.transform.BaseTransformData;
@@ -33,9 +36,86 @@ public class UpdateData extends BaseTransformData implements
ITransformData {
*/
public boolean useSavepoints;
- /** Whether the statements go out in JDBC batches: asked for, and no
savepoint per row. */
+ /**
+ * Whether the statements go out in JDBC batches: asked for, no savepoint
per row, a commit size
+ * and a database that runs batches on this connection.
+ */
public boolean batchMode;
+ /**
+ * A row that waits for the current JDBC batch before it is passed on.
+ *
+ * <p>A row with bind values is in the batch: it goes to the output or the
error hop once the
+ * database has said which. The other rows (nothing to update, key not
found, an error of their
+ * own) already know where they go, but wait as well, so that rows leave in
the order they came
+ * in.
+ */
+ @Getter
+ public static final class PendingRow {
+ /** The row as it came in, for the error hop. */
+ private final Object[] inputRow;
+
+ /** The row for the output, or null when it goes to the error hop. */
+ private final Object[] outputRow;
+
+ /** The UPDATE parameters of a row in the batch, to send it again; null
for the other rows. */
+ private final Object[] bindValues;
+
+ private String errorMessage;
+ private String errorFields;
+
+ /** Set when the row goes to the error hop. */
+ private String errorCode;
+
+ private PendingRow(Object[] inputRow, Object[] outputRow, Object[]
bindValues) {
+ this.inputRow = inputRow;
+ this.outputRow = outputRow;
+ this.bindValues = bindValues;
+ }
+
+ /** A row whose UPDATE is in the batch. */
+ public static PendingRow batched(Object[] inputRow, Object[] outputRow,
Object[] bindValues) {
+ return new PendingRow(inputRow, outputRow, bindValues);
+ }
+
+ /** A row for the output that isn't in the batch. */
+ public static PendingRow output(Object[] outputRow) {
+ return new PendingRow(null, outputRow, null);
+ }
+
+ /** A row for the error hop that isn't in the batch. */
+ public static PendingRow error(
+ Object[] inputRow, String errorMessage, String errorFields, String
errorCode) {
+ PendingRow row = new PendingRow(inputRow, null, null);
+ row.fail(errorMessage, errorFields, errorCode);
+ return row;
+ }
+
+ public boolean isBatched() {
+ return bindValues != null;
+ }
+
+ public boolean isFailed() {
+ return errorCode != null;
+ }
+
+ /** Sends the row to the error hop instead of the output. */
+ public void fail(String errorMessage, String errorFields, String
errorCode) {
+ this.errorMessage = errorMessage;
+ this.errorFields = errorFields;
+ this.errorCode = errorCode;
+ }
+ }
+
+ /** The rows in the current JDBC batch, in the order they were added to it.
*/
+ public final List<PendingRow> batchBuffer = new ArrayList<>();
+
+ /**
+ * Every row that waits for the current batch, in the order the rows came
in: the rows of {@link
+ * #batchBuffer} and the rows that arrived between them. Empty when no batch
is pending.
+ */
+ public final List<PendingRow> pendingRows = new ArrayList<>();
+
/** The lookup of the current row found no row to update, with an error hop
to send it to. */
public boolean keyNotFound;
diff --git
a/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
b/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
index a9a7daa759..4c1f2fb8a2 100644
---
a/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
+++
b/plugins/transforms/update/src/main/resources/org/apache/hop/pipeline/transforms/update/messages/messages_en_US.properties
@@ -19,6 +19,8 @@ Update.Description=Update data in a database table based upon
keys
Update.Exception.FieldRequired=Field [{0}] is required and couldn''t be found!
Update.Exception.KeyCouldNotFound=Entry to update with following key could not
be found:
Update.Exception.NoKeyFields=No key fields are specified. At least one key is
required to build the WHERE clause of the UPDATE statement, also when the Skip
lookup option is enabled.
+Update.Exception.BatchFailed=Error updating a batch of rows in table [{0}]
+Update.Exception.BatchCountsMismatch=Unable to tell which rows of the batch
failed: the database returned {0} update counts for a batch of {1} rows.
Update.Init.ConnectionMissing=Database connection is missing for transform
[{0}]!
Update.Log.CheckingRow=Checking row:
Update.Log.ConnectedToDB=Connected to database...
diff --git
a/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchErrorTest.java
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchErrorTest.java
new file mode 100644
index 0000000000..cf089c2850
--- /dev/null
+++
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchErrorTest.java
@@ -0,0 +1,272 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.update;
+
+import static java.sql.Statement.EXECUTE_FAILED;
+import static java.sql.Statement.SUCCESS_NO_INFO;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+import static org.mockito.ArgumentMatchers.any;
+import static org.mockito.ArgumentMatchers.anyLong;
+import static org.mockito.ArgumentMatchers.anyString;
+import static org.mockito.ArgumentMatchers.nullable;
+import static org.mockito.Mockito.doAnswer;
+import static org.mockito.Mockito.doReturn;
+import static org.mockito.Mockito.doThrow;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.never;
+import static org.mockito.Mockito.spy;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
+
+import java.sql.BatchUpdateException;
+import java.sql.PreparedStatement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
+import java.util.stream.LongStream;
+import org.apache.hop.core.database.Database;
+import org.apache.hop.core.exception.HopException;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.pipeline.PipelineMeta;
+import org.apache.hop.pipeline.engines.local.LocalPipelineEngine;
+import org.apache.hop.pipeline.transform.TransformMeta;
+import org.apache.hop.pipeline.transform.TransformPartitioningMeta;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Which stream each row of a failed batch goes to, for each shape of update
counts a driver gives
+ * back. Every row in the batch has to leave on exactly one stream.
+ */
+class UpdateBatchErrorTest {
+
+ private static final int BATCH = 10;
+
+ private Update update;
+ private TransformMeta transformMeta;
+ private UpdateData data;
+ private PreparedStatement statement;
+ private List<Long> output;
+ private List<Long> errors;
+
+ @BeforeEach
+ void setUp() throws Exception {
+ UpdateMeta meta = mock(UpdateMeta.class);
+ transformMeta = mock(TransformMeta.class);
+ doReturn("update").when(transformMeta).getName();
+ doReturn(mock(TransformPartitioningMeta.class))
+ .when(transformMeta)
+ .getTargetTransformPartitioningMeta();
+ doReturn(meta).when(transformMeta).getTransform();
+ doReturn(true).when(transformMeta).isDoingErrorHandling();
+ PipelineMeta pipelineMeta = mock(PipelineMeta.class);
+ doReturn(transformMeta).when(pipelineMeta).findTransform(anyString());
+
+ IRowMeta rowMeta = new RowMeta();
+ rowMeta.addValueMeta(new ValueMetaInteger("id"));
+
+ data = new UpdateData();
+ data.db = mock(Database.class);
+ data.batchMode = true;
+ data.outputRowMeta = rowMeta;
+ data.updateParameterRowMeta = rowMeta;
+ statement = mock(PreparedStatement.class);
+ data.prepStatementUpdate = statement;
+ for (long id = 0; id < BATCH; id++) {
+ Object[] row = {id};
+ UpdateData.PendingRow pending = UpdateData.PendingRow.batched(row, row,
row);
+ data.batchBuffer.add(pending);
+ data.pendingRows.add(pending);
+ }
+
+ update = spy(new Update(transformMeta, meta, data, 0, pipelineMeta, new
LocalPipelineEngine()));
+ doReturn(transformMeta).when(update).getTransformMeta();
+ doReturn(rowMeta).when(update).getInputRowMeta();
+
+ output = new ArrayList<>();
+ errors = new ArrayList<>();
+ doAnswer(
+ inv -> {
+ output.add((Long) ((Object[]) inv.getArgument(1))[0]);
+ return null;
+ })
+ .when(update)
+ .putRow(any(IRowMeta.class), any());
+ doAnswer(
+ inv -> {
+ errors.add((Long) ((Object[]) inv.getArgument(1))[0]);
+ return null;
+ })
+ .when(update)
+ .putError(
+ any(IRowMeta.class),
+ any(),
+ anyLong(),
+ nullable(String.class),
+ nullable(String.class),
+ anyString());
+ }
+
+ private static int[] counts(int length, int... failed) {
+ int[] counts = new int[length];
+ Arrays.fill(counts, 1);
+ for (int i : failed) {
+ counts[i] = EXECUTE_FAILED;
+ }
+ return counts;
+ }
+
+ private void assertEveryRowLeftOnce() {
+ List<Long> all = new ArrayList<>(output);
+ all.addAll(errors);
+ all.sort(Long::compare);
+ assertEquals(
+ LongStream.range(0, BATCH).boxed().toList(),
+ all,
+ "every row of the batch must leave on exactly one stream");
+ assertTrue(data.batchBuffer.isEmpty(), "the batch must be emptied");
+ assertTrue(data.pendingRows.isEmpty(), "the pending rows must be emptied");
+ }
+
+ /** SQL Server, MySQL, H2: one count per row. */
+ @Test
+ void aCountPerRowMarksTheFailedRows() throws HopException {
+ update.routeFailedBatch("failed", counts(BATCH, 3, 7), null);
+
+ assertEquals(List.of(3L, 7L), errors);
+ assertEveryRowLeftOnce();
+ assertEquals(BATCH - 2, update.getLinesUpdated());
+ }
+
+ /** Oracle, Derby: stopped at row 3; rows 4..9 were never sent and are sent
again. */
+ @Test
+ void aShortCountsArraySendsTheRowsAfterTheFailureAgain() throws Exception {
+ when(statement.executeBatch()).thenReturn(counts(BATCH - 4));
+
+ update.routeFailedBatch("failed", counts(3), null);
+
+ assertEquals(List.of(3L), errors);
+ assertEveryRowLeftOnce();
+ verify(statement, times(BATCH - 4)).addBatch();
+ }
+
+ /** The retry fails again, at its third row (row 6 of the batch). */
+ @Test
+ void aRetryThatFailsIsSortedOutTheSameWay() throws Exception {
+ when(statement.executeBatch())
+ .thenThrow(new BatchUpdateException("again", counts(2)))
+ .thenReturn(counts(3));
+
+ update.routeFailedBatch("failed", counts(3), null);
+
+ assertEquals(List.of(3L, 6L), errors);
+ assertEveryRowLeftOnce();
+ }
+
+ /** A failure on the first row gives a zero-length array, not null. */
+ @Test
+ void aFailureOnTheFirstRow() throws Exception {
+ when(statement.executeBatch()).thenReturn(counts(BATCH - 1));
+
+ update.routeFailedBatch("failed", new int[0], null);
+
+ assertEquals(List.of(0L), errors);
+ assertEveryRowLeftOnce();
+ }
+
+ /** Nothing says which rows were applied: sending them again could update a
row twice. */
+ @Test
+ void noCountsSendsEveryRowToTheErrorHop() throws Exception {
+ update.routeFailedBatch("failed", null, null);
+
+ assertEquals(BATCH, errors.size());
+ assertEveryRowLeftOnce();
+ verify(statement, never()).executeBatch();
+ }
+
+ @Test
+ void successNoInfoAndZeroRowsAreNotFailures() throws HopException {
+ int[] counts = counts(BATCH, 5);
+ counts[1] = SUCCESS_NO_INFO;
+ counts[2] = 0;
+
+ update.routeFailedBatch("failed", counts, null);
+
+ assertEquals(List.of(5L), errors);
+ assertEveryRowLeftOnce();
+ }
+
+ /**
+ * Rows that need no update wait behind the batch: the output keeps the
order the rows came in,
+ * also when the batch fails.
+ */
+ @Test
+ void rowsThatWaitedBehindTheBatchKeepTheirPlace() throws HopException {
+ // 100 and 101 came in after batched row 2, 102 after batched row 7; 103
is an error of its own.
+ data.pendingRows.add(3, UpdateData.PendingRow.output(new Object[] {100L}));
+ data.pendingRows.add(4, UpdateData.PendingRow.output(new Object[] {101L}));
+ data.pendingRows.add(10, UpdateData.PendingRow.output(new Object[]
{102L}));
+ data.pendingRows.add(
+ 11, UpdateData.PendingRow.error(new Object[] {103L}, "not found",
"id", "UPD001"));
+
+ update.routeFailedBatch("failed", counts(BATCH, 3), null);
+
+ assertEquals(List.of(0L, 1L, 2L, 100L, 101L, 4L, 5L, 6L, 7L, 102L, 8L,
9L), output);
+ assertEquals(List.of(3L, 103L), errors);
+ assertEquals(BATCH - 1, update.getLinesUpdated());
+ assertTrue(data.pendingRows.isEmpty());
+ assertTrue(data.batchBuffer.isEmpty());
+ }
+
+ @Test
+ void moreCountsThanRowsFails() {
+ assertThrows(
+ HopException.class, () -> update.routeFailedBatch("failed",
counts(BATCH + 1), null));
+ }
+
+ /** Without an error hop a failed batch rolls back and stops the transform.
*/
+ @Test
+ void withoutErrorHopTheBatchIsRolledBack() throws Exception {
+ doReturn(false).when(transformMeta).isDoingErrorHandling();
+ doThrow(new BatchUpdateException("failed", counts(BATCH,
3))).when(statement).executeBatch();
+
+ assertThrows(HopException.class, () -> update.flushBatch());
+
+ verify(data.db).rollback();
+ assertTrue(output.isEmpty());
+ assertTrue(errors.isEmpty());
+ assertTrue(data.batchBuffer.isEmpty());
+ }
+
+ @Test
+ void aBatchThatRunsPassesEveryRowOn() throws Exception {
+ when(statement.executeBatch()).thenReturn(counts(BATCH));
+
+ update.flushBatch();
+
+ assertEquals(BATCH, output.size());
+ assertTrue(errors.isEmpty());
+ assertEveryRowLeftOnce();
+ verify(data.db).commit();
+ }
+}
diff --git
a/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchH2Test.java
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchH2Test.java
new file mode 100644
index 0000000000..bb48a3dd33
--- /dev/null
+++
b/plugins/transforms/update/src/test/java/org/apache/hop/pipeline/transforms/update/UpdateBatchH2Test.java
@@ -0,0 +1,310 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.hop.pipeline.transforms.update;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Queue;
+import java.util.Set;
+import java.util.StringJoiner;
+import java.util.TreeSet;
+import java.util.UUID;
+import java.util.concurrent.ConcurrentLinkedQueue;
+import java.util.function.LongFunction;
+import java.util.stream.LongStream;
+import org.apache.hop.core.HopClientEnvironment;
+import org.apache.hop.core.database.Database;
+import org.apache.hop.core.database.DatabaseMeta;
+import org.apache.hop.core.database.DatabasePluginType;
+import org.apache.hop.core.logging.LoggingObject;
+import org.apache.hop.core.row.IRowMeta;
+import org.apache.hop.core.row.RowMeta;
+import org.apache.hop.core.row.value.ValueMetaInteger;
+import org.apache.hop.core.row.value.ValueMetaString;
+import org.apache.hop.core.variables.Variables;
+import org.apache.hop.databases.h2.H2DatabaseMeta;
+import org.apache.hop.metadata.serializer.memory.MemoryMetadataProvider;
+import org.apache.hop.pipeline.PipelineHopMeta;
+import org.apache.hop.pipeline.PipelineMeta;
+import org.apache.hop.pipeline.engines.local.LocalPipelineEngine;
+import org.apache.hop.pipeline.transform.TransformErrorMeta;
+import org.apache.hop.pipeline.transform.TransformMeta;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.BeforeEach;
+import org.junit.jupiter.api.Test;
+
+/**
+ * Update with "use batch update" against a real JDBC batch. H2, like SQL
Server and MySQL, runs the
+ * whole batch and marks the rows that failed in the update counts.
+ *
+ * <p>A failed batch used to surface on the row that ran it, at the commit
size: the error hop got
+ * rows 100, 200, ... instead of the ones that failed, and the failed rows had
already gone to the
+ * output.
+ */
+class UpdateBatchH2Test {
+
+ private static final String CONNECTION = "h2";
+ private static final int ROWS = 250;
+ private static final int COMMIT_SIZE = 100;
+ private static final int BAD_EVERY = 47;
+
+ private DatabaseMeta databaseMeta;
+
+ /** How many rows had gone to the output each time the transform read a row.
*/
+ private final List<Integer> outputSizeAtRead = new ArrayList<>();
+
+ @BeforeAll
+ static void setUpOnce() throws Exception {
+ HopClientEnvironment.init();
+
DatabasePluginType.getInstance().registerClassPathPlugin(H2DatabaseMeta.class);
+ DatabaseMeta.clearDatabaseInterfacesMap();
+ }
+
+ @BeforeEach
+ void setUp() throws Exception {
+ String databaseName =
+ "mem:update" + UUID.randomUUID().toString().replace("-", "") +
";DB_CLOSE_DELAY=-1";
+ databaseMeta = new DatabaseMeta(CONNECTION, "H2", "Native", "",
databaseName, "", "sa", "");
+ try (Database db = new Database(new LoggingObject("setup"), new
Variables(), databaseMeta)) {
+ db.connect();
+ db.execStatement("CREATE TABLE target (id BIGINT PRIMARY KEY, name
VARCHAR(5))");
+ db.execStatement("INSERT INTO target SELECT x, 'x' FROM SYSTEM_RANGE(1,
" + ROWS + ")");
+ }
+ }
+
+ private static boolean bad(long id) {
+ return id % BAD_EVERY == 0;
+ }
+
+ /** Every row is set to 'a', except the bad ones, whose name is too long for
the column. */
+ private static Queue<Object[]> input() {
+ return input(id -> bad(id) ? "toolong" : "a");
+ }
+
+ private static Queue<Object[]> input(LongFunction<String> name) {
+ Queue<Object[]> input = new ConcurrentLinkedQueue<>();
+ for (long id = 1; id <= ROWS; id++) {
+ input.add(new Object[] {id, name.apply(id)});
+ }
+ return input;
+ }
+
+ private static List<Long> idList(List<Object[]> rows) {
+ List<Long> ids = new ArrayList<>();
+ for (Object[] row : rows) {
+ ids.add((Long) row[0]);
+ }
+ return ids;
+ }
+
+ private String table() throws Exception {
+ StringJoiner content = new StringJoiner(",");
+ try (Database db = new Database(new LoggingObject("check"), new
Variables(), databaseMeta)) {
+ db.connect();
+ for (Object[] row : db.getRows("SELECT id, name FROM target ORDER BY
id", -1)) {
+ content.add(row[0] + ":" + row[1]);
+ }
+ }
+ return content.toString();
+ }
+
+ private static String expectedTable(boolean updated) {
+ StringJoiner content = new StringJoiner(",");
+ for (long id = 1; id <= ROWS; id++) {
+ content.add(id + ":" + (updated && !bad(id) ? "a" : "x"));
+ }
+ return content.toString();
+ }
+
+ private static Set<Long> ids(List<Object[]> rows) {
+ Set<Long> ids = new TreeSet<>();
+ for (Object[] row : rows) {
+ assertTrue(ids.add((Long) row[0]), "row " + row[0] + " was passed on
twice");
+ }
+ return ids;
+ }
+
+ /** An Update in batch mode, with an error hop when errors isn't null. */
+ private Update update(Queue<Object[]> input, List<Object[]> output,
List<Object[]> errors)
+ throws Exception {
+ IRowMeta inputRowMeta = new RowMeta();
+ inputRowMeta.addValueMeta(new ValueMetaInteger("id"));
+ inputRowMeta.addValueMeta(new ValueMetaString("name"));
+
+ PipelineMeta pipelineMeta = new PipelineMeta();
+ MemoryMetadataProvider metadataProvider = new MemoryMetadataProvider();
+ metadataProvider.getSerializer(DatabaseMeta.class).save(databaseMeta);
+ pipelineMeta.setMetadataProvider(metadataProvider);
+
+ UpdateMeta meta = new UpdateMeta();
+ meta.setDefault();
+ meta.setConnection(CONNECTION);
+ meta.setCommitSize(Integer.toString(COMMIT_SIZE));
+ meta.setUseBatchUpdate(true);
+ meta.getLookupField().setSchemaName("");
+ meta.getLookupField().setTableName("target");
+ meta.getLookupField().getLookupKeys().add(new UpdateKeyField("id", "id",
"="));
+ meta.getLookupField().getUpdateFields().add(new UpdateField("name",
"name"));
+ TransformMeta transformMeta = new TransformMeta("update", meta);
+ pipelineMeta.addTransform(transformMeta);
+
+ Update update =
+ new Update(
+ transformMeta, meta, new UpdateData(), 0, pipelineMeta, new
LocalPipelineEngine()) {
+ @Override
+ public IRowMeta getInputRowMeta() {
+ return inputRowMeta;
+ }
+
+ @Override
+ public Object[] getRow() {
+ outputSizeAtRead.add(output.size());
+ Object[] row = input.poll();
+ return row == null ? null : row.clone();
+ }
+
+ @Override
+ public void putRow(IRowMeta rowMeta, Object[] row) {
+ output.add(row);
+ }
+
+ @Override
+ public void putError(
+ IRowMeta rowMeta,
+ Object[] row,
+ long nrErrors,
+ String errorDescriptions,
+ String fieldNames,
+ String errorCodes) {
+ errors.add(row);
+ }
+ };
+
+ if (errors != null) {
+ // After the transform: its constructor looks at the hops of the
pipeline.
+ TransformMeta target = new TransformMeta("errors", new UpdateMeta());
+ pipelineMeta.addTransform(target);
+ pipelineMeta.addPipelineHop(new PipelineHopMeta(transformMeta, target));
+ TransformErrorMeta errorMeta = new TransformErrorMeta(transformMeta,
target);
+ errorMeta.setEnabled(true);
+ transformMeta.setTransformErrorMeta(errorMeta);
+ }
+ return update;
+ }
+
+ private static void run(Update update) {
+ assertTrue(update.init());
+ try {
+ while (update.processRow()) {
+ // keep going
+ }
+ } catch (Exception e) {
+ throw new AssertionError(e);
+ } finally {
+ update.dispose();
+ }
+ }
+
+ @Test
+ void theRowsThatFailedGoToTheErrorHop() throws Exception {
+ List<Object[]> output = new ArrayList<>();
+ List<Object[]> errors = new ArrayList<>();
+ Update update = update(input(), output, errors);
+
+ run(update);
+
+ Set<Long> expectedErrors = new TreeSet<>();
+ Set<Long> expectedOutput = new TreeSet<>();
+ for (long id = 1; id <= ROWS; id++) {
+ (bad(id) ? expectedErrors : expectedOutput).add(id);
+ }
+ // 47, 94, 141, 188 and 235: the last one is in the partial batch run at
the end of the input.
+ assertEquals(expectedErrors, ids(errors));
+ assertEquals(expectedOutput, ids(output));
+ assertEquals(0, update.getErrors());
+ assertEquals(expectedOutput.size(), update.getLinesUpdated());
+ assertEquals(expectedTable(true), table());
+ }
+
+ /**
+ * Rows that need no update wait for the batch in front of them, so the
output keeps the order of
+ * the input. Here the even rows already hold their value and only the odd
ones are updated.
+ */
+ @Test
+ void theOutputKeepsTheOrderOfTheInput() throws Exception {
+ List<Object[]> output = new ArrayList<>();
+ List<Object[]> errors = new ArrayList<>();
+ Update update =
+ update(input(id -> id % 2 == 0 ? "x" : bad(id) ? "toolong" : "a"),
output, errors);
+
+ run(update);
+
+ List<Long> expectedOutput = new ArrayList<>();
+ List<Long> expectedErrors = new ArrayList<>();
+ StringJoiner expectedTable = new StringJoiner(",");
+ for (long id = 1; id <= ROWS; id++) {
+ boolean failed = id % 2 != 0 && bad(id);
+ (failed ? expectedErrors : expectedOutput).add(id);
+ expectedTable.add(id + ":" + (id % 2 != 0 && !failed ? "a" : "x"));
+ }
+ assertEquals(expectedOutput, idList(output));
+ assertEquals(expectedErrors, idList(errors));
+ assertEquals(0, update.getErrors());
+ assertEquals(ROWS / 2 - expectedErrors.size(), update.getLinesUpdated());
+ assertEquals(ROWS / 2, update.getLinesSkipped());
+ assertEquals(expectedTable.toString(), table());
+ }
+
+ /**
+ * Only the first row needs an update. The rows behind it wait for the
batch, but no more than a
+ * commit size of them: the batch then runs before it is full, so they don't
pile up in memory.
+ */
+ @Test
+ void rowsWaitingBehindTheBatchAreCappedAtTheCommitSize() throws Exception {
+ List<Object[]> output = new ArrayList<>();
+ List<Object[]> errors = new ArrayList<>();
+ Update update = update(input(id -> id == 1 ? "a" : "x"), output, errors);
+
+ run(update);
+
+ assertEquals(LongStream.rangeClosed(1, ROWS).boxed().toList(),
idList(output));
+ assertTrue(errors.isEmpty());
+ assertEquals(1, update.getLinesUpdated());
+ for (int read = 0; read < outputSizeAtRead.size(); read++) {
+ assertTrue(
+ read - outputSizeAtRead.get(read) <= COMMIT_SIZE,
+ "more than a commit size of rows waited at read " + read);
+ }
+ }
+
+ @Test
+ void withoutErrorHopAFailedBatchStopsTheTransformAndRollsBack() throws
Exception {
+ List<Object[]> output = new ArrayList<>();
+ Update update = update(input(), output, null);
+
+ run(update);
+
+ assertEquals(1, update.getErrors());
+ // Row 47 fails the first batch: none of its rows was passed on.
+ assertTrue(output.isEmpty(), "rows of a failed batch went to the output");
+ assertEquals(expectedTable(false), table());
+ }
+}