EmergencyEMERGENCY? Get 24/7 Help Now!

Why base64-output=DECODE-ROWS does not print row events in MySQL binary logs

 | July 30, 2015 |  Posted In: MySQL, MySQL 101

PREVIOUS POST
NEXT POST

Lately I saw many cases when users specified option --base64-output=DECODE-ROWS  to print out a statement representation of row events in MySQL binary logs just to get nothing. Reason for this is obvious: option --base64-output=DECODE-ROWS  does not convert row events into its string representation, this is job of option --verbose . But why users mix these two options so often? This blog post is result of my investigations.

There are already two great blog posts about printing row events on the Percona blog: “Debugging problems with row based replication” by Justin Swanhart and “Identifying useful info from MySQL row-based binary logs” by Alok Pathak.

Both authors run mysqlbinlog  with options  –base64-output=decode-rows -vv  and demonstrate how a combination of them can produce human-readable output of row events. However, one thing which is not clear yet is what the differences are between these options. I want to underline the differences in this post.

Let’s check the user manual first.

–base64-output=value

This option determines when events should be displayed encoded as base-64 strings using BINLOG statements. The option has these permissible values (not case sensitive):

    AUTO (“automatic”) or UNSPEC (“unspecified”) displays BINLOG statements automatically when necessary (that is, for format description events and row events). If no –base64-output option is given, the effect is the same as –base64-output=AUTO.
    Note

    Automatic BINLOG display is the only safe behavior if you intend to use the output of mysqlbinlog to re-execute binary log file contents. The other option values are intended only for debugging or testing purposes because they may produce output that does not include all events in executable form.

    NEVER causes BINLOG statements not to be displayed. mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG.

    DECODE-ROWS specifies to mysqlbinlog that you intend for row events to be decoded and displayed as commented SQL statements by also specifying the –verbose option. Like NEVER, DECODE-ROWS suppresses display of BINLOG statements, but unlike NEVER, it does not exit with an error if a row event is found.

For examples that show the effect of –base64-output and –verbose on row event output, see Section 4.6.8.2, “mysqlbinlog Row Event Display”.

Literally --base64-output=DECODE-ROWS  just suppresses BINLOG  statement and does not print anything.

To test its effect I run command

on an InnoDB table while binary log uses ROW format. As expected if I specify no option I will receive unreadable output:

The INSERT  is here:

But this string is not for humans.

What will happen if I add option --base64-output=DECODE-ROWS ?

Row event was just suppressed!

Lets now check option verbose:

–verbose, -v

Reconstruct row events and display them as commented SQL statements. If this option is given twice, the output includes comments to indicate column data types and some metadata.

For examples that show the effect of –base64-output and –verbose on row event output, see Section 4.6.8.2, “mysqlbinlog Row Event Display”.

Surprisingly --base64-output=DECODE-ROWS  is not needed!:

INSERT statement successfully restored as:

Why do the bloggers mentioned above suggest to use --base64-output=DECODE-ROWS ? Lets try to use both options:

In this case row event was suppressed and statement is printed. Also, the resulting file cannot be used to re-apply events, because statements are commented out. This is very useful when binary log is big and you just need to investigate what it contains, not re-apply events.

This is not main purpose of this post, but you can also find information about columns metadata if specify option --verbose  twice:

Note, this is, again, job of --verbose , not --base64-output=DECODE-ROWS

To conclude:

If you want to see statement representation of row events use option --verbose (-v)
If you want to see metadata of columns specify --verbose  twice: --verbose --verbose  or -vv
If you want to suppress output of row events specify option --base64-output=DECODE-ROWS

PREVIOUS POST
NEXT POST
Sveta Smirnova

Sveta joined Percona in 2015. Her main professional interests are problem solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as a Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of the book "MySQL Troubleshooting" and JSON UDF functions for MySQL.

3 Comments

  • Hello Sveta, Thanks for the above information but the confusion of re-applying the binlog events incase of failure is not clear. What options should we use to get the statements in uncommented format so that it can be re-applied.

  • There is no such an option. If you need to re-apply events you should not use –base64-output=DECODE-ROWS: binary events will be applied. SQL events exist for information purpose only. For example, when you are trying to find which statement caused replication failure.

Leave a Reply