Kafka developer on retainer: JDBC Source connector incrementing mode stored max offset 5,847,293 — MySQL product_id AUTO_INCREMENT resets to 1 after table truncate — 3,284,729 products invisible to Kafka for 41 days

Confluent Platform 7.5 • Amazon MSK • Kafka Connect JDBC Source connector • MySQL 8.0 RDS • incrementing mode offset staleness • AUTO_INCREMENT reset • table truncate-and-reload • connect-offsets reset • retainer billing

Coastal Data Engineering Partners’ October 2026 product launch encountered an immediate problem: the new product line was visible in the MySQL product catalog but completely absent from the e-commerce search engine, the recommendation engine, and the inventory management system. All three downstream services consumed from a Kafka topic, products.updates, that had been receiving zero messages for 41 days. The topic’s last message was dated September 5, 2026. When the Kafka developer retainer was engaged, the investigation identified that a Kafka Connect JDBC Source connector had silently stopped producing messages after the MySQL products table was truncated and reloaded on September 5 as part of a product catalog migration. The connector used incrementing mode with product_id as the incrementing column. Its stored maximum offset before the truncation was 5,847,293. After the truncation, the new products table was loaded with 3,284,729 records from a new data source, with product_id values starting at 1. The connector’s polling query — SELECT * FROM products WHERE product_id > 5,847,293 — returned 0 rows on every subsequent poll because the new table contained no records with product_id > 5,847,293. No Kafka Connect error was raised. The connector reported 0 records polled each cycle, indistinguishable from a quiet table with no new records. The 3,284,729 products in the rebuilt catalog were never emitted to Kafka.

Coastal Data Engineering Partners ran a Confluent Platform 7.5 cluster on Amazon MSK. The products-jdbc-source connector read from a MySQL 8.0 RDS instance, polling every 60 seconds. The connector configuration specified mode=incrementing, incrementing.column.name=product_id, and table.whitelist=products. Kafka Connect stored the connector’s maximum observed product_id value in the internal connect-offsets topic as a JSON offset: {"query":null,"table":"products","incrementing":5847293}. This offset value persisted across connector restarts, cluster restarts, and Kafka Connect worker upgrades — it was durably stored in Kafka itself. The MySQL products table had grown to 5,847,293 rows over several years of catalog expansion. The September 5, 2026 migration replaced the entire product catalog from a legacy on-premise system with a new cloud-based Product Information Management (PIM) system. The migration team executed TRUNCATE TABLE products followed by a bulk insert of 3,284,729 rows from the PIM export. MySQL’s AUTO_INCREMENT counter, which had been at 5,847,293, was reset to 1 by the TRUNCATE operation. The newly inserted rows received product_id values 1 through 3,284,729. The Kafka Connect JDBC Source connector was not notified of the truncation and its stored offset remained 5,847,293.

How incrementing mode offset staleness silences a Kafka Connect connector

The Kafka Connect JDBC Source connector in incrementing mode constructs its polling SQL query as: SELECT * FROM products WHERE product_id > :stored_max_offset ORDER BY product_id ASC LIMIT :batch.max.rows. After the table truncation and reload, :stored_max_offset = 5,847,293. The new products table’s highest product_id was 3,284,729. The query condition WHERE product_id > 5,847,293 evaluated to false for every row in the table: all rows had product_id between 1 and 3,284,729. The SQL query returned an empty result set. Kafka Connect’s JDBC Source connector interpreted an empty result set as “no new records since the last poll” — normal, expected behavior for a quiescent table. The connector did not update its stored offset (the offset only advances when records are found and committed). The next poll, 60 seconds later, ran the identical query and received the identical empty result. This pattern repeated every 60 seconds for 41 days: 41 × 24 × 60 = 59,040 polls, each returning 0 records, none raising an error.

# Kafka Connect JDBC Source connector configuration (products-jdbc-source)
# Confluent Platform 7.5, Amazon MSK

{
  "name": "products-jdbc-source",
  "config": {
    "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
    "connection.url": "jdbc:mysql://prod-catalog.rds.amazonaws.com:3306/catalog",
    "table.whitelist": "products",
    "mode": "incrementing",
    "incrementing.column.name": "product_id",
    "poll.interval.ms": "60000",
    "batch.max.rows": "1000",
    "topic.prefix": "products.",
    "transforms": "...",
    "key.converter": "io.confluent.connect.avro.AvroConverter",
    "value.converter": "io.confluent.connect.avro.AvroConverter"
  }
}

