osheroff / mysql-binlog-connector-java

MySQL Binary Log connector
641 stars 161 forks source link

GEOMETRY type data Inconsistent with the inserted value #123

Closed AwarenessTrend closed 10 months ago

AwarenessTrend commented 10 months ago

mysql version is 8.0.32 and run in docker, docker image is mysql:8.0.32-debian@sha256:3e54e01ca605ee64672bf588cd1782a669cb387954593ccd1c9dcd551598d1df mysql-binlog-connector-java version is

<dependency>
          <groupId>com.zendesk</groupId>
          <artifactId>mysql-binlog-connector-java</artifactId>
          <version>0.28.1</version>
</dependency>

table ddl is :

CREATE TABLE test_table (
   geometry_column GEOMETRY,
   point_column POINT,
   linestring_column LINESTRING,
   polygon_column POLYGON,
   geometrycollection_column GEOMETRYCOLLECTION,
   multilinestring_column MULTILINESTRING,
   multipoint_column MULTIPOINT,
   multipolygon_column MULTIPOLYGON
);

insert sql is :

INSERT INTO test_table (
            geometry_column,
            point_column,
            linestring_column,
            polygon_column,
            geometrycollection_column,
            multilinestring_column,
            multipoint_column,
            multipolygon_column
) VALUES (
        ST_GeomFromText('POINT(1 1)'),
        ST_GeomFromText('POINT(2 2)'),
        ST_GeomFromText('LINESTRING(1 1, 2 2)'),
        ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
        ST_GeomFromText('GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(1 1, 2 2))'),
        ST_GeomFromText('MULTILINESTRING((1 1, 2 2),(3 3, 4 4))'),
        ST_GeomFromText('MULTIPOINT((1 1),(2 2))'),
        ST_GeomFromText('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)),((2 2, 3 2, 3 3, 2 3, 2 2)))')
);

After executing SQL, I copy binlog file to local and parse , this is EventDeserializer config, other config is default

   EventDeserializer eventDeserializer = new EventDeserializer();
        eventDeserializer.setCompatibilityMode(
                EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG,
                EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
        );

this is parse GEOMETRY type parse code, WKBReader is jts API image I found that the parsed result is inconsistent with the inserted,parsed result always POINT(0 0)

Further debugging after I found SELECT HEX(ST_AsBinary(ST_GeomFromText('POINT(1 1)')));
corresponding wkb byte[] is [1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, -16, 63, 0, 0, 0, 0, 0, 0, -16, 63] array length is 21

but binlog resolved wkb byte[] is [0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, -16, 63, 0, 0, 0, 0, 0, 0, -16, 63] array length is 25 not just GEOMETRY other filed resolved array all big 4 length relative ST_AsBinary Is this normal?

in create table statement all field, inconsistent insertion and parsing values will occur, this is a bug or problem with my parsing method?

AwarenessTrend commented 10 months ago

The problem has been resolved