Bug #91822 incorrect datatype in RBR event when column is NULL and not explicit in query
Description:
Decoded binary log events when using RBR do not have the proper datatype listed when columns are omitted from the query. TBH, I don't know if this is actually a *bug*; it seems to only impact external tools / libraries which attempt to parse binary log events and are expecting columns to be of a certain type, and it is definitely not expected behavior. See "how to repeat" for more information.
How to repeat:
- configure server for row-based binlogs.
- create innodb table with nullable column
- insert a row that does not list the second column:
create table test.foo ( i int not null primary key, data VARCHAR(32) );
insert into test.foo (i) values (1);
run mysqlbinlog -vvv on the binary log. the event will be decoded into something that looks sort of like this:
### INSERT INTO `test`.`foo`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=NULL /* INT meta=32 nullable=1 is_null=1 */
Column #2 is not an INT. It is a VARCHAR. It appears that whenever this situation occurs, the missing column is given the datatype of the column immediately preceding it. I tried the same thing with two columns:
create table test.bar (i int not null primary key, data varchar(32), otherdata varchar(255));
insert into test.bar (i) values (1);
mysqlbinlog -vvv and I get this:
### INSERT INTO `test`.`bar`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=NULL /* INT meta=32 nullable=1 is_null=1 */
### @3=NULL /* INT meta=255 nullable=1 is_null=1 */
Then....
insert into test.bar(i, data) values (2, 'arf')
and that turns into a decoded row event like this:
### INSERT INTO `test`.`bar`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='arf' /* VARSTRING(32) meta=32 nullable=1 is_null=0 */
### @3=NULL /* VARSTRING(32) meta=255 nullable=1 is_null=1 */
Suggested fix:
The types in the decoded binlog event should match the ones used in the schema.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.