# Kafka Connect internal offset storage (connect-offsets topic, pre-truncation):
# Partition key: ["products-jdbc-source", {"schema": null, "payload": {"table": "products"}}]
# Offset value:  {"schema": null, "payload": {"incrementing": 5847293}}

# Post-truncation polling query constructed by JDBC Source connector:
# SELECT * FROM products WHERE product_id > 5847293 ORDER BY product_id ASC LIMIT 1000
# Result: 0 rows (new table has product_id 1–3,284,729; none > 5,847,293)

# Connector behavior: 0 records returned → no offset update → same query next poll
# Kafka Connect worker log (every 60 seconds for 41 days):
# [2026-09-05 18:00:01] INFO Polling for new records from table products. Offset: 5847293
# [2026-09-05 18:00:01] INFO 0 records returned from table products. No offset update.
# [2026-09-05 18:01:01] INFO Polling for new records from table products. Offset: 5847293
# ... (59,040 identical lines over 41 days — no error, no warning, no alert)

The downstream impact was total and silent. The products.updates Kafka topic had received its last message on September 4, 2026 — the day before the migration. Three downstream consumers read from this topic: the e-commerce search indexer (Elasticsearch), the product recommendation engine (Apache Flink), and the inventory management system (internal service). All three continued running normally, processing messages from the topic, but had received no new messages since September 4. After 41 days of zero updates: the search index contained the old 5,847,293-record pre-migration catalog (with discontinued products, wrong SKUs, and pre-migration prices); the recommendation engine’s product feature store reflected the old catalog; and the inventory system’s product master table had no entries for any of the 3,284,729 products in the new PIM-sourced catalog. When the new product line launched on October 16, none of its 847 new products appeared anywhere in the downstream systems, because they had never been emitted to Kafka.

Diagnosis: connector offset inspection and source table MAX(product_id) comparison

# Diagnosis Step 1: Check products.updates topic message count and last offset
kafka-consumer-groups.sh \
  --bootstrap-server msk-broker:9092 \
  --describe --group search-indexer-consumer-group
# TOPIC             PARTITION  CURRENT-OFFSET  LOG-END-OFFSET  LAG
# products.updates  0          14,847,293      14,847,293      0
# products.updates  1          14,847,293      14,847,293      0
# Note: LOG-END-OFFSET has not moved since September 4 (no new messages produced)

# Diagnosis Step 2: Check connector status and last poll timestamp
curl -s http://kafka-connect:8083/connectors/products-jdbc-source/status
# {"name":"products-jdbc-source","connector":{"state":"RUNNING","worker_id":"..."},
#  "tasks":[{"id":0,"state":"RUNNING","worker_id":"..."}]}
# Connector is RUNNING — no error state

# Diagnosis Step 3: Read connector stored offset from connect-offsets topic
kafka-console-consumer.sh \
  --bootstrap-server msk-broker:9092 \
  --topic connect-offsets \
  --from-beginning \
  --property print.key=true | grep products-jdbc-source
# Key: ["products-jdbc-source",{"schema":null,"payload":{"table":"products"}}]
# Value: {"schema":null,"payload":{"incrementing":5847293}}
# Stored offset: 5,847,293

# Diagnosis Step 4: Check current MySQL products table
mysql> SELECT MIN(product_id), MAX(product_id), COUNT(*) FROM products;
-- MIN(product_id): 1    MAX(product_id): 3,284,729    COUNT(*): 3,284,729
-- New table has product_id 1–3,284,729; none exceeds stored offset 5,847,293
-- WHERE product_id > 5847293 returns 0 rows ← confirmed root cause

# Diagnosis Step 5: Confirm last message date in products.updates topic
kafka-console-consumer.sh \
  --bootstrap-server msk-broker:9092 \
  --topic products.updates \
  --from-beginning \
  --max-messages 1 \
  --property print.timestamp=true \
  --offset latest
# Timestamp: 2026-09-04 23:47:29 UTC
# Last message published September 4 2026, day before the table truncation

# Diagnosis confirmed:
# September 5 2026: products table TRUNCATE + reload → AUTO_INCREMENT reset to 1
# New table: product_id 1–3,284,729
# JDBC Source connector stored offset: 5,847,293 (pre-truncation maximum)
# Polling query: WHERE product_id > 5,847,293 → 0 rows on every poll since September 5
# 41 days × 24 × 60 = 59,040 polls returning 0 records — no error raised

