Backup locks do not guarantee consistent SHOW SLAVE STATUS with binary log disabled
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
High
|
Alexey Kopytov | ||
5.1 |
Invalid
|
Undecided
|
Unassigned | ||
5.5 |
Invalid
|
Undecided
|
Unassigned | ||
5.6 |
Fix Released
|
High
|
Alexey Kopytov |
Bug Description
An active binlog lock via LOCK BINLOG FOR BACKUP does not prevent Exec_Master_Log_Pos from incrementing. This seems to be contrary to the documented behavior here: http://
The documentation seems to indicate that LOCK BINLOG FOR BACKUP will block changes that will increment the binary log position or Exec_Master_
Expected behavior:
LOCK BINLOG FOR BACKUP will provide a consistent Relay_Master_
Observed behavior:
DML to InnoDB tables increments Exec_Master_Log_Pos in spite of LOCK TABLES FOR BACKUP / LOCK BINLOG FOR BACKUP being held.
Tested on: 5.6.19-67.0-log (Percona Server (GPL), Release 67.0, Revision 618)
In this case, the situation was an LVM snapshot backup where backup locks were being explored as an alternative to FTWRL when using Percona-Server.
The basic pattern was:
mysql[master]> CREATE DATABASE IF NOT EXISTS `test`;
Query OK, 1 row affected (0.00 sec)
mysql[master]> CREATE TABLE IF NOT EXISTS `test`.`kv` (id int primary key auto_increment, data varchar(32)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql[slave]> LOCK TABLES FOR BACKUP;
Query OK, 0 rows affected (0.00 sec)
mysql[slave]> LOCK BINLOG FOR BACKUP;
Query OK, 0 rows affected (0.00 sec)
mysql[slave]> \P grep Exec_Master_Log_Pos PAGER set to 'grep Exec_Master_
mysql[slave]> SHOW SLAVE STATUS\G
1 row in set (0.00 sec)
mysql[master]> INSERT INTO `test`.`kv` (data) SELECT MD5('foo');
mysql[slave]> SHOW SLAVE STATUS\G
1 row in set (0.00 sec)
However, if this was MyISAM the backup or binlog lock seems to protect this position:
mysql[master]> ALTER TABLE `test`.`kv` ENGINE=MyISAM;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql[slave]> SHOW PROCESSLIST;
+----+-
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-
| 1 | system user | | NULL | Connect | 132 | Waiting for master to send event | NULL | 0 | 0 |
| 2 | system user | | | Connect | 7 | Waiting for backup lock | ALTER TABLE `test`.`kv` ENGINE = MyISAM | 0 | 0 |
| 3 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0 | 0 |
+----+-
3 rows in set (0.00 sec)
mysql[slave]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql[sandbox]> SHOW PROCESSLIST;
+----+-
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+-
| 1 | system user | | NULL | Connect | 328 | Waiting for master to send event | NULL | 0 | 0 |
| 2 | system user | | NULL | Connect | 203 | Waiting for binlog lock | NULL | 0 | 0 |
| 3 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST | 0 | 0 |
+----+-
3 rows in set (0.00 sec)
There's a more serious problem here if log-slave-updates is enabled on the slave. In that case replication does block on a binlog lock (even for an insert to an InnoDB table), but then running SHOW SLAVE STATUS will hang in the original connection that holds a binlog lock. This is essentially the symptoms of http://
Related branches
- Laurynas Biveinis (community): Approve
-
Diff: 1000 lines (+487/-45)16 files modifiedmysql-test/suite/rpl/r/rpl_backup_locks.result (+7/-0)
mysql-test/suite/rpl/r/rpl_backup_locks_mts.result (+30/-0)
mysql-test/suite/rpl/r/rpl_bug1358836.result (+18/-0)
mysql-test/suite/rpl/t/rpl_backup_locks.test (+32/-0)
mysql-test/suite/rpl/t/rpl_backup_locks_mts-slave.opt (+1/-0)
mysql-test/suite/rpl/t/rpl_backup_locks_mts.test (+84/-0)
mysql-test/suite/rpl/t/rpl_bug1358836-slave.opt (+1/-0)
mysql-test/suite/rpl/t/rpl_bug1358836.test (+49/-0)
sql/binlog.cc (+13/-3)
sql/log_event.cc (+21/-0)
sql/rpl_info_factory.cc (+24/-0)
sql/rpl_rli.cc (+55/-21)
sql/rpl_rli.h (+17/-8)
sql/rpl_rli_pdb.cc (+56/-13)
sql/rpl_slave.cc (+77/-0)
sql/sql_class.h (+2/-0)
Changed in percona-server: | |
status: | Incomplete → New |
summary: |
- Backup locks do not guarantee consistent SHOW SLAVE STATUS + Backup locks do not guarantee consistent SHOW SLAVE STATUS with binary + log disabled |
We have a regression test case covering this very scenario, i.e. the one that checks that a DML against an InnoDB table on the master is blocked on an active BINLOG lock when being replayed on the slave (mysql- test/suite/ rpl/t/rpl_ backup_ locks.test) . The test case passes with all values of binlog_format.
Which means that either something is different between your environment and the one that the test case sets up, or there's a mistake in your tests.
Can you show the full output of SHOW SLAVE STATUS and the contents of the relay log on the slave corresponding to "before" and "after" values of Exec_Master_ Log_Pos?