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