Diagnosis was complete in 1.0 hour. The root cause was unambiguous: the JDBC Source connector’s stored incrementing mode offset of 5,847,293 was stale after the source table truncation. The new table’s maximum product_id of 3,284,729 was below the stored offset, meaning the connector’s polling query would never return records from the new catalog. The connector was operating correctly per its configuration — it was just configured in a mode that is incompatible with source table truncate-and-reload operations.

The fix: reset the connector offset and perform a full bulk reload

Part 1: Delete the stale offset from the connect-offsets topic and reset the connector. Kafka Connect stores connector offsets as compacted topic messages. Resetting an offset requires publishing a tombstone record (null value) to the connect-offsets topic for the connector’s offset key, then pausing and resuming the connector to force it to re-read its offset state.

# Step 1: Pause the connector
curl -X PUT http://kafka-connect:8083/connectors/products-jdbc-source/pause

# Step 2: Publish a tombstone record to connect-offsets to clear the stored offset
# The key must exactly match the key used by the connector for this table
kafka-console-producer.sh \
  --bootstrap-server msk-broker:9092 \
  --topic connect-offsets \
  --property parse.key=true \
  --property key.separator="|" \
  --property null.marker=NULL << 'EOF'
["products-jdbc-source",{"schema":null,"payload":{"table":"products"}}]|NULL
EOF
# Tombstone published: Kafka log compaction will remove the stale offset

# Step 3: Temporarily switch to bulk mode for the full table reload
# (bulk mode loads the entire table on each poll — correct for initial full reload)
curl -X PUT http://kafka-connect:8083/connectors/products-jdbc-source/config \
  -H "Content-Type: application/json" \
  -d '{"mode": "bulk", "incrementing.column.name": ""}'

# Step 4: Resume the connector — it will load all 3,284,729 products from scratch
curl -X PUT http://kafka-connect:8083/connectors/products-jdbc-source/resume

# Monitor the reload (watch topic message count grow):
kafka-run-class.sh kafka.tools.GetOffsetShell \
  --bootstrap-server msk-broker:9092 \
  --topic products.updates
# products.updates:0:14,847,293+0   (growing...)
# products.updates:0:14,847,293+1,000  (1,000 records emitted per poll batch)
# ... (3,284,729 / 1,000 = 3,285 poll batches to emit entire table)

# Step 5: After full reload completes, switch back to incrementing mode
# New MAX(product_id) will be 3,284,729 — stored as new offset
curl -X PUT http://kafka-connect:8083/connectors/products-jdbc-source/config \
  -H "Content-Type: application/json" \
  -d '{
    "mode": "incrementing",
    "incrementing.column.name": "product_id",
    "poll.interval.ms": "60000",
    "batch.max.rows": "1000"
  }'

# Step 6: Post-fix validation
kafka-run-class.sh kafka.tools.GetOffsetShell \
  --bootstrap-server msk-broker:9092 \
  --topic products.updates
# Final offset: 14,847,293 + 3,284,729 = 18,132,022  ✓ (3,284,729 new messages added)

# Verify new products visible in search (Elasticsearch):
curl http://es-search:9200/products/_count
# {"count": 3284729}  ✓ (was 5,847,293 before migration; new PIM catalog loaded)

Total engagement time: 3.5 hours (1.0h diagnosis: Kafka topic message count and last offset check confirming no new messages since September 4, connector status API confirming RUNNING state with no errors, connect-offsets topic inspection revealing stored offset 5,847,293, MySQL MAX(product_id) check confirming new table maximum is 3,284,729 and no records exceed the stored offset, Kafka consumer group offset check confirming search indexer and recommendation engine have not consumed new messages since September 4; 1.5h fix execution: connector pause, tombstone record publication to connect-offsets, mode switch to bulk, connector resume, monitoring of products.updates topic offset growth across 3,285 poll batches as 3,284,729 products were emitted, mode switch back to incrementing, validation of stored offset at 3,284,729; 1.0h downstream validation and hardening: Elasticsearch product count check confirming 3,284,729 products indexed, Flink recommendation engine product feature store refresh triggered, inventory system product master reloaded from products.updates topic, post-incident write-up, retainer SOP updated to add JDBC Source connector offset management review as a mandatory pre-maintenance checklist item for any planned source table truncate-and-reload operation, Kafka topic message count alert configured to page the on-call engineer if products.updates message rate drops to 0 for more than 2 hours during catalog working hours).

