Unsigned numeric types, do not work correctly during replication

[EvgeniyKirr]

github.com/go-mysql-org/go-mysql v1.7.0 mysql version: 8.0.19-10

CREATE TABLE `test`.`unsigned_test` (
  `Column1` bigint unsigned NOT NULL,
  `Column2` int unsigned NOT NULL,
  `Column3` mediumint unsigned NOT NULL,
  `Column4` smallint unsigned NOT NULL,
  `Column5` tinyint unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO test.unsigned_test
(Column1, Column2, Column3, Column4, Column5)
VALUES (9323372036854775807, 2247483647, 8888607, 33000, 200)

=== TableMapEvent ===
Date: 2023-02-07 17:45:30
Log position: 72807326
Event size: 69
TableID: 1305
TableID size: 6
Flags: 1
Schema: test
Table: unsigned_test
Column count: 5
Column type:
00000000  08 03 09 02 01                                    |.....|
NULL bitmap:
00000000  00                                                |.|
Signedness bitmap:
00000000  f8                                                |.|
Default charset: []
Column charset: []
Set str value: []
Enum str value: []
Column name: []
Geometry type: []
Primary key: []
Primary key prefix: []
Enum/set default charset: []
Enum/set column charset: []
UnsignedMap: map[int]bool{0:true, 1:true, 2:true, 3:true, 4:true}
CollationMap: map[int]uint64(nil)
EnumSetCollationMap: map[int]uint64(nil)
EnumStrValueMap: map[int][]string(nil)
SetStrValueMap: map[int][]string(nil)
GeometryTypeMap: map[int]uint64(nil)
Columns:
  <n/a>  type=8    unsigned=yes  null=no
  <n/a>  type=3    unsigned=yes  null=no
  <n/a>  type=9    unsigned=yes  null=no
  <n/a>  type=2    unsigned=yes  null=no
  <n/a>  type=1    unsigned=yes  null=no

=== WriteRowsEventV2 ===
Date: 2023-02-07 17:45:30
Log position: 72807380
Event size: 54
TableID: 1305
Flags: 1
Column count: 5
Values:
--
0:-9123372036854775809
1:-2047483649
2:-7888609
3:-32536
4:-56

Values are corrupted

9323372036854775807 -> -9123372036854775809 2247483647 -> -2047483649 8888607 -> -7888609 33000 -> -32536 200 -> -56

[lance6716]

Thanks for reporting, can you check the output of official mysqlbinlog? maybe it's a limitation of binlog protocol

[EvgeniyKirr]

Thanks for the reply. Here is the mysqlbinlog output. It really looks like it's mysql's troubles, although the correct values are also written

#230216 16:54:10 server id 2  end_log_pos 31101420 CRC32 0x1dc40ad2     Write_rows: table id 973 flags: STMT_END_F

BINLOG '
gjXuYxMCAAAARQAAALaR2gEAAM0DAAAAAAEACmxpdmVtYXN0ZXIADXVuc2lnbmVkX3Rlc3QABQgD
CQIBAAABAfihx/FO
gjXuYx4CAAAANgAAAOyR2gEAAM0DAAAAAAEAAgAF/wD//4ldeEVjgf/g9YUfoYfogMjSCsQd
'/*!*/;
### INSERT INTO `test`.`unsigned_test`
### SET
###   @1=-9123372036854775809 (9323372036854775807)
###   @2=-2047483649 (2247483647)
###   @3=-7888609 (8888607)
###   @4=-32536 (33000)
###   @5=-56 (200)
# at 31101420
#230216 16:54:10 server id 2  end_log_pos 31101451 CRC32 0x5a77dd3c     Xid = 8094968
COMMIT/*!*/;
# at 31101451

[baozinihao]

How did you solve this problem?

[mux]

The MySQL binary log does not contain sign information, this is why the output from mysqlbinlog shows two numbers, one where the bytes are interpreted as a signed number, and one where they are interpreted as an unsigned number. It is up to this code to get DDL information to know how to interpret those values correctly.

200 - 256 = -56 33000 - 65536 = -32536 etc