1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
include/master-slave.inc
[connection master]
CREATE TABLE t1 (id int, a int);
CREATE TABLE t2 (id int, b int);
CREATE TABLE t3 (id int, c int);
CREATE TABLE t4 (id int, d int);
CREATE TABLE t5 (id int, e int);
CREATE TABLE t6 (id int, f int);
CREATE TABLE t7 (id int, g int);
CREATE TABLE t8 (id int, h int);
CREATE TABLE t9 (id int, i int);
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t5 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t6 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t7 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t8 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t9 VALUES (1, 1), (2, 2), (3, 3);
[on slave]
SHOW TABLES LIKE 't%';
Tables_in_test (t%)
t1
t2
t3
[on master]
UPDATE t7 LEFT JOIN t4 ON (t4.id=t7.id) SET d=0, g=0 where t7.id=1;
UPDATE t7 LEFT JOIN (t4, t5, t6) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t6.id) SET d=0, e=0, f=0, g=0 where t7.id=1;
UPDATE t4 LEFT JOIN (t7, t8, t9) ON (t4.id=t7.id and t4.id=t8.id and t4.id=t9.id) SET d=0, g=0, h=0, i=0 where t4.id=1;
UPDATE t7 LEFT JOIN (t8, t9) ON (t7.id=t8.id and t7.id=t9.id) SET g=0, h=0, i=0 where t7.id=1;
UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET d=0 where t1.id=1;
UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET g=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET d=0, e=0, f=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t4, t5, t8) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t8.id) SET d=0, e=0, h=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t7, t8, t5) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t5.id) SET g=0, h=0, e=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t2, t3, t5) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t5.id) SET e=0 where t1.id=1;
UPDATE t4 LEFT JOIN t1 ON (t1.id=t4.id) SET a=0, d=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t7) ON (t4.id=t1.id and t7.id=t4.id) SET a = 0, d=0, g=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t2, t3) ON (t1.id=t4.id and t2.id=t4.id and t3.id=t4.id) SET a=0, b=0, c=0, d=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t2, t5) ON (t1.id=t4.id and t2.id=t4.id and t5.id=t4.id) SET a=0, b=0, e=0, d=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t6, t7) ON (t4.id=t1.id and t4.id=t6.id and t4.id=t7.id) SET a=0, d=0, f=0, g=0 where t4.id=1;
UPDATE t7 LEFT JOIN (t4, t1, t2) ON (t7.id=t4.id and t7.id=t1.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
UPDATE t7 LEFT JOIN (t8, t4, t1) ON (t7.id=t8.id and t7.id=t4.id and t7.id=t1.id) SET a=0, d=0, g=0, h=0 where t7.id=1;
call mtr.add_suppression("Slave SQL.*Error .Table .test.t[47]. doesn.t exist. on query.* Error_code: 1146");
UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1''
UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1''
UPDATE t1 LEFT JOIN (t2, t4, t7) ON (t1.id=t2.id and t1.id=t4.id and t1.id=t7.id) SET a=0, b=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t2, t4, t7) ON (t1.id=t2.id and t1.id=t4.id and t1.id=t7.id) SET a=0, b=0 where t1.id=1''
UPDATE t1 LEFT JOIN (t2, t3, t7) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t7.id) SET a=0, b=0, c=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t2, t3, t7) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t7.id) SET a=0, b=0, c=0 where t1.id=1''
UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1''
UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1''
UPDATE t1 LEFT JOIN (t4, t5, t7) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t7.id) SET a=0, g=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t5, t7) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t7.id) SET a=0, g=0 where t1.id=1''
UPDATE t1 LEFT JOIN (t4, t7, t8) ON (t1.id=t4.id and t1.id=t7.id and t1.id=t8.id) SET a=0, g=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t7, t8) ON (t1.id=t4.id and t1.id=t7.id and t1.id=t8.id) SET a=0, g=0 where t1.id=1''
UPDATE t1 LEFT JOIN (t7, t8, t9) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t9.id) SET a=0, g=0, h=0, i=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t7, t8, t9) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t9.id) SET a=0, g=0, h=0, i=0 where t1.id=1''
UPDATE t7 LEFT JOIN (t1, t2, t3) ON (t7.id=t1.id and t7.id=t2.id and t7.id=t3.id) SET g=0, a=0, b=0, c=0 where t7.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t1, t2, t3) ON (t7.id=t1.id and t7.id=t2.id and t7.id=t3.id) SET g=0, a=0, b=0, c=0 where t7.id=1''
UPDATE t7 LEFT JOIN (t4, t5, t3) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t3.id) SET g=0, c=0 where t7.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t4, t5, t3) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t3.id) SET g=0, c=0 where t7.id=1''
UPDATE t7 LEFT JOIN (t8, t9, t3) ON (t7.id=t8.id and t7.id=t9.id and t7.id=t3.id) SET g=0, h=0, i=0, c=0 where t7.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t8, t9, t3) ON (t7.id=t8.id and t7.id=t9.id and t7.id=t3.id) SET g=0, h=0, i=0, c=0 where t7.id=1''
UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1''
UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET a=0, d=0, e=0, f=0 where t1.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET a=0, d=0, e=0, f=0 where t1.id=1''
UPDATE t4 LEFT JOIN (t1, t5, t6) ON (t4.id=t1.id and t4.id=t5.id and t4.id=t6.id) SET a=0, e=0, f=0 where t4.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t4' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t4 LEFT JOIN (t1, t5, t6) ON (t4.id=t1.id and t4.id=t5.id and t4.id=t6.id) SET a=0, e=0, f=0 where t4.id=1''
UPDATE t7 LEFT JOIN (t1, t4, t2) ON (t7.id=t1.id and t7.id=t4.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
include/wait_for_slave_sql_error_and_skip.inc [errno=1146]
Last_SQL_Error = 'Error 'Table 'test.t7' doesn't exist' on query. Default database: 'test'. Query: 'UPDATE t7 LEFT JOIN (t1, t4, t2) ON (t7.id=t1.id and t7.id=t4.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1''
[on slave]
show tables like 't%';
Tables_in_test (t%)
t1
t2
t3
SELECT * FROM t1;
id a
1 1
2 2
3 3
SELECT * FROM t2;
id b
1 1
2 2
3 3
SELECT * FROM t3;
id c
1 1
2 2
3 3
[on master]
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
CREATE TABLE test.t5 (a INT AUTO_INCREMENT PRIMARY KEY, b DECIMAL(20,20), c INT);
CREATE TABLE test.t1 (a INT);
INSERT INTO test.t1 VALUES(1);
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
CREATE TABLE test.t_slave (a INT AUTO_INCREMENT PRIMARY KEY, b DECIMAL(20,20), c INT);
CREATE TRIGGER t1_update AFTER UPDATE ON test.t1 FOR EACH ROW
INSERT INTO test.t_slave VALUES(NULL, RAND(), @c);
SET INSERT_ID=2;
SET @c=2;
SET @@rand_seed1=10000000, @@rand_seed2=1000000;
INSERT INTO t5 VALUES (NULL, RAND(), @c);
SELECT b into @b FROM test.t5;
UPDATE test.t1 SET a=2;
SELECT a AS 'ONE' into @a FROM test.t_slave;
SELECT c AS 'NULL' into @c FROM test.t_slave;
SELECT b into @b FROM test.t_slave;
include/assert.inc [Random values from master and slave must be different]
drop table test.t5;
drop table test.t1;
drop table test.t_slave;
include/rpl_end.inc
|
