This is an automated email from the ASF dual-hosted git repository.

davsclaus pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/camel.git


The following commit(s) were added to refs/heads/main by this push:
     new d63ae73213a6 CAMEL-27169: camel-sql - run onConsumeBatchComplete only 
after the whole batch was routed (#30360)
d63ae73213a6 is described below

commit d63ae73213a615467c5435f0aebedee9870cce27
Author: allthingssecurity <[email protected]>
AuthorDate: Thu Nov 19 17:24:38 2026 +0530

    CAMEL-27169: camel-sql - run onConsumeBatchComplete only after the whole 
batch was routed (#30360)
    
    onConsumeBatchComplete is documented to run after processing the entire
    batch, for example "delete from projects". When the consumer was stopped
    while it routed a batch (route stop, context shutdown, reload: the graceful
    shutdown completes only the current exchange), processBatch left the loop
    and still ran the query, so the rows that were not routed were deleted or
    marked as processed and never consumed.
    
    - SqlConsumer.processBatch does not run the onConsumeBatchComplete query
      when it routed only part of the batch (logged at INFO).
    - The exchanges of the rows that were not routed are released in a finally,
      so also when a failed row (transacted=true) or a failed onConsume query
      (breakBatchOnConsumeFail=true) ends the batch.
    - 4.24 upgrade guide entry: a route that uses onConsumeBatchComplete alone
      sees the routed rows of a cut-short batch again; shutdownRunningTask=
      CompleteAllTasks lets a graceful stop finish the batch.
    - Tests: SqlConsumerBatchCompleteStopTest, 
SqlConsumerBatchEndsEarlyReleaseTest.
    
    Co-Authored-By: Claude Opus 5.5 <[email protected]>
---
 .../apache/camel/component/sql/SqlConsumer.java    | 148 +++++++++++++--------
 .../sql/SqlConsumerBatchCompleteStopTest.java      | 113 ++++++++++++++++
 .../sql/SqlConsumerBatchEndsEarlyReleaseTest.java  | 111 ++++++++++++++++
 .../ROOT/pages/camel-4x-upgrade-guide-4_24.adoc    |  14 ++
 4 files changed, 327 insertions(+), 59 deletions(-)

diff --git 
a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java
 
b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java
index c96c2e80714b..445155eeb531 100644
--- 
a/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java
+++ 
b/components/camel-sql/src/main/java/org/apache/camel/component/sql/SqlConsumer.java
@@ -257,72 +257,92 @@ public class SqlConsumer extends 
ScheduledBatchPollingConsumer {
                     total);
         }
 
-        for (int index = 0; index < total && isBatchAllowed(); index++) {
-            // only loop if we are started (allowed to run)
-            DataHolder holder = ObjectHelper.cast(DataHolder.class, 
exchanges.poll());
-            Exchange exchange = holder.exchange;
-            Object data = holder.data;
-
-            // add current index and total as properties
-            exchange.setProperty(ExchangePropertyKey.BATCH_INDEX, index);
-            exchange.setProperty(ExchangePropertyKey.BATCH_SIZE, total);
-            exchange.setProperty(ExchangePropertyKey.BATCH_COMPLETE, index == 
total - 1);
-
-            // update pending number of exchanges
-            pendingExchanges = total - index - 1;
-
-            // process the current exchange
-            try {
-                getProcessor().process(exchange);
-            } catch (Exception e) {
-                exchange.setException(e);
-            }
+        int index = 0;
+        try {
+            for (; index < total && isBatchAllowed(); index++) {
+                // only loop if we are started (allowed to run)
+                DataHolder holder = ObjectHelper.cast(DataHolder.class, 
exchanges.poll());
+                Exchange exchange = holder.exchange;
+                Object data = holder.data;
+
+                // add current index and total as properties
+                exchange.setProperty(ExchangePropertyKey.BATCH_INDEX, index);
+                exchange.setProperty(ExchangePropertyKey.BATCH_SIZE, total);
+                exchange.setProperty(ExchangePropertyKey.BATCH_COMPLETE, index 
== total - 1);
+
+                // update pending number of exchanges
+                pendingExchanges = total - index - 1;
 
-            // a rollback only exchange (without an exception) did not 
complete successfully either; markRollbackOnlyLast
-            // reaches the consumer as the last transaction when no 
transacted() EIP consumed the flag, so honor it too
-            boolean failed = exchange.isFailed() || exchange.isRollbackOnly() 
|| exchange.isRollbackOnlyLast();
-
-            if (getEndpoint().isTransacted() && failed) {
-                // break out as we are transacted and should rollback
-                Exception cause = exchange.getException();
-                if (cause == null) {
-                    // rollback only, there was no error (the exception must 
be created before the exchange is released)
-                    cause = new RollbackExchangeException(
-                            "Rollback transaction as the exchange was marked 
as rollback only", exchange);
+                // process the current exchange
+                try {
+                    getProcessor().process(exchange);
+                } catch (Exception e) {
+                    exchange.setException(e);
                 }
-                // must release exchange
-                releaseExchange(exchange, false);
-                throw cause;
-            }
 
-            // pick the on consume to use
-            String sql = failed ? onConsumeFailed : onConsume;
-            try {
-                // we can only run on consume if there was data
-                if (data != null && sql != null) {
-                    int updateCount;
-                    if (namedJdbcTemplate != null && sqlProcessingStrategy 
instanceof SqlNamedProcessingStrategy) {
-                        SqlNamedProcessingStrategy namedProcessingStrategy = 
(SqlNamedProcessingStrategy) sqlProcessingStrategy;
-                        updateCount = 
namedProcessingStrategy.commit(getEndpoint(), exchange, data, namedJdbcTemplate,
-                                parameterSource, sql);
-                    } else {
-                        updateCount = 
sqlProcessingStrategy.commit(getEndpoint(), exchange, data, jdbcTemplate, sql);
-                    }
-                    if (expectedUpdateCount > -1 && updateCount != 
expectedUpdateCount) {
-                        String msg = "Expected update count " + 
expectedUpdateCount + " but was " + updateCount
-                                     + " executing query: " + sql;
-                        throw new SQLException(msg);
+                // a rollback only exchange (without an exception) did not 
complete successfully either; markRollbackOnlyLast
+                // reaches the consumer as the last transaction when no 
transacted() EIP consumed the flag, so honor it too
+                boolean failed = exchange.isFailed() || 
exchange.isRollbackOnly() || exchange.isRollbackOnlyLast();
+
+                if (getEndpoint().isTransacted() && failed) {
+                    // break out as we are transacted and should rollback
+                    Exception cause = exchange.getException();
+                    if (cause == null) {
+                        // rollback only, there was no error (the exception 
must be created before the exchange is released)
+                        cause = new RollbackExchangeException(
+                                "Rollback transaction as the exchange was 
marked as rollback only", exchange);
                     }
+                    // must release exchange
+                    releaseExchange(exchange, false);
+                    throw cause;
                 }
-            } catch (Exception e) {
-                if (breakBatchOnConsumeFail) {
-                    throw e;
-                } else {
-                    handleException("Error executing onConsume/onConsumeFailed 
query " + sql, e);
+
+                // pick the on consume to use
+                String sql = failed ? onConsumeFailed : onConsume;
+                try {
+                    // we can only run on consume if there was data
+                    if (data != null && sql != null) {
+                        int updateCount;
+                        if (namedJdbcTemplate != null && sqlProcessingStrategy 
instanceof SqlNamedProcessingStrategy) {
+                            SqlNamedProcessingStrategy namedProcessingStrategy
+                                    = (SqlNamedProcessingStrategy) 
sqlProcessingStrategy;
+                            updateCount = 
namedProcessingStrategy.commit(getEndpoint(), exchange, data, namedJdbcTemplate,
+                                    parameterSource, sql);
+                        } else {
+                            updateCount = 
sqlProcessingStrategy.commit(getEndpoint(), exchange, data, jdbcTemplate, sql);
+                        }
+                        if (expectedUpdateCount > -1 && updateCount != 
expectedUpdateCount) {
+                            String msg = "Expected update count " + 
expectedUpdateCount + " but was " + updateCount
+                                         + " executing query: " + sql;
+                            throw new SQLException(msg);
+                        }
+                    }
+                } catch (Exception e) {
+                    if (breakBatchOnConsumeFail) {
+                        throw e;
+                    } else {
+                        handleException("Error executing 
onConsume/onConsumeFailed query " + sql, e);
+                    }
+                } finally {
+                    releaseExchange(exchange, false);
                 }
-            } finally {
-                releaseExchange(exchange, false);
             }
+        } finally {
+            // the exchanges of the rows that were not routed: the consumer is 
stopping, or a failed row or a failed onConsume
+            // query ended the batch (transacted, breakBatchOnConsumeFail)
+            releaseNotRouted(exchanges);
+        }
+
+        if (index < total) {
+            // the consumer is stopping and routed only part of the batch. The 
onConsumeBatchComplete query is for the
+            // whole batch (it cannot tell the rows apart, for example "delete 
from projects"): it is not run, so the
+            // rows that were not routed are left as they are and are consumed 
again by the next poll
+            if (onConsumeBatchComplete != null) {
+                LOG.info("Not running the onConsumeBatchComplete query, as the 
consumer is stopping and routed only {} of"
+                         + " the {} rows of the batch",
+                        index, total);
+            }
+            return total;
         }
 
         try {
@@ -349,6 +369,16 @@ public class SqlConsumer extends 
ScheduledBatchPollingConsumer {
         return total;
     }
 
+    /**
+     * Releases the exchanges of the rows that the consumer did not route, as 
it is stopping or the batch ended early.
+     */
+    private void releaseNotRouted(Queue<Object> exchanges) {
+        Object next;
+        while ((next = exchanges.poll()) != null) {
+            releaseExchange(ObjectHelper.cast(DataHolder.class, 
next).exchange, false);
+        }
+    }
+
     public String getOnConsume() {
         return onConsume;
     }
diff --git 
a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerBatchCompleteStopTest.java
 
b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerBatchCompleteStopTest.java
new file mode 100644
index 000000000000..dfdf113ab9cd
--- /dev/null
+++ 
b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerBatchCompleteStopTest.java
@@ -0,0 +1,113 @@
+/*
+ * 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.camel.component.sql;
+
+import java.util.List;
+import java.util.concurrent.CompletableFuture;
+import java.util.concurrent.CountDownLatch;
+import java.util.concurrent.TimeUnit;
+
+import org.apache.camel.builder.RouteBuilder;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.apache.camel.test.junit6.CamelTestSupport;
+import org.junit.jupiter.api.Test;
+import org.springframework.jdbc.core.JdbcTemplate;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
+
+import static org.awaitility.Awaitility.await;
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+/**
+ * The onConsumeBatchComplete query runs after the entire batch was processed: 
a route stopped while it routes the first
+ * row of a batch of three routes only that row, and the query does not run 
for the two rows it did not route.
+ */
+class SqlConsumerBatchCompleteStopTest extends CamelTestSupport {
+
+    private final CountDownLatch firstRowArrived = new CountDownLatch(1);
+    private final CountDownLatch releaseFirstRow = new CountDownLatch(1);
+    private EmbeddedDatabase db;
+    private JdbcTemplate jdbcTemplate;
+
+    @Override
+    public void doPreSetup() throws Exception {
+        db = new EmbeddedDatabaseBuilder()
+                .setName(getClass().getSimpleName())
+                .setType(EmbeddedDatabaseType.H2)
+                .addScript("sql/createAndPopulateDatabase.sql").build();
+
+        jdbcTemplate = new JdbcTemplate(db);
+    }
+
+    @Override
+    public void doPostTearDown() throws Exception {
+        if (db != null) {
+            db.shutdown();
+        }
+    }
+
+    @Test
+    void testStopDuringBatchDoesNotRunBatchCompleteQuery() throws Exception {
+        MockEndpoint mock = getMockEndpoint("mock:result");
+        mock.expectedBodiesReceived("Camel");
+
+        context.getRouteController().startRoute("batch");
+        assertTrue(firstRowArrived.await(10, TimeUnit.SECONDS), "The first row 
of the batch should be routed");
+
+        // a graceful stop of the route while it routes the first row: the 
consumer completes the current row only
+        SqlConsumer consumer = (SqlConsumer) 
context.getRoute("batch").getConsumer();
+        CompletableFuture<Void> stop = CompletableFuture.runAsync(() -> {
+            try {
+                context.getRouteController().stopRoute("batch");
+            } catch (Exception e) {
+                throw new IllegalStateException(e);
+            }
+        });
+        // the consumer routes no further row of the batch
+        await().atMost(10, TimeUnit.SECONDS).until(() -> 
!consumer.isBatchAllowed());
+        releaseFirstRow.countDown();
+        stop.get(20, TimeUnit.SECONDS);
+
+        MockEndpoint.assertIsSatisfied(context);
+        // the routed row was deleted by onConsume, the two rows that were not 
routed are still there
+        assertEquals(List.of(2, 3),
+                jdbcTemplate.queryForList("select id from projects order by 
id", Integer.class));
+    }
+
+    @Override
+    protected RouteBuilder createRouteBuilder() {
+        return new RouteBuilder() {
+            @Override
+            public void configure() {
+                getContext().getComponent("sql", 
SqlComponent.class).setDataSource(db);
+
+                from("sql:select * from projects order by 
id?initialDelay=0&delay=50"
+                     + "&onConsume=delete from projects where id = :#id"
+                     + "&onConsumeBatchComplete=delete from projects")
+                        .routeId("batch").autoStartup(false)
+                        .process(exchange -> {
+                            firstRowArrived.countDown();
+                            assertTrue(releaseFirstRow.await(20, 
TimeUnit.SECONDS));
+                        })
+                        .setBody(simple("${body[project]}"))
+                        .to("mock:result");
+            }
+        };
+    }
+}
diff --git 
a/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerBatchEndsEarlyReleaseTest.java
 
b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerBatchEndsEarlyReleaseTest.java
new file mode 100644
index 000000000000..6db89fc3812c
--- /dev/null
+++ 
b/components/camel-sql/src/test/java/org/apache/camel/component/sql/SqlConsumerBatchEndsEarlyReleaseTest.java
@@ -0,0 +1,111 @@
+/*
+ * 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.camel.component.sql;
+
+import org.apache.camel.CamelContext;
+import org.apache.camel.builder.RouteBuilder;
+import org.apache.camel.component.mock.MockEndpoint;
+import org.apache.camel.spi.ExchangeFactory;
+import org.apache.camel.test.junit6.CamelTestSupport;
+import org.junit.jupiter.api.Test;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabase;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
+import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+import static org.junit.jupiter.api.Assertions.assertThrows;
+
+/**
+ * A batch of three rows that ends with an error after its first row 
(transacted, breakBatchOnConsumeFail): the
+ * exchanges of the two rows that were not routed are released too.
+ */
+class SqlConsumerBatchEndsEarlyReleaseTest extends CamelTestSupport {
+
+    private EmbeddedDatabase db;
+
+    @Override
+    protected CamelContext createCamelContext() throws Exception {
+        CamelContext camelContext = super.createCamelContext();
+        // count the exchanges the consumers create and release (enabled 
before the consumers are created)
+        
camelContext.getCamelContextExtension().getExchangeFactory().setStatisticsEnabled(true);
+        return camelContext;
+    }
+
+    @Override
+    public void doPreSetup() throws Exception {
+        db = new EmbeddedDatabaseBuilder()
+                .setName(getClass().getSimpleName())
+                .setType(EmbeddedDatabaseType.H2)
+                .addScript("sql/createAndPopulateDatabase.sql").build();
+    }
+
+    @Override
+    public void doPostTearDown() throws Exception {
+        if (db != null) {
+            db.shutdown();
+        }
+    }
+
+    @Test
+    void testTransactedFailureReleasesTheRowsNotRouted() throws Exception {
+        pollOnceAndCheckReleased("transacted", "mock:transacted");
+    }
+
+    @Test
+    void testBreakBatchOnConsumeFailReleasesTheRowsNotRouted() throws 
Exception {
+        pollOnceAndCheckReleased("break", "mock:break");
+    }
+
+    private void pollOnceAndCheckReleased(String routeId, String mockUri) 
throws Exception {
+        MockEndpoint mock = getMockEndpoint(mockUri);
+        mock.expectedBodiesReceived("Camel");
+
+        // polled from the test: the route is synchronous, so the exchanges 
are done when poll returns or throws
+        SqlConsumer consumer = (SqlConsumer) 
context.getRoute(routeId).getConsumer();
+        assertThrows(Exception.class, consumer::poll);
+
+        mock.assertIsSatisfied();
+        // the exchange of the query, the one of the routed row and those of 
the two rows that were not routed
+        ExchangeFactory.Statistics stats = 
context.getCamelContextExtension().getExchangeFactoryManager().getStatistics();
+        assertEquals(4, stats.getCreatedCounter() + 
stats.getAcquiredCounter());
+        assertEquals(4, stats.getReleasedCounter());
+    }
+
+    @Override
+    protected RouteBuilder createRouteBuilder() {
+        return new RouteBuilder() {
+            @Override
+            public void configure() {
+                getContext().getComponent("sql", 
SqlComponent.class).setDataSource(db);
+
+                // the first row fails, and a transacted consumer breaks out 
of the batch
+                from("sql:select * from projects order by 
id?startScheduler=false&transacted=true")
+                        .routeId("transacted")
+                        .setBody(simple("${body[project]}"))
+                        .to("mock:transacted")
+                        .throwException(new IllegalStateException("Forced"));
+
+                // the onConsume query of the first row updates no row, and 
breakBatchOnConsumeFail breaks out of the batch
+                from("sql:select * from projects order by 
id?startScheduler=false&breakBatchOnConsumeFail=true"
+                     + "&onConsume=update projects set license = 'ASF' where 
id = -1&expectedUpdateCount=1")
+                        .routeId("break")
+                        .setBody(simple("${body[project]}"))
+                        .to("mock:break");
+            }
+        };
+    }
+}
diff --git 
a/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc 
b/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
index 677e16afa2a5..06a65126c967 100644
--- a/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
+++ b/docs/user-manual/modules/ROOT/pages/camel-4x-upgrade-guide-4_24.adoc
@@ -4305,6 +4305,20 @@ again. With `transacted=true` the SQL and MyBatis 
consumers break out of the bat
 `markRollbackOnlyLast()` reaches the consumer only when no `transacted()` EIP 
has consumed the flag, so the
 consumer's own transaction is the last one.
 
+=== camel-sql - onConsumeBatchComplete is not run when the consumer stops 
before the end of the batch
+
+The `onConsumeBatchComplete` query runs after the consumer has routed the 
whole batch. When the consumer was stopped
+in the middle of a batch (a route stop, a CamelContext shutdown or a route 
reload), it still ran the query. A query
+such as `delete from projects`, or an update that marks the rows as processed, 
also deleted or marked the rows that
+were never routed, and those rows were lost. The query is now not run when the 
consumer routed only part of the batch
+(logged at INFO), and the next poll reads the rows that were not routed.
+
+A route that uses `onConsumeBatchComplete` alone (without `onConsume`) to 
delete or mark the rows behaves
+differently. Before, the rows of a cut-short batch that were not routed were 
lost. Now the whole batch is left as it is, so
+the rows that were already routed are routed again after a restart. Use 
`onConsume` to delete or mark each row as it
+is routed, or set `shutdownRunningTask=CompleteAllTasks` on the route, so that 
a graceful stop routes the whole batch
+(within the shutdown timeout) and the query runs.
+
 === camel-sql - sql and sql-stored with batch=true: the update count and a 
single Map body
 
 With `batch=true` the `CamelSqlUpdateCount` (sql) and 
`CamelSqlStoredUpdateCount` (sql-stored) headers no longer add

Reply via email to