[MDEV-7506] Bellow Blackhole engine, update with inner join you'll end up with inconsistent data in your database Created: 2015-01-27  Updated: 2015-02-04  Resolved: 2015-02-04

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Tsao Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

master database use MySQL 5.1 , slave level 1 database use Mariadb 10.0.15 , slave level 2 database use MySQL5.1 or MySQL5.6 or Mariadb.
sample: MySQL 5.1→Mariadb 10.0.15(level 1)→Mariadb 10.0.15(level 2)
level 1 table use blackhole engine


 Description   

Environment:
master database use MySQL 5.1 ,
slave level 1 database use Mariadb 10.0.15 ,
slave level 2 database use MySQL5.1 or MySQL5.6 or Mariadb.

sample: MySQL 5.1→Mariadb 10.0.15(level 1)→Mariadb 10.0.15(level 2)
level 1 table use blackhole engine

1, master database:

mysql> DROP TABLE IF EXISTS z1,z2;
 
Query OK, 0 rows affected (0.21 sec)
 
 
 
mysql> CREATE TABLE z1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(100) NOT NULL DEFAULT '');
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> INSERT INTO z1 VALUES(1,'aaa'),(2,'bbb'),(3,'ccc');
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
mysql> CREATE TABLE z2 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(100) NOT NULL DEFAULT '');
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> INSERT INTO z2 VALUES(1,'ddd'),(2,'eee'),(3,'fff');
 
Query OK, 3 rows affected (0.00 sec)
 
Records: 3  Duplicates: 0  Warnings:

2, slave level 1 database:

mysql> set sql_log_bin=0;                            
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> ALTER TABLE z1 ENGINE=BLACKHOLE;              
Query OK, 3 rows affected (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
mysql> ALTER TABLE z2 ENGINE=BLACKHOLE;                            
Query OK, 3 rows affected (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 0

3, master database:

mysql> UPDATE z1,z2 SET z1.NAME='d9' WHERE z1.id=z2.id AND z1.id=3;
 
Query OK, 1 row affected (0.01 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0
 
 
 
mysql> UPDATE z1,z2 SET z2.NAME='d9' WHERE z1.id=z2.id AND z1.id=3;
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1  Changed: 1  Warnings: 0

4, slave level 2 database:

mysql> select * from z1,z2 where z1.id=z2.id and z1.id=3;
 
+----+------+----+------+
 
| id | NAME | id | NAME |
 
+----+------+----+------+
 
|  3 | ccc  |  3 | fff  |
 
+----+------+----+------+
 
1 row in set (0.00 sec)

– You can find the data did not update

5, master database:

mysql> UPDATE z1,z2 SET z1.NAME='d10',z2.NAME='d10' WHERE z1.id=z2.id AND z1.id=3;   
Query OK, 2 rows affected (0.00 sec)
 
Rows matched: 2  Changed: 2  Warnings: 0

6, slave level 2 database:

mysql> select * from z1,z2 where z1.id=z2.id and z1.id=3;
 
+----+------+----+------+
 
| id | NAME | id | NAME |
 
+----+------+----+------+
 
|  3 | d10  |  3 | d10  |
 
+----+------+----+------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2015-01-27 ]

Could you please provide

  • binary logs from master and slave level 1 which cover this test case;
  • show slave status output from slave level 1 and slave level 2;
  • error logs from slave level 1 and slave level 2;
  • show variables from all servers
    ?

Thanks.

Comment by Tsao [ 2015-01-28 ]

binary logs from master:

# at 106
 
#150128 10:34:23 server id 254  end_log_pos 194  Query   thread_id=36687328      exec_time=0     error_code=0
 
use db/*!*/;
 
SET TIMESTAMP=1422412463/*!*/;
 
SET @@session.pseudo_thread_id=36687328/*!*/;
 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 
SET @@session.sql_mode=0/*!*/;
 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 
/*!\C utf8 *//*!*/;
 
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=28/*!*/;
 
SET @@session.lc_time_names=0/*!*/;
 
SET @@session.collation_database=DEFAULT/*!*/;
 
DROP TABLE IF EXISTS z1,z2
 
/*!*/;
 
# at 194
 
#150128 10:34:24 server id 254  end_log_pos 354  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412464/*!*/;
 
CREATE TABLE z1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(100) NOT NULL DEFAULT '')
 
/*!*/;
 
# at 354
 
#150128 10:34:24 server id 254  end_log_pos 514  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412464/*!*/;
 
CREATE TABLE z2 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(100) NOT NULL DEFAULT '')
 
/*!*/;
 
# at 514
 
#150128 10:34:25 server id 254  end_log_pos 581  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412465/*!*/;
 
BEGIN
 
/*!*/;
 
# at 581
 
#150128 10:34:25 server id 254  end_log_pos 693  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412465/*!*/;
 
