MySQL Bugs: #87728: replication inconsistencies on update through a view with 3 tables

Bug #87728	replication inconsistencies on update through a view with 3 tables
Submitted:	11 Sep 2017 16:58	Modified:	18 Sep 2017 9:22
Reporter:	kais ahmed	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	MySQL 5.6.27	OS:	Any (aws RDS)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	mixed, replication, VIEW

Description:
If you have a view with 3 tables and update a row through the view, the row is updated on the master and the statement appear in the binlog but the slave are not updated.

Same trouble with a view of 2 tables but it's work with one table in the view.

How to repeat:
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+

CREATE TABLE IF NOT EXISTS test_A (
 id int(10) unsigned NOT NULL,
 a char(1) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS test_B (
 id int(10) unsigned NOT NULL,
 b char(1) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS test_C (
  id int(10) unsigned NOT NULL,
  c char(1) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;

CREATE ALGORITHM=UNDEFINED DEFINER=`kais`@`%` SQL SECURITY DEFINER VIEW test_View AS select A.id as id, A.a as a , B.b as b, C.c as c  from (( test_A A  join test_B B  on((A.id = B.id))) join test_C C on(( B.id = C.id)));

insert into test_A (id, a) values (1,'P');
insert into test_B (id, b) values (1,'Q');
insert into test_C (id, c) values (1,'R');

mysql> select * from test_View;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | P | Q | R |
+----+---+---+---+

update test_View set a='D' where id=1; //the row is updated on master, the statement appear in binlog but it's not replicated on slave

Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

master [localhost] {msandbox} ((none)) > use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > CREATE TABLE IF NOT EXISTS test_A (
    ->  id int(10) unsigned NOT NULL,
    ->  a char(1) NOT NULL,
    ->  PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > CREATE TABLE IF NOT EXISTS test_B (
    ->  id int(10) unsigned NOT NULL,
    ->  b char(1) NOT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.10 sec)

master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > CREATE TABLE IF NOT EXISTS test_C (
    ->   id int(10) unsigned NOT NULL,
    ->   c char(1) NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;
Query OK, 0 rows affected (0.07 sec)

master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > insert into test_A (id, a) values (1,'P');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > insert into test_B (id, b) values (1,'Q');
Query OK, 1 row affected (0.02 sec)

master [localhost] {msandbox} (test) > insert into test_C (id, c) values (1,'R');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW test_View AS select A.id as id, A.a as a , B.b as b, C.c as c  from (( test_A A  join test_B B  on((A.id = B.id))) join test_C C on(( B.id = C.id)));                   Query OK, 0 rows affected (0.01 sec)

master [localhost] {msandbox} (test) > select * from test_View;                                                                   +----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | P | Q | R |
+----+---+---+---+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > update test_View set a='D' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master [localhost] {msandbox} (test) > select * from test_View;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | D | Q | R |
+----+---+---+---+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > \q
Bye
[arhimed@localhost rsandbox_mysql-5_7_19]$ node2/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave2 [localhost] {msandbox} ((none)) > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
slave2 [localhost] {msandbox} (test) > select * from test_View;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | D | Q | R |
+----+---+---+---+
1 row in set (0.01 sec)

slave2 [localhost] {msandbox} (test) > \q
Bye
[arhimed@localhost rsandbox_mysql-5_7_19]$ ./use_all "select * from test.test_View;"
# master
id      a       b       c
1       D       Q       R
# server: 1:
id      a       b       c
1       D       Q       R
# server: 2:
id      a       b       c
1       D       Q       R
[arhimed@localhost rsandbox_mysql-5_7_19]$

[arhimed@localhost rsandbox_mysql-5_6_37]$ ./m
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master [localhost] {msandbox} ((none)) > use test;
Database changed
master [localhost] {msandbox} (test) > show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > CREATE TABLE IF NOT EXISTS test_A (
    ->  id int(10) unsigned NOT NULL,
    ->  a char(1) NOT NULL,
    ->  PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > CREATE TABLE IF NOT EXISTS test_B (
    ->  id int(10) unsigned NOT NULL,
    ->  b char(1) NOT NULL,
    -> PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.10 sec)

master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > CREATE TABLE IF NOT EXISTS test_C (
    ->   id int(10) unsigned NOT NULL,
    ->   c char(1) NOT NULL,
    ->   PRIMARY KEY (id)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8  ;
Query OK, 0 rows affected (0.06 sec)

master [localhost] {msandbox} (test) > CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW test_View AS select A.id as id, A.a as a , B.b as b, C.c as c  from (( test_A A  join test_B B  on((A.id = B.id))) join test_C C on(( B.id = C.id)));                   Query OK, 0 rows affected (0.03 sec)

master [localhost] {msandbox} (test) > insert into test_A (id, a) values (1,'P');
Query OK, 1 row affected (0.00 sec)

master [localhost] {msandbox} (test) > insert into test_B (id, b) values (1,'Q');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > insert into test_C (id, c) values (1,'R');
Query OK, 1 row affected (0.01 sec)

master [localhost] {msandbox} (test) > select * from test_View;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | P | Q | R |
+----+---+---+---+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > update test_View set a='D' where id=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

master [localhost] {msandbox} (test) > select * from test_View;
+----+---+---+---+
| id | a | b | c |
+----+---+---+---+
|  1 | D | Q | R |
+----+---+---+---+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > \q
Bye
[arhimed@localhost rsandbox_mysql-5_6_37]$ ./use_all "select * from test.test_View;"
# master
id      a       b       c
1       D       Q       R
# server: 1:
id      a       b       c
1       D       Q       R
# server: 2:
id      a       b       c
1       D       Q       R
[arhimed@localhost rsandbox_mysql-5_6_37]$

Cannot reproduce this with latest 5.6 nor 5.7 MySQL release.

all best
Bogdan

Thanks a lot for your test, i have some updates concerning this ticket.

From AWS support (Vidyadhar Ch) : 

...
>> Further, I have enabled log_warnings variable on the replica instance and noticed below warnings recorded in the error log. These are related to the replication filters defined on read replica instance. When any event ignored due to replication filter then "throttle" count will increase and this message will be displayed every one minute. I have considered to execute the update command 10 times with in a minute and noticed that "throttle" count is 10 in the error log warning.

[Warning] Error log throttle: 11 time(s) Error_code: 1237 "Slave SQL thread ignored the query because of replicate-*-table rules" got suppressed.
[Warning] Slave SQL: Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237

>> I have performed some test cases by creating MySQL slave running on EC2 instance and noticed that this behavior when replication filters defined. When I remove the replication filters then the update getting processes as expected.

...

I have installed MySQL 5.6.35 and MySQL 5.6.37 versions on EC2 instances and configured these instances as replicas to RDS MySQL 5.6.35 and tested the behavior. I noticed that when I specify the replication filter rule in the my.cnf then the view updates are getting ignored. When there are no replication filters then the replication is working as expected and update command executed on the replica instance.

replicate-ignore-table='mysql.rds_heartbeat2'

Based on above, this is a bug in MySQL 5.6.x associated to the replication filters. To check further, I have tried to reproduce the issue on RDS MySQL 5.7.11 version and unable to do the same. Looks like this has been fixed in 5.7 version.

....

By the way, there is a separate bug related to the warnings. Please look into below link related to the same.
https://bugs.mysql.com/bug.php?id=73292