Bug #110399 Incorrect Signedness in Table Map Binlog Event for Int Following Year
Submitted: 16 Mar 2023 15:42 Modified: 20 Mar 2023 19:53
Reporter: Josh Wood Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Utilities: Binlog Events Severity:S3 (Non-critical)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[16 Mar 2023 15:42] Josh Wood
Description:
Binlog table map event contains SIGNEDNESS bit for numeric columns indicating signed INT column is unsigned when INT column follows a YEAR column. Expected behavior is that signed INT column following YEAR column does not have signedness bit set to 1 in table map events.

How to repeat:
Ensure binary logging is enabled and binlog_format is set to ROW.

Create a table with the following statement

CREATE TABLE `demo` (`year_col` YEAR, `int_col` INT, `primary_key` INT NOT NULL);

Insert a row

INSERT INTO `demo` (`year_col`, `int_col`, `primary_key`) VALUES (2041, 8489734, 123);

Examine binary log table map event prior to the insert, the table map event will indicate that `int_col` is an UNSIGNED INT but it was not created with the UNSIGNED attribute.
[17 Mar 2023 13:33] MySQL Verification Team
Hi Mr. Wood,

Thank you very much for your bug report.

We have used 8.0.32 and we do not see the issue.

Here is the relevant part of the binlog dump:

# at 556
#230317 15:28:34 server id 82721  end_log_pos 608 CRC32 0xec10e90f
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 0000022c 02 6b 14 64   13   21 43 01 00   34 00 00 00   60 02 00 00   00 00
# 0000023f 54 00 00 00 00 00 01 00  04 74 65 73 74 00 04 64 |T........test..d|
# 0000024f 65 6d 6f 00 03 0d 03 03  00 03 01 01 80 0f e9 10 |emo.............|
# 0000025f ec                                               |.|
# 	Table_map: `test`.`demo` mapped to number 84
# at 608
#230317 15:28:34 server id 82721  end_log_pos 653 CRC32 0xd600d972
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 00000260 02 6b 14 64   1e   21 43 01 00   2d 00 00 00   8d 02 00 00   00 00
# 00000273 54 00 00 00 00 00 01 00  02 00 03 ff 00 8d 06 8b |T...............|
# 00000283 81 00 7b 00 00 00 72 d9  00 d6                   |......r...|
# 	Write_rows: table id 84 flags: STMT_END_F

BINLOG '
AmsUZBMhQwEANAAAAGACAAAAAFQAAAAAAAEABHRlc3QABGRlbW8AAw0DAwADAQGAD+kQ7A==
AmsUZB4hQwEALQAAAI0CAAAAAFQAAAAAAAEAAgAD/wCNBouBAHsAAABy2QDW
'/*!*/;
# at 653
#230317 15:28:34 server id 82721  end_log_pos 684 CRC32 0x426f5060
# Position  Timestamp   Type   Master ID        Size      Master Pos    Flags
# 0000028d 02 6b 14 64   10   21 43 01 00   1f 00 00 00   ac 02 00 00   00 00
# 000002a0 08 00 00 00 00 00 00 00  60 50 6f 42             |.........PoB|
# 	Xid = 8
COMMIT/*!*/;
[17 Mar 2023 14:15] MySQL Verification Team
Hi,

This is even MUCH  better output. It shows all metadata:

CREATE TABLE `demo` (`year_col` YEAR, `int_col` INT, `primary_key` INT NOT NULL)
/*!*/;
# at 402
#230317 15:28:34 server id 82721  end_log_pos 481 CRC32 0x8de0ea44 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1679059714143051	immediate_commit_timestamp=1679059714143051	transaction_length=282
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1679059714143051 (2023-03-17 15:28:34.143051 EET)
# immediate_commit_timestamp=1679059714143051 (2023-03-17 15:28:34.143051 EET)
/*!80001 SET @@session.original_commit_timestamp=1679059714143051*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 481
#230317 15:28:34 server id 82721  end_log_pos 556 CRC32 0x045742d6 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1679059714/*!*/;
BEGIN
/*!*/;
# at 556
#230317 15:28:34 server id 82721  end_log_pos 608 CRC32 0xec10e90f 	Table_map: `test`.`demo` mapped to number 84
# Columns(YEAR,
#         INT,
#         INT NOT NULL)
# at 608
#230317 15:28:34 server id 82721  end_log_pos 653 CRC32 0xd600d972 	Write_rows: table id 84 flags: STMT_END_F