INSERT INTO z1 VALUES(1,'aaa'),(2,'bbb'),(3,'ccc')
 
/*!*/;
 
# at 693
 
#150128 10:34:25 server id 254  end_log_pos 720  Xid = 599827107
 
COMMIT/*!*/;
 
# at 720
 
#150128 10:34:25 server id 254  end_log_pos 787  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412465/*!*/;
 
BEGIN
 
/*!*/;
 
# at 787
 
#150128 10:34:25 server id 254  end_log_pos 899  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412465/*!*/;
 
INSERT INTO z2 VALUES(1,'ddd'),(2,'eee'),(3,'fff')
 
/*!*/;
 
# at 899
 
#150128 10:34:25 server id 254  end_log_pos 926  Xid = 599827110
 
COMMIT/*!*/;
 
# at 926
 
#150128 10:36:11 server id 254  end_log_pos 993  Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412571/*!*/;
 
BEGIN
 
/*!*/;
 
# at 993
 
#150128 10:36:11 server id 254  end_log_pos 1123         Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412571/*!*/;
 
UPDATE z1,z2 SET z1.NAME='d9' WHERE z1.id=z2.id AND z1.id=3
 
/*!*/;
 
# at 1123
 
#150128 10:36:11 server id 254  end_log_pos 1150         Xid = 599840132
 
COMMIT/*!*/;
 
# at 1150
 
#150128 10:36:12 server id 254  end_log_pos 1217         Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412572/*!*/;
 
BEGIN
 
/*!*/;
 
# at 1217
 
#150128 10:36:12 server id 254  end_log_pos 1347         Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412572/*!*/;
 
UPDATE z1,z2 SET z2.NAME='d9' WHERE z1.id=z2.id AND z1.id=3
 
/*!*/;
 
# at 1347
 
#150128 10:36:12 server id 254  end_log_pos 1374         Xid = 599840271
 
COMMIT/*!*/;
 
# at 1374
 
#150128 10:36:20 server id 254  end_log_pos 1441         Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412580/*!*/;
 
BEGIN
 
/*!*/;
 
# at 1441
 
#150128 10:36:20 server id 254  end_log_pos 1586         Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412580/*!*/;
 
UPDATE z1,z2 SET z1.NAME='d10',z2.NAME='d10' WHERE z1.id=z2.id AND z1.id=3
 
/*!*/;
 
# at 1586
 
#150128 10:36:20 server id 254  end_log_pos 1613         Xid = 599841077
 
COMMIT/*!*/;
 
DELIMITER ;

binary logs from slave level 1:

# at 592248
 
#150128 10:34:23 server id 254  end_log_pos 592286       GTID 0-33-34767927
 
/*!100001 SET @@session.server_id=33*//*!*/;
 
/*!100001 SET @@session.gtid_seq_no=34767927*//*!*/;
 
# at 592286
 
#150128 10:34:23 server id 254  end_log_pos 592404       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412463/*!*/;
 
SET @@session.sql_mode=0/*!*/;
 
DROP TABLE IF EXISTS `z1`,`z2` /* generated by server */
 
/*!*/;
 
# at 592404
 
#150128 10:34:24 server id 254  end_log_pos 592442       GTID 0-33-34767928
 
/*!100001 SET @@session.gtid_seq_no=34767928*//*!*/;
 
# at 592442
 
#150128 10:34:24 server id 254  end_log_pos 592602       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412464/*!*/;
 
CREATE TABLE z1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(100) NOT NULL DEFAULT '')
 
/*!*/;
 
# at 592602
 
#150128 10:34:24 server id 254  end_log_pos 592640       GTID 0-33-34767929
 
/*!100001 SET @@session.gtid_seq_no=34767929*//*!*/;
 
# at 592640
 
#150128 10:34:24 server id 254  end_log_pos 592800       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412464/*!*/;
 
CREATE TABLE z2 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(100) NOT NULL DEFAULT '')
 
/*!*/;
 
# at 592800
 
#150128 10:34:25 server id 254  end_log_pos 592838       GTID 0-33-34767930
 
/*!100001 SET @@session.gtid_seq_no=34767930*//*!*/;
 
BEGIN
 
/*!*/;
 
# at 592838
 
#150128 10:34:25 server id 254  end_log_pos 592950       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412465/*!*/;
 
INSERT INTO z1 VALUES(1,'aaa'),(2,'bbb'),(3,'ccc')
 
/*!*/;
 
# at 592950
 
#150128 10:34:25 server id 254  end_log_pos 592977       Xid = 87567251
 
COMMIT/*!*/;
 
# at 592977
 
#150128 10:34:25 server id 254  end_log_pos 593015       GTID 0-33-34767931
 
/*!100001 SET @@session.gtid_seq_no=34767931*//*!*/;
 
BEGIN
 
/*!*/;
 
# at 593015
 