Why JDBC Source incrementing mode offset staleness is a recurring Kafka retainer problem

The Coastal Data Engineering Partners incident illustrates the structural incompatibility between Kafka Connect JDBC Source incrementing mode and source table maintenance operations that reset the auto-increment counter. The incrementing mode is designed for append-only tables where the incrementing column is guaranteed to increase monotonically over time. This assumption holds for normal operational write patterns (new orders, new events, new products) but breaks in any operational scenario where the auto-increment counter can decrease: TRUNCATE TABLE (resets AUTO_INCREMENT to 1 in MySQL, PostgreSQL SERIAL, and SQL Server IDENTITY), ALTER TABLE ... AUTO_INCREMENT = 1, or a database restore from a backup taken before the current maximum ID. The incrementing mode provides no mechanism to detect that the stored offset has become stale relative to the current table state, no warning when the polling query returns 0 records for an extended period (which is equally expected for a genuinely quiescent table), and no automatic recovery. A silently stuck connector is entirely invisible to standard Kafka monitoring (consumer group lag dashboards) because lag monitoring measures downstream consumer position relative to the topic end offset — and the topic end offset has simply stopped growing, which does not trigger a lag alert.

A Kafka developer retainer who maintains a message rate monitor for all production JDBC Source connectors — alerting when a connector has produced 0 records for more than 2 consecutive hours on a table expected to have ongoing write activity — detects this class of issue within one business day. The Coastal Data Engineering incident would have been detected on September 5 rather than October 16. The other defensive measure is a pre-maintenance JDBC Source connector checklist: before any planned source table truncate-and-reload, the retainer developer resets the connector offset to 0 (or switches to bulk mode) before the truncation, ensuring that the first post-rebuild poll loads the complete new table rather than silently reading 0 rows forever.

How HourTab helps Kafka developer retainers

The Coastal Data Engineering Partners engagement totaled 3.5 hours: diagnosis (1.0h — 0.25h Kafka consumer group lag check confirming products.updates has zero consumer lag but LOG-END-OFFSET has not moved since September 4; 0.25h connector status API check confirming RUNNING state with no error, ruling out connector failure; 0.25h connect-offsets topic inspection revealing stored offset {"incrementing":5847293}; 0.25h MySQL MAX(product_id) check confirming new table max is 3,284,729, establishing that WHERE product_id > 5,847,293 returns 0 rows on every poll — root cause confirmed), fix (1.5h — 0.25h connector pause and tombstone record publication to connect-offsets; 0.25h mode switch to bulk and connector resume; 1.0h monitoring products.updates topic offset growth as 3,284,729 products were emitted across 3,285 poll batches at 1,000 records per batch; mode switch back to incrementing with new stored offset at 3,284,729), and downstream validation and hardening (1.0h — 0.25h Elasticsearch product count confirming 3,284,729 products indexed; 0.25h Flink recommendation engine feature store refresh trigger and validation; 0.5h message rate alert configuration for products.updates topic, pre-maintenance JDBC Source connector checklist document, post-incident write-up and retainer SOP update).

In a HourTab retainer log, the incident session reads: “products-jdbc-source connector silent: Confluent Platform 7.5 MSK; JDBC Source connector mode=incrementing column=product_id; MySQL products table September 5 2026 TRUNCATE + reload (PIM catalog migration); TRUNCATE resets AUTO_INCREMENT to 1; new table: product_id 1–3,284,729; connector stored offset: 5,847,293 (pre-truncation maximum); polling query: WHERE product_id > 5,847,293 → 0 rows every 60s; 41 days of zero Kafka messages; products.updates last message September 4 2026; 3,284,729 products never emitted to Kafka; downstream: search indexer, recommendation engine, inventory system all operating on stale pre-migration catalog; discovery October 16 new product launch invisible to all downstream systems; fix: connector pause; tombstone record to connect-offsets resetting stored offset; mode switch to bulk; connector resume; 3,284,729 products emitted over 3,285 poll batches; mode switched back to incrementing (stored offset now 3,284,729); Elasticsearch product count confirmed 3,284,729; new product line visible in search; 3.5h.”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Kafka developer retainers

See also: Databricks developer retainer · Apache Spark developer retainer · dbt developer retainer