BINLOG '
AmsUZBMhQwEANAAAAGACAAAAAFQAAAAAAAEABHRlc3QABGRlbW8AAw0DAwADAQGAD+kQ7A==
AmsUZB4hQwEALQAAAI0CAAAAAFQAAAAAAAEAAgAD/wCNBouBAHsAAABy2QDW
'/*!*/;
# at 653
#230317 15:28:34 server id 82721  end_log_pos 684 CRC32 0x426f5060 	Xid = 8
COMMIT/*!*/;
# at 684
#230317 15:32:02 server id 82721  end_log_pos 761 CRC32 0x6c60a587 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no	original_committed_timestamp=1679059922152685	immediate_commit_timestamp=1679059922152685	transaction_length=206
# original_commit_timestamp=1679059922152685 (2023-03-17 15:32:02.152685 EET)
# immediate_commit_timestamp=1679059922152685 (2023-03-17 15:32:02.152685 EET)
/*!80001 SET @@session.original_commit_timestamp=1679059922152685*//*!*/;
/*!80014 SET @@session.original_server_version=80031*//*!*/;
/*!80014 SET @@session.immediate_server_version=80031*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 761
#230317 15:32:02 server id 82721  end_log_pos 890 CRC32 0xa0500c2e 	Query	thread_id=10	exec_time=0	error_code=0	Xid = 12
SET TIMESTAMP=1679059922/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
DROP TABLE `demo` /* generated by server */
/*!*/;
# at 890
#230317 15:37:55 server id 82721  end_log_pos 913 CRC32 0x5fcd1dab 	Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

As you can see, in 8.0.32 it is just fine ......
[20 Mar 2023 19:53] Josh Wood
Thank you for investigating. I see similar output for my binlog.

# at 1423
#230320 13:59:59 server id 1  end_log_pos 1475 CRC32 0xb0114116 	Table_map: `demo`.`demo` mapped to number 119
# at 1475
#230320 13:59:59 server id 1  end_log_pos 1520 CRC32 0xc91ee716 	Write_rows: table id 119 flags: STMT_END_F
### INSERT INTO `demo`.`demo`
### SET
###   @1=2041
###   @2=8489734
###   @3=123
# at 1520
#230320 13:59:59 server id 1  end_log_pos 1551 CRC32 0x64ee4d22 	Xid = 684
COMMIT/*!*/;
# at 1551

# hex snippet from 1423 to 1551
0000058f: 2fad 1864 1301 0000 0034 0000 00c3 0500  /..d.....4......
0000059f: 0000 0077 0000 0000 0001 0004 6465 6d6f  ...w........demo
000005af: 0004 6465 6d6f 0003 0d03 0300 0301 0180  ..demo..........
000005bf: 1641 11b0 2fad 1864 1e01 0000 002d 0000  .A../..d.....-..
000005cf: 00f0 0500 0000 0077 0000 0000 0001 0002  .......w........
000005df: 0003 ff00 8d06 8b81 007b 0000 0016 e71e  .........{......
000005ef: c92f ad18 6410 0100 0000 1f00 0000 0f06  ./..d...........
000005ff: 0000 0000 ac02 0000 0000 0000 224d ee64  ............"M.d

Since that is the case, a 3rd party library used for deserialization may be decoding the binlog incorrectly.
[21 Mar 2023 13:49] MySQL Verification Team
It is definitely so .....