#150128 10:34:25 server id 254  end_log_pos 593127       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412465/*!*/;
 
INSERT INTO z2 VALUES(1,'ddd'),(2,'eee'),(3,'fff')
 
/*!*/;
 
# at 593127
 
#150128 10:34:25 server id 254  end_log_pos 593154       Xid = 87567253
 
COMMIT/*!*/;
 
# at 593154
 
#150128 10:36:20 server id 254  end_log_pos 593192       GTID 0-33-34767932
 
/*!100001 SET @@session.gtid_seq_no=34767932*//*!*/;
 
BEGIN
 
/*!*/;
 
# at 593192
 
#150128 10:36:20 server id 254  end_log_pos 593337       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412580/*!*/;
 
UPDATE z1,z2 SET z1.NAME='d10',z2.NAME='d10' WHERE z1.id=z2.id AND z1.id=3
 
/*!*/;
 
# at 593337
 
#150128 10:36:20 server id 254  end_log_pos 593414       Query   thread_id=36687328      exec_time=0     error_code=0
 
SET TIMESTAMP=1422412580/*!*/;
 
COMMIT
 
/*!*/;
 
DELIMITER ;

– You can find the bellow sql did't in the slave level 1:

UPDATE z1,z2 SET z1.NAME='d9' WHERE z1.id=z2.id AND z1.id=3;
 
UPDATE z1,z2 SET z2.NAME='d9' WHERE z1.id=z2.id AND z1.id=3

"show slave status" is running status is ok;
no error log on the slave level 1 and slave level 2;

PS: master,slave level 1 and slave level 2 the binlog_format is all MIXED

tx_isolation                                           | REPEATABLE-READ
 
sql_mode                                               | NO_AUTO_CREATE_USER

slave level 1(mariadb):

gtid_binlog_pos                                        | 0-32-34780397                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | 
gtid_binlog_state                                      | 0-333307-22179369,0-12143307-34780395,0-25123307-999823,0-33-34777025,0-32-34780397                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 
| gtid_current_pos                                       |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | 
| gtid_domain_id                                         | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 
| gtid_ignore_duplicates                                 | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                | 
gtid_seq_no                                            | 0                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  | 
gtid_slave_pos                                         |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | 
gtid_strict_mode                                       | OFF

Comment by Elena Stepanova [ 2015-02-03 ]

Okay, thanks, this will do.
Here is what happens here.

On both servers (here and further I will talk about master and slave1, slave2 is unimportant here) you have mixed binlog format.
Master is old – 5.1 has far less logic for the mixed mode (for automatic switching to row-based format). When you execute your UPDATE on master, it's written in the binlog as is, in the statement format.
Then it reaches slave1. 10.0 (actually, 5.5 even) has a lot of logic for 'statements unsafe for SBR'. This is one of such statements. The reasoning is this (you can see it if you attempt to execute the statement in SBR mode):
"Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave."

But since you're in MBR mode, no warnings are issued, instead the server switches to row-based binlog format.
But it's the Blackhole – nothing has been updated, no rows have been changed, so there's nothing to write in row-based format (blackhole as a replication gateway only works for SBR).

The true multi-table update (the one with 'd10') is not considered unsafe for SBR; so, it stays in statement format and is written to the binary log. Whether it's a wise exception or a miss, can probably be investigated, but as I understand it's not a complaint in your case.

So, if you want this setup to work, you should probably switch both servers to SBR. Switching only slave1 is dangerous, because if at some point the master sends any row event, the replication will break, as the slave won't be able to log it.

Do you find this explanation satisfactory, or do you have any other questions/concerns regarding the problem?

Comment by Tsao [ 2015-02-04 ]

Thank you.

if I want keep this setup, master use MySQL5.1, slave level 1 use MariaDB(blackhole engine), slave level 2 use MySQL5.1.
When I execute the bellow command on the slave level 1, the data on the mater,slave 1 and slave 2 is the same.
on the slave level 1, remove the auto_increment from the id field:
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE z1 CHANGE id id INT NOT NULL ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE z2 CHANGE id id INT NOT NULL ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

then update sql execute on the master, it can sync to the slave level 2.

Can I use like this?

Comment by Elena Stepanova [ 2015-02-04 ]

If you always insert explicit values for the PK, it should work all right.
If you ever actually use auto-increment, there might be a problem I suppose. Master will write INSERT_ID into the binary log, so that slaves know where to start auto-incrementing from.
1st slave won't care, it doesn't insert anything anyway. But it also won't write this INSERT_ID, because it doesn't have an auto-increment on the table, so the 2nd slave will lose this information. In some cases, it can come up with different IDs.

On a separate note, I would advise against replicating from a newer server to an older server (as you are planning to replicate from 10.0 to 5.1) if you can avoid it. It is supported on a best effort basis, but generally is not guaranteed to work.

Generated at Thu Feb 08 07:20:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.