Bug #62268 mysqlbackup 3.6 will not work with ROW based binlogs and READ-COMMITTED

• Submitted: 26 Aug 2011 14:20
• Modified: 12 Sep 2011 10:03
• Reporter: Matthew Lord
• Status: Closed
• Category: MySQL Enterprise Backup
• Severity: S1 (Critical)
• Version: 3.6.0
• OS: Any
• CPU Architecture: Any
• Tags: binlog_format, READ-COMMITTED, row, tx_isolation

[26 Aug 2011 14:20] Matthew Lord

Description:
If you are using binlog_format=ROW and transaction-isolation=READ-COMMITTED, full backups will fail:

./bin/mysqlbackup -u root --port=3355 --backup-dir=/opt/mysql/backups --with-timestamp backup

...

110826 10:15:07 mysqlbackup: INFO: Copying /var/lib/mysql/mysql5.5/sbtest/sbtest.ibd (Antelope file format).
mysqlbackup: Progress in MB: 200
 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
110826 10:15:13 mysqlbackup: INFO: Starting to lock all the tables....
 mysqlbackup: ERROR: mysql query: 'INSERT INTO ibbackup_binlog_marker VALUES(1)': Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
 mysqlbackup: ERROR: Could not lock tables. Aborting.
 mysqlbackup: ERROR: Backup of non-innodb tables failed.!
Error: operation failed. global_error_count: 3  backup_config.error_count: 1

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 0
   End LSN                    : 0
-------------------------------------------------------------

How to repeat:
Start an instance of 5.5 with these options:
[mysqld]
log-bin
binlog_format=ROW
transaction-isolation=READ-COMMITTED 

Then attempt to do a full backup.

Suggested fix:
allow row based logging for this event or force statement mode properly

[12 Sep 2011 10:03] John Russell

Adding to doc changelog: 

Under MySQL 5.5.8 and higher, a full backup using the mysqlbackup 
 command could fail with the combination of settings binlog_format=ROW 
 and transaction-isolation=READ-COMMITTED. The error message was: 

mysqlbackup: ERROR: Could not lock tables. Aborting. 
mysqlbackup: ERROR: Backup of non-innodb tables failed.!