# JDBC Source connector loses data in "incrementing" and "timestamp+incrementing" modes

**URL:** <https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175>\
**Category:** Kafka Connect\
**Created:** [22 October 2021 11:59 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175 "2021-10-22T11:59:32Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![whatsupbros](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/whatsupbros/32/239_2.png) [@whatsupbros](https://forum.confluent.io/u/whatsupbros)\
**Post date:** [22 October 2021 11:59 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/1 "2021-10-22T11:59:32Z")

</div>

Hello Kafkateers,

Noticed an issue with JDBC Source connectors and long transactions, which affect all operating modes including “incrementing” and “timestamp+incrementing”, which are [claimed](https://docs.confluent.io/kafka-connect-jdbc/current/source-connector/index.html#incremental-query-modes) to be stable.

There is a tracked outbox table `Table1` as following:

```auto
CREATE TABLE Table1 (
  i INTEGER NOT NULL,
  t TIMESTAMP NOT NULL,
  v VARCHAR2(2000)
);

```

Let’s imaging there are 2 sessions.

**Session 1** inserts a row to the table, but doesn’t commit the transaction yet:

```auto
INSERT INTO Table1 VALUES (1, SYSTIMESTAMP, 'row1');

```

**Session 2** inserts another row and commits it immediately:

```auto
INSERT INTO Table1 VALUES (2, SYSTIMESTAMP, 'row2');
COMMIT;

```

The connector sees “row2” and syncs it to our kafka topic.

Now **Session 1** commits its transaction:

```auto
COMMIT;

```

But “row1” is not seen by the connector and never synced to Kafka, despite being inserted later to the table, because it is behind connector’s stored offset already.

The issue may seem artificial, but in fact it’s very real. When you have a concurrent environment and transactions can be long enough, it happens often enough with columns, populated with a sequence value, when an earlier transaction finishes later, than the other one, which started later.

Are there any “good” workaround for the issue?  
I found only one using materialized views, and I don’t like it, probably other ideas? 🙂

---

<div class="post-metadata">

**Author:** ![ksilin](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/ksilin/32/220_2.png) [@ksilin](https://forum.confluent.io/u/ksilin)\
**Post date:** [28 October 2021 04:45 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/2 "2021-10-28T04:45:14Z")

</div>

Hi there. As a workaround, you can introduce a delay, using `timestamp.delay.interval.ms` as documented here: [https://docs.confluent.io/kafka-connect-jdbc/current/source-connector/source\_config\_options.html#database](https://docs.confluent.io/kafka-connect-jdbc/current/source-connector/source_config_options.html#database)

It only works in `timestamp.*` modes and does not guarantee that you will not lose data if a TX lasts longer than the delay, but it will catch a number of late arrivals.

---

<div class="post-metadata">

**Author:** ![whatsupbros](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/whatsupbros/32/239_2.png) [@whatsupbros](https://forum.confluent.io/u/whatsupbros)\
**Post date:** [28 October 2021 10:44 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/3 "2021-10-28T10:44:42Z")

</div>

Hi @ksilin!

Hmm, thank you for the idea, it is something, will look into it.

In Oracle (which is my data source) there is the `ORA_ROWSCN` pseudocolumn, which is populated only after commit, and is monotonously increasing. It also can be configured to be stored row-level (by default block-level), so it is a very good candidate to be used for tracking changed rows in the source table.

But, the connector doesn’t see it in the table, and if exposed to a view, then the connector doesn’t want to use it as `incrementing` column due to the fact its `nullable`:

```auto
org.apache.kafka.connect.errors.ConnectException: Cannot make incremental queries using incrementing column ORA_ROWSCN on KAFKA_SANDBOX.V_OUTBOX_TEST because this column is nullable.

```

It also doesn’t seem to be possible to be exposed to a materialized view with `FAST REFRESH ON COMMIT`…

Is there probably a way to make the connector use an incrementing column despite it being `nullable`?

---

<div class="post-metadata">

**Author:** ![ksilin](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/ksilin/32/220_2.png) [@ksilin](https://forum.confluent.io/u/ksilin)\
**Post date:** [28 October 2021 11:14 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/4 "2021-10-28T11:14:30Z")

</div>

ah, yeah, that’s the default setting. Using nullable columns is prohibited by default. You can try setting `validate.non.null=false` [https://docs.confluent.io/kafka-connect-jdbc/current/source-connector/source\_config\_options.html#mode](https://docs.confluent.io/kafka-connect-jdbc/current/source-connector/source_config_options.html#mode). However, I am not sure about the actual behavior of the connector, should some rows have NULLs in that column.

---

<div class="post-metadata">

**Author:** ![whatsupbros](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/whatsupbros/32/239_2.png) [@whatsupbros](https://forum.confluent.io/u/whatsupbros)\
**Post date:** [2 November 2021 08:56 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/5 "2021-11-02T08:56:50Z")

</div>

Oh gosh, I completely overlooked this config, will give it a try definitely.

The thing is that the column will not be `null` in fact - the `ora_rowscn` value may be empty only for unfinished transactions. On `commit` the value is populated with the current sequence change number. If this field is empty for committed data, then it is a bug, an Oracle Database bug 🙂

This means, for all data, visible by connector, the field will always be there, so should be no problems at all.

---

<div class="post-metadata">

**Author:** ![ksilin](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/ksilin/32/220_2.png) [@ksilin](https://forum.confluent.io/u/ksilin)\
**Post date:** [2 November 2021 16:40 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/6 "2021-11-02T16:40:53Z")

</div>

sounds good. Please, LMK once you have it running, just to confirm that it works as expected.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [2 December 2021 16:41 UTC](https://forum.confluent.io/t/jdbc-source-connector-loses-data-in-incrementing-and-timestamp-incrementing-modes/3175/7 "2021-12-02T16:41:20Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.