Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 10.0.15
-
Fix Version/s: N/A
-
Component/s: Replication
-
Labels:None
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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
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?
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?
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.
Could you please provide
?
Thanks.