Bug #91822 incorrect datatype in RBR event when column is NULL and not explicit in query

Submitted: 28 Jul 2018 3:56 Modified: 30 Jul 2018 10:46
Reporter: Ernie Souhrada Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.6, 5.6.41, 5.5.60 OS:Debian
Assigned to: CPU Architecture:Any

[28 Jul 2018 3:56] Ernie Souhrada
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.
[30 Jul 2018 10:46] MySQL Verification Team
Hello Ernie Souhrada,

Thank you for the report!

Thanks,
Umesh

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.