mysql emits BINLOG rows even though binlog_­format=­STATEMENT

All we need is an easy explanation of the problem, so here it is.

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default)

According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in our binlogs, as the first statement in each binlog.

Why is that?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Whenever mysql writes to binary logs, it has to encode all the operations.

There exists a base64 constant at the head of every binary log : 0xfe 0x62 0x69 0x6e. This is referred to the binlog magic number.

This value is registered in log_event.h in the mysql source code.

Other events are also encoded in log_event.h:

enum Log_event_type
{
  UNKNOWN_EVENT= 0, START_EVENT_V3= 1, QUERY_EVENT= 2, STOP_EVENT= 3,
  ROTATE_EVENT= 4, INTVAR_EVENT= 5, LOAD_EVENT= 6, SLAVE_EVENT= 7,
  CREATE_FILE_EVENT= 8, APPEND_BLOCK_EVENT= 9, EXEC_LOAD_EVENT= 10,
  DELETE_FILE_EVENT= 11, NEW_LOAD_EVENT= 12, RAND_EVENT= 13,
  USER_VAR_EVENT= 14, FORMAT_DESCRIPTION_EVENT= 15, XID_EVENT= 16,
  BEGIN_LOAD_QUERY_EVENT= 17, EXECUTE_LOAD_QUERY_EVENT= 18,
  TABLE_MAP_EVENT = 19, PRE_GA_WRITE_ROWS_EVENT = 20,
  PRE_GA_UPDATE_ROWS_EVENT = 21, PRE_GA_DELETE_ROWS_EVENT = 22,
  WRITE_ROWS_EVENT = 23, UPDATE_ROWS_EVENT = 24,
  DELETE_ROWS_EVENT = 25, INCIDENT_EVENT= 26,
  HEARTBEAT_LOG_EVENT= 27,
  ENUM_END_EVENT /* end marker */
};

Whenever mysqlbinlog dumps the contents of a binary logs, it has to see these event numbers, especially the binlog magic number. If this value is missing, that quickly reveals one of the following:

  • the file is not a binary log
  • the binary log is horrifically corrupt

If mysqlbinlog dumps the expression BINLOG, the file it is reading can be trusted to be a binary log because it encoutered the binlog magic number when parsing the file.

For more information on this: please see MySQL Internals on Binary Logs

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply