Debian Codegraph @

mysql-test/suite/perfschema/include/binlog_edge_common.inc

raw
1 # Test replication, when using special non-replicated tables.
2 #
3 # This test involve special statements that use non-replicated tables.
4 # Changes affecting non replicated tables are never written to the binlog.
5 # Executing these statements may or may not work, as the statements involved
6 # are edge cases.
7 # In MIXED or ROW binlog format, execution should succeed,
8 # and only partial data (the rows affecting replicated tables only)
9 # should be written to the binlog.
10 # In STATEMENT binlog format, execution should
11 # raise a warning (ER_BINLOG_UNSAFE_STATEMENT) if a non replicated table is
12 # only read from, or fail with an error (ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES)
13 # if a non replicated table is written to.
14 #
15 # SHOW ERRORS will print in the
16 # test .result file the exact outcome.
17
18 RESET MASTER;
19
20 --disable_warnings
21 drop database if exists my_replicated_db;
22 --enable_warnings
23
24 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
25
26 create database my_replicated_db;
27
28 create table my_replicated_db.my_tx_table(a bigint) engine = innodb;
29 create table my_replicated_db.my_non_tx_table(a bigint) engine = myisam;
30 create table my_replicated_db.my_bh_table(a bigint) engine = blackhole;
31
32 use test;
33 drop table if exists marker_start;
34
35 use my_replicated_db;
36
37 insert into my_tx_table(a)
38 values (1000), (2000), (3000);
39
40 insert into my_non_tx_table(a)
41 values (1000), (2000), (3000);
42
43 insert into my_bh_table(a)
44 values (1000), (2000), (3000);
45
46 use test;
47 drop table if exists marker_insert_select;
48
49 use my_replicated_db;
50
51 # Note:
52 # The queries used here do not make any sense (no semantic).
53 # What this test is interrested in, is check the behavior
54 # when replicating queries that mix both:
55 # - non replicated tables
56 # - replicated tables
57
58 insert into my_tx_table(a)
59 select thread_id from performance_schema.threads;
60
61 insert into my_non_tx_table(a)
62 select thread_id from performance_schema.threads;
63
64 insert into my_bh_table(a)
65 select thread_id from performance_schema.threads;
66
67 # For the information_schema,
68 # no error is enforced yet.
69 # Documenting the current behavior
70
71 insert into my_tx_table(a)
72 select id from information_schema.processlist;
73
74 insert into my_non_tx_table(a)
75 select id from information_schema.processlist;
76
77 insert into my_bh_table(a)
78 select id from information_schema.processlist;
79
80 insert into my_tx_table(a)
81 select thread_id from mysql.general_log;
82
83 insert into my_non_tx_table(a)
84 select thread_id from mysql.general_log;
85
86 insert into my_bh_table(a)
87 select thread_id from mysql.general_log;
88
89 insert into my_tx_table(a)
90 select thread_id from mysql.slow_log;
91
92 insert into my_non_tx_table(a)
93 select thread_id from mysql.slow_log;
94
95 insert into my_bh_table(a)
96 select thread_id from mysql.slow_log;
97
98 insert into my_tx_table(a)
99 select Relay_log_pos from mysql.slave_relay_log_info;
100
101 insert into my_non_tx_table(a)
102 select Relay_log_pos from mysql.slave_relay_log_info;
103
104 insert into my_bh_table(a)
105 select Relay_log_pos from mysql.slave_relay_log_info;
106
107 insert into my_tx_table(a)
108 select Master_log_pos from mysql.slave_master_info;
109
110 insert into my_non_tx_table(a)
111 select Master_log_pos from mysql.slave_master_info;
112
113 insert into my_bh_table(a)
114 select Master_log_pos from mysql.slave_master_info;
115
116 insert into my_tx_table(a)
117 select Relay_log_pos from mysql.slave_worker_info;
118
119 insert into my_non_tx_table(a)
120 select Relay_log_pos from mysql.slave_worker_info;
121
122 insert into my_bh_table(a)
123 select Relay_log_pos from mysql.slave_worker_info;
124
125 use test;
126 drop table if exists marker_multi_update;
127
128 use my_replicated_db;
129
130 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
131 update my_tx_table, performance_schema.setup_instruments
132 set my_tx_table.a = my_tx_table.a + 1,
133 performance_schema.setup_instruments.timed= 'NO';
134
135 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
136 update my_non_tx_table, performance_schema.setup_instruments
137 set my_non_tx_table.a = my_non_tx_table.a + 1,
138 performance_schema.setup_instruments.timed= 'NO';
139
140 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
141 update my_bh_table, performance_schema.setup_instruments
142 set my_bh_table.a = my_bh_table.a + 1,
143 performance_schema.setup_instruments.timed= 'NO';
144
145 use test;
146 drop table if exists marker_multi_delete;
147
148 use my_replicated_db;
149
150 insert into performance_schema.setup_actors
151 values ('FOO', 'FOO', 'FOO', 'YES', 'YES');
152
153 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
154 delete my_tx_table.*, performance_schema.setup_actors.*
155 from my_tx_table, performance_schema.setup_actors
156 where my_tx_table.a != 1000
157 or performance_schema.setup_actors.role='FOO';
158
159 insert into performance_schema.setup_actors
160 values ('BAR', 'BAR', 'BAR', 'YES', 'YES');
161
162 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
163 delete my_non_tx_table.*, performance_schema.setup_actors.*
164 from my_non_tx_table, performance_schema.setup_actors
165 where my_non_tx_table.a != 1000
166 or performance_schema.setup_actors.role='BAR';
167
168 insert into performance_schema.setup_actors
169 values ('BAZ', 'BAZ', 'BAZ', 'YES', 'YES');
170
171 --error 0, ER_BINLOG_STMT_MODE_AND_NO_REPL_TABLES
172 delete my_bh_table.*, performance_schema.setup_actors.*
173 from my_bh_table, performance_schema.setup_actors
174 where my_bh_table.a != 1000
175 or performance_schema.setup_actors.role='BAZ';
176
177 use test;
178 drop table if exists marker_end;
179
180 drop database my_replicated_db;
181
182 --source include/show_binlog_events.inc
183
184 # Restore performance_schema.setup_actors, damaged by this script
185
186 truncate table performance_schema.setup_actors;
187 insert into performance_schema.setup_actors values ('%', '%', '%', 'YES', 'YES');
188