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
138
139
140
141
142
143
144
145
146
|
# Test replication, when using special non-replicated tables.
#
# This test involve special statements that use non-replicated tables.
# Changes affecting non replicated tables are never written to the binlog.
# Executing these statements should work nicely with replication in all cases:
# - STATEMENT binlog format
# - MIXED binlog format
# - ROW binlog format.
RESET MASTER;
--disable_warnings
drop database if exists my_local_db;
--enable_warnings
create database my_local_db;
create table my_local_db.my_tx_table(a bigint) engine = innodb;
create table my_local_db.my_non_tx_table(a bigint) engine = myisam;
use test;
drop table if exists marker_start;
# --binlog-ignore-db only works with the current database.
use my_local_db;
insert into my_tx_table(a)
values (1000), (2000), (3000);
insert into my_non_tx_table(a)
values (1000), (2000), (3000);
use test;
drop table if exists marker_truncate;
use performance_schema;
truncate table events_waits_history;
truncate table events_waits_history_long;
use test;
truncate table performance_schema.events_statements_history_long;
truncate table performance_schema.host_cache;
use test;
drop table if exists marker_update;
use performance_schema;
update setup_instruments set enabled='NO';
update setup_instruments set timed='NO';
use test;
update performance_schema.setup_instruments set enabled='YES', timed='YES';
update performance_schema.threads set instrumented='YES';
use test;
drop table if exists marker_insert;
insert into performance_schema.setup_actors(`user`, `host`, `role`)
values ('XXX', 'XXX', 'XXX'),
('YYY', 'YYY', 'YYY'),
('ZZZ', 'ZZZ', 'ZZZ');
select * from performance_schema.setup_actors
where user in ('XXX', 'YYY', 'ZZZ') order by user;
insert into performance_schema.setup_objects
(object_type, object_schema, object_name, enabled, timed)
values ('TABLE', 'DB1', 'AAA', 'YES', 'YES'),
('TABLE', 'DB1', 'BBB', 'NO', 'NO'),
('TABLE', 'DB2', 'CCC', 'YES', 'NO'),
('TABLE', 'DB2', 'DDD', 'NO', 'YES');
select * from performance_schema.setup_objects
where object_schema like 'DB%' order by object_name;
use test;
drop table if exists marker_insert_select;
# Note:
# The queries used here do not make any sense (no semantic).
# What this test is interrested in, is check the behavior
# when replicating queries that mix both:
# - non replicated tables
# - replicated tables
use my_local_db;
insert into my_tx_table(a)
select thread_id from performance_schema.threads;
insert into my_non_tx_table(a)
select thread_id from performance_schema.threads;
insert into my_tx_table(a)
select id from information_schema.processlist;
insert into my_non_tx_table(a)
select id from information_schema.processlist;
insert into my_tx_table(a)
select thread_id from mysql.general_log;
insert into my_non_tx_table(a)
select thread_id from mysql.general_log;
insert into my_tx_table(a)
select thread_id from mysql.slow_log;
insert into my_non_tx_table(a)
select thread_id from mysql.slow_log;
insert into my_tx_table(a)
select Relay_log_pos from mysql.slave_relay_log_info;
insert into my_non_tx_table(a)
select Relay_log_pos from mysql.slave_relay_log_info;
insert into my_tx_table(a)
select Master_log_pos from mysql.slave_master_info;
insert into my_non_tx_table(a)
select Master_log_pos from mysql.slave_master_info;
insert into my_tx_table(a)
select Relay_log_pos from mysql.slave_worker_info;
insert into my_non_tx_table(a)
select Relay_log_pos from mysql.slave_worker_info;
use test;
drop table if exists marker_delete;
delete from performance_schema.setup_actors
where user in ('XXX', 'YYY', 'ZZZ');
delete from performance_schema.setup_objects
where object_schema like 'DB%';
use test;
drop table if exists marker_end;
drop database my_local_db;
# The content of the binlog dumped in the result file
# should not contain any references to non-replicated tables.
--source include/show_binlog_events.inc
|
