include/master-slave.inc
[connection master]
CREATE TABLE t1 (i INT, a INT, b TEXT, PRIMARY KEY(i, a)) ENGINE=InnoDB;
CREATE TABLE t2 (i INT, a INT, b TEXT, PRIMARY KEY(i, a)) ENGINE=MyISAM;
SET @b= REPEAT('$', 0);
BEGIN;
INSERT INTO t1 VALUES (0, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (0, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (0, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (0, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (0, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (0, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (0, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (0, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=0 ORDER BY a;
a	length(b)
1	0
2	0
6	0
7	0
8	0
BEGIN;
INSERT INTO t1 VALUES (0, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (0, 11, @b);
INSERT INTO t2 VALUES (0, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=0 AND a>=10 ORDER BY a;
a	length(b)
10	0
SELECT a, length(b) FROM t2 WHERE i=0 ORDER BY a;
a	length(b)
12	0
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=0;
UPDATE t1 SET b='x' WHERE i=0;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (0, 101, @b), (0, 102, @b), (0, 103, @b), (0, 104, @b), (0, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=0 AND a > 100;
ERROR 23000: Duplicate entry '0-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=0 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=0 AND a >= 100 ORDER BY a;
a	length(b)
111	0
112	0
113	0
114	0
115	0
SET @b= REPEAT('$', 10);
BEGIN;
INSERT INTO t1 VALUES (1, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (1, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (1, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (1, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (1, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (1, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (1, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (1, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=1 ORDER BY a;
a	length(b)
1	10
2	10
6	10
7	10
8	10
BEGIN;
INSERT INTO t1 VALUES (1, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (1, 11, @b);
INSERT INTO t2 VALUES (1, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=1 AND a>=10 ORDER BY a;
a	length(b)
10	10
SELECT a, length(b) FROM t2 WHERE i=1 ORDER BY a;
a	length(b)
12	10
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=1;
UPDATE t1 SET b='x' WHERE i=1;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (1, 101, @b), (1, 102, @b), (1, 103, @b), (1, 104, @b), (1, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=1 AND a > 100;
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=1 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=1 AND a >= 100 ORDER BY a;
a	length(b)
111	10
112	10
113	10
114	10
115	10
SET @b= REPEAT('$', 100);
BEGIN;
INSERT INTO t1 VALUES (2, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (2, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (2, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (2, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (2, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (2, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (2, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (2, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=2 ORDER BY a;
a	length(b)
1	100
2	100
6	100
7	100
8	100
BEGIN;
INSERT INTO t1 VALUES (2, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (2, 11, @b);
INSERT INTO t2 VALUES (2, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=2 AND a>=10 ORDER BY a;
a	length(b)
10	100
SELECT a, length(b) FROM t2 WHERE i=2 ORDER BY a;
a	length(b)
12	100
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=2;
UPDATE t1 SET b='x' WHERE i=2;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (2, 101, @b), (2, 102, @b), (2, 103, @b), (2, 104, @b), (2, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=2 AND a > 100;
ERROR 23000: Duplicate entry '2-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=2 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=2 AND a >= 100 ORDER BY a;
a	length(b)
111	100
112	100
113	100
114	100
115	100
SET @b= REPEAT('$', 642);
BEGIN;
INSERT INTO t1 VALUES (3, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (3, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (3, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (3, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (3, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (3, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (3, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (3, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=3 ORDER BY a;
a	length(b)
1	642
2	642
6	642
7	642
8	642
BEGIN;
INSERT INTO t1 VALUES (3, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (3, 11, @b);
INSERT INTO t2 VALUES (3, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=3 AND a>=10 ORDER BY a;
a	length(b)
10	642
SELECT a, length(b) FROM t2 WHERE i=3 ORDER BY a;
a	length(b)
12	642
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=3;
UPDATE t1 SET b='x' WHERE i=3;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (3, 101, @b), (3, 102, @b), (3, 103, @b), (3, 104, @b), (3, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=3 AND a > 100;
ERROR 23000: Duplicate entry '3-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=3 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=3 AND a >= 100 ORDER BY a;
a	length(b)
111	642
112	642
113	642
114	642
115	642
SET @b= REPEAT('$', 3930);
BEGIN;
INSERT INTO t1 VALUES (4, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (4, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (4, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (4, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (4, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (4, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (4, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (4, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=4 ORDER BY a;
a	length(b)
1	3930
2	3930
6	3930
7	3930
8	3930
BEGIN;
INSERT INTO t1 VALUES (4, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (4, 11, @b);
INSERT INTO t2 VALUES (4, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=4 AND a>=10 ORDER BY a;
a	length(b)
10	3930
SELECT a, length(b) FROM t2 WHERE i=4 ORDER BY a;
a	length(b)
12	3930
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=4;
UPDATE t1 SET b='x' WHERE i=4;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (4, 101, @b), (4, 102, @b), (4, 103, @b), (4, 104, @b), (4, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=4 AND a > 100;
ERROR 23000: Duplicate entry '4-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=4 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=4 AND a >= 100 ORDER BY a;
a	length(b)
111	3930
112	3930
113	3930
114	3930
115	3930
SET @b= REPEAT('$', 16000);
BEGIN;
INSERT INTO t1 VALUES (5, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (5, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (5, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (5, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (5, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (5, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (5, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (5, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=5 ORDER BY a;
a	length(b)
1	16000
2	16000
6	16000
7	16000
8	16000
BEGIN;
INSERT INTO t1 VALUES (5, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (5, 11, @b);
INSERT INTO t2 VALUES (5, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=5 AND a>=10 ORDER BY a;
a	length(b)
10	16000
SELECT a, length(b) FROM t2 WHERE i=5 ORDER BY a;
a	length(b)
12	16000
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=5;
UPDATE t1 SET b='x' WHERE i=5;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (5, 101, @b), (5, 102, @b), (5, 103, @b), (5, 104, @b), (5, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=5 AND a > 100;
ERROR 23000: Duplicate entry '5-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=5 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=5 AND a >= 100 ORDER BY a;
a	length(b)
111	16000
112	16000
113	16000
114	16000
115	16000
SET @b= REPEAT('$', 40000);
BEGIN;
INSERT INTO t1 VALUES (6, 1, @b);
SAVEPOINT s1;
INSERT INTO t1 VALUES (6, 2, @b);
SAVEPOINT s2;
INSERT INTO t1 VALUES (6, 3, @b);
SAVEPOINT s3;
INSERT INTO t1 VALUES (6, 4, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (6, 5, @b);
ROLLBACK TO s2;
INSERT INTO t1 VALUES (6, 6, @b);
SAVEPOINT s4;
INSERT INTO t1 VALUES (6, 7, @b);
SAVEPOINT s5;
ROLLBACK TO s5;
INSERT INTO t1 VALUES (6, 8, @b);
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=6 ORDER BY a;
a	length(b)
1	40000
2	40000
6	40000
7	40000
8	40000
BEGIN;
INSERT INTO t1 VALUES (6, 10, @b);
SAVEPOINT s10;
INSERT INTO t1 VALUES (6, 11, @b);
INSERT INTO t2 VALUES (6, 12, @b);
ROLLBACK TO s10;
Warnings:
Warning	1196	Some non-transactional changed tables couldn't be rolled back
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=6 AND a>=10 ORDER BY a;
a	length(b)
10	40000
SELECT a, length(b) FROM t2 WHERE i=6 ORDER BY a;
a	length(b)
12	40000
BEGIN;
UPDATE t1 SET a=a+1000 WHERE i=6;
UPDATE t1 SET b='x' WHERE i=6;
ROLLBACK;
BEGIN;
INSERT INTO t1
VALUES (6, 101, @b), (6, 102, @b), (6, 103, @b), (6, 104, @b), (6, 105, @b);
UPDATE t1 SET a=a-104 WHERE i=6 AND a > 100;
ERROR 23000: Duplicate entry '6-1' for key 'PRIMARY'
UPDATE t1 SET a=a+10 WHERE i=6 AND a > 100;
COMMIT;
SELECT a, length(b) FROM t1 WHERE i=6 AND a >= 100 ORDER BY a;
a	length(b)
111	40000
112	40000
113	40000
114	40000
115	40000
include/save_master_gtid.inc
connection slave;
include/sync_with_master_gtid.inc
*** Slave data checksums with master, all ok. ***
connection master;
DROP TABLE t1, t2;
include/rpl_end.inc