mysqlbinlog not showing 'pseudo-SQL' comments for BINLOG statements

Question

We are running Percona mysql server 5.7.36-39.1 and corresponding version of mysqlbinlog.

I'm running mysqlbinlog against a binlog file and position (taken from the output of show master status;) with --verbose and/or --base64-output=DECODE-ROWS options but im not seeing the expected pseudo-SQL output based on https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog-row-events.html

(note i have censored the actual BINLOG output)

# mysqlbinlog --base64-output=DECODE-ROWS --verbose --start-position 56871313 --stop-position 56871313 <path_to_bin_log>
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
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*/;

# mysqlbinlog --verbose --start-position 56871313 --stop-position 56871313 /pdisk1/mysql/replication/master-bin.019473
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220211 16:23:41 server id 176422933 end_log_pos 123 CRC32 0x8ffe454f Start: binlog v 4, server v 5.7.36-39-log created 220211 16:23:41
BINLOG '
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXX
'/*!*/;
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*/;

However i do see pseudo-SQL for other BINLOG statements in the same binlog file.

I'd be grateful if someone could point me in the right direction to decoding this BINLOG statement!

Thanks!

Answer 1

This is very likely because the binary logging format is set to MIXED. In this case, some statements could get logged in the statement-based and others in the row-based format, and only the latter allows outputting pseudo-SQL statements.