-
Task
-
Resolution: Done
-
Major
-
2.6.1.Final
-
None
-
False
-
None
-
False
In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.
Bug report
For bug reports, provide this information, please:
What Debezium connector do you use and what version?
Embedded Debezium with Postgres SQL.
Version: 2.6.1.Final
Also tested with 2.3.7.Final and 2.7.0.Alpha2
What is the connector configuration?
[Connector Config] connector.class = io.debezium.connector.postgresql.PostgresConnector slot.name = myslot publication.name = mypub hstore.handling.mode = json tombstones.on.delete = false topic.prefix = debezium binary.handling.mode = base64 flush.lsn.source = true interval.handling.mode = string errors.retry.delay.initial.ms = 300 value.converter = org.apache.kafka.connect.json.JsonConverter key.converter = org.apache.kafka.connect.json.JsonConverter publication.autocreate.mode = disabled database.user = test database.dbname = postgresTest offset.storage = io.customdz.DZOffsetStore max.queue.size.in.bytes = 268435456 time.precision.mode = connect offset.flush.timeout.ms = 30000 errors.retry.delay.max.ms = 10000 snapshot.max.threads = 4 column.include.list = public.alltypes.col_geography,public.alltypes.col_geometry,public.alltypes.col_smallserial plugin.name = pgoutput database.port = 33212 column.propagate.source.type = .* offset.flush.interval.ms = 10000 include.unknown.datatypes = true errors.max.retries = 0 database.hostname = 192.168.106.2 database.password = ******** name = postgres-connector table.include.list = public.alltypes snapshot.mode = initial_only
What is the captured database version and mode of deployment?
Local Postgres deployed in Docker using image "postgis/postgis:15-3.4-alpine"
What behaviour do you expect?
Events must have the columns col_geography and col_geometry with non null values during snapshot.
What behaviour do you see?
Both the columns col_geography and col_geometry have null values during snapshot (works fine during streaming).
Do you see the same behaviour using the latest released Debezium version?
(Ideally, also verify with latest Alpha/Beta/CR version)
Yes. Checked in 2.6.1.Final and 2.7.0.Alpha2
Do you have the connector logs, ideally from start till finish?
(You might be asked later to provide DEBUG/TRACE level log)
Yes. Attached below
How to reproduce the issue using our tutorial deployment?
In Postgres, run the below queries to setup the database:
-- Enables PostGIS extension that makes geography and geometry types available create extension if not exists postgis; -- Create table create table alltypes( col_smallserial smallserial primary key, col_geography geography, col_geometry geometry ); -- Insert one record insert into alltypes values ( default, ST_GeographyFromText('POINT(-22.6056 63.9850)'), 'LINESTRING(0 0, 1 1, 2 1, 2 2)' );
Run an initial Snapshot on the table using Debezium with the previously mentioned configuration (or similar).
The columns col_geography and col_geometry would be emitted with null values.
The same columns are emitted with the correct values during streaming.
On further debugging, saw that the problem is that the values are expected to be in hexadecimal but they are not. (attached the screenshot).
We can see from logs the errors:
... 2024-05-17 09:23:57.293 [WARN ] [pool-4-thread-1] [io.debezium.connector.postgresql.PostgresValueConverter] - Error converting to a Geography type: col_geography geography(2147483647, 0) DEFAULT VALUE NULL 2024-05-17 09:23:57.294 [WARN ] [pool-4-thread-1] [io.debezium.connector.postgresql.PostgresValueConverter] - Unexpected value for JDBC type 1111 and column col_geography geography(2147483647, 0) DEFAULT VALUE NULL: class=org.postgis.PGgeography 2024-05-17 09:23:57.295 [WARN ] [pool-4-thread-1] [io.debezium.connector.postgresql.PostgresValueConverter] - Error converting to a Geometry type: col_geometry geometry(2147483647, 0) DEFAULT VALUE NULL 2024-05-17 09:23:57.295 [WARN ] [pool-4-thread-1] [io.debezium.connector.postgresql.PostgresValueConverter] - Unexpected value for JDBC type 1111 and column col_geometry geometry(2147483647, 0) DEFAULT VALUE NULL: class=org.postgis.PGgeometry ...
Feature request or enhancement
For feature requests or enhancements, provide this information, please:
Which use case/requirement will be addressed by the proposed feature?
Geography and Geometry columns would be captured during snapshots.
Implementation ideas (optional)
Perhaps we can check if the value is actually a hex string. If not, convert the string value to bytes and then call "PostGisGeometry.fromEwkb(byte[] ewkb)".
