LOAD DATA INFILE is not replicated correctly if master uses relay-log-info-repos=TABLE

[Nickolay Ihalainen]

This bug is the same as upstream https://bugs.mysql.com/bug.php?id=78051
"LOAD DATA CONCURRENT LOCAL INFILE" is not replicated correctly
relay-log-info-repository=TABLE
max-relay-log-size=6990506

There is a master db1.
db2 is a slave for db1 and master for db3.

db2 uses --log-slave-updates --log-bin=mysqld-bin --binlog-format=MIXED --max-relay-log-size=6990506 --relay-log-info-repository=TABLE

If relay-log-info-repository is not equal TABLE, the query "LOAD DATA INFILE" is replicated correctly.

If relay-log-info-repository=TABLE, not all rows are replicated.

db3 MD5: 55d16e47098228fd6e7f2e1a50040c32 -
db3 MD5: 55d16e47098228fd6e7f2e1a50040c32 -
db1 MD5: 6321b74ab1727c9d50833e04b485032f -
db2 MD5: 6321b74ab1727c9d50833e04b485032f -
Count on slave: 1392236
Count on master: 2000000

Docker script to produce this output:
#!/bin/bash
docker run -d --name db1 -e MYSQL_ROOT_PASSWORD=Ohsh7Zae1d percona:5.6 --server-id=55 --log-bin=mysqld-bin --binlog-format=MIXED
docker run --link db1:db1 -d --name db2 -e MYSQL_ROOT_PASSWORD=Ohsh7Zae1d percona:5.6 --server-id=56 --log-slave-updates --log-bin=mysqld-bin --binlog-format=MIXED --max-relay-log-size=6990506 --relay-log-info-repository=TABLE
docker run --link db2 -d --name db3 -e MYSQL_ROOT_PASSWORD=Ohsh7Zae1d percona:5.6 --server-id=562

# docker exec -it db2 mysql -pOhsh7Zae1d
echo "waiting for mysql start"
while ! docker exec -it db3 mysqladmin -pOhsh7Zae1d ping &>/dev/null ; do sleep 1 ; done
while ! docker exec -it db2 mysqladmin -pOhsh7Zae1d ping &>/dev/null ; do sleep 1 ; done
while ! docker exec -it db1 mysqladmin -pOhsh7Zae1d ping &>/dev/null ; do sleep 1 ; done

sleep 20
echo "loading database data"
docker exec -it db1 mysql -pOhsh7Zae1d -e "SHOW MASTER STATUS\G"
docker exec -it db2 mysql -pOhsh7Zae1d -e "SHOW MASTER STATUS\G"

docker exec -it db1 mysql -pOhsh7Zae1d -e "create database test_db"

docker exec -it db1 mysql -pOhsh7Zae1d test_db -e "CREATE TABLE a ( a int) ENGINE=InnoDB"
docker exec -it db1 /bin/bash -c 'seq 2000000 > /root/test.csv'
docker exec -it db1 mysql -pOhsh7Zae1d test_db -e "LOAD DATA CONCURRENT LOCAL INFILE '/root/test.csv' INTO TABLE a FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'";

docker exec -it db2 mysql -pOhsh7Zae1d -e "change master to master_host='db1', master_user='root', master_password='Ohsh7Zae1d',master_log_file='mysqld-bin.000001', master_log_pos=830; start slave"
docker exec -it db3 mysql -pOhsh7Zae1d -e "change master to master_host='db2', master_user='root', master_password='Ohsh7Zae1d',master_log_file='mysqld-bin.000001', master_log_pos=830; start slave"

echo "Waiting before checksum"
sleep 150
echo -n "db3 MD5: "
docker exec -it db3 mysql -N -pOhsh7Zae1d -e "select * from test_db.a;" |grep -v 'Using a password on the command line interface can be insecure'|md5sum
sleep 30
echo -n "db3 MD5: "
docker exec -it db3 mysql -N -pOhsh7Zae1d -e "select * from test_db.a;" |grep -v 'Using a password on the command line interface can be insecure'|md5sum
echo -n "db1 MD5: "
docker exec -it db1 mysql -N -pOhsh7Zae1d -e "select * from test_db.a;" |grep -v 'Using a password on the command line interface can be insecure'|md5sum

echo -n "db2 MD5: "
docker exec -it db2 mysql -N -pOhsh7Zae1d -e "select * from test_db.a;" |grep -v 'Using a password on the command line interface can be insecure'|md5sum

echo -n "Count on slave: "
docker exec -it db3 mysql --batch -N -pOhsh7Zae1d -e "select count(*) from test_db.a;" |grep -v 'Using a password on the command line interface can be insecure'
echo -n "Count on master: "
docker exec -it db1 mysql --batch -N -pOhsh7Zae1d -e "select count(*) from test_db.a;" |grep -v 'Using a password on the command line interface can be insecure'

[Nickolay Ihalainen (ihanick)]

if db1 uses percona server 5.5 all three servers will have a different data:
db3 MD5: fdc60840e5171ce4929b261eef297c4f -
db1 MD5: 8eb5b59b82e619c153a0af2a22b22572 -
db2 MD5: 6321b74ab1727c9d50833e04b485032f -
Count on slave: 1358450
Count on master: 2000000

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-920