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 should work nicely with replication in all cases:
6# - STATEMENT binlog format
7# - MIXED binlog format
8# - ROW binlog format.
9
10RESET MASTER;
11
12--disable_warnings
13drop database if exists my_local_db;
14--enable_warnings
15
16create database my_local_db;
17
18create table my_local_db.my_tx_table(a bigint) engine = innodb;
19create table my_local_db.my_non_tx_table(a bigint) engine = myisam;
20
21use test;
22drop table if exists marker_start;
23
24# --binlog-ignore-db only works with the current database.
25use my_local_db;
26
27insert into my_tx_table(a)
28  values (1000), (2000), (3000);
29
30insert into my_non_tx_table(a)
31  values (1000), (2000), (3000);
32
33use test;
34drop table if exists marker_truncate;
35
36use performance_schema;
37truncate table events_waits_history;
38truncate table events_waits_history_long;
39
40use test;
41truncate table performance_schema.events_statements_history_long;
42truncate table performance_schema.host_cache;
43
44use test;
45drop table if exists marker_update;
46
47use performance_schema;
48update setup_instruments set enabled='NO';
49update setup_instruments set timed='NO';
50use test;
51update performance_schema.setup_instruments set enabled='YES', timed='YES';
52update performance_schema.threads set instrumented='YES';
53
54use test;
55drop table if exists marker_insert;
56
57insert into performance_schema.setup_actors(`user`, `host`, `role`)
58  values ('XXX', 'XXX', 'XXX'),
59         ('YYY', 'YYY', 'YYY'),
60         ('ZZZ', 'ZZZ', 'ZZZ');
61
62select * from performance_schema.setup_actors
63  where user in ('XXX', 'YYY', 'ZZZ') order by user;
64
65insert into performance_schema.setup_objects
66  (object_type, object_schema, object_name, enabled, timed)
67  values ('TABLE', 'DB1', 'AAA', 'YES', 'YES'),
68         ('TABLE', 'DB1', 'BBB', 'NO', 'NO'),
69         ('TABLE', 'DB2', 'CCC', 'YES', 'NO'),
70         ('TABLE', 'DB2', 'DDD', 'NO', 'YES');
71
72select * from performance_schema.setup_objects
73  where object_schema like 'DB%' order by object_name;
74
75use test;
76drop table if exists marker_insert_select;
77
78# Note:
79# The queries used here do not make any sense (no semantic).
80# What this test is interrested in, is check the behavior
81# when replicating queries that mix both:
82# - non replicated tables
83# - replicated tables
84
85use my_local_db;
86insert into my_tx_table(a)
87  select thread_id from performance_schema.threads;
88
89insert into my_non_tx_table(a)
90  select thread_id from performance_schema.threads;
91
92insert into my_tx_table(a)
93  select id from information_schema.processlist;
94
95insert into my_non_tx_table(a)
96  select id from information_schema.processlist;
97
98insert into my_tx_table(a)
99  select thread_id from mysql.general_log;
100
101insert into my_non_tx_table(a)
102  select thread_id from mysql.general_log;
103
104insert into my_tx_table(a)
105  select thread_id from mysql.slow_log;
106
107insert into my_non_tx_table(a)
108  select thread_id from mysql.slow_log;
109
110insert into my_tx_table(a)
111  select Relay_log_pos from mysql.slave_relay_log_info;
112
113insert into my_non_tx_table(a)
114  select Relay_log_pos from mysql.slave_relay_log_info;
115
116insert into my_tx_table(a)
117  select Master_log_pos from mysql.slave_master_info;
118
119insert into my_non_tx_table(a)
120  select Master_log_pos from mysql.slave_master_info;
121
122insert into my_tx_table(a)
123  select Relay_log_pos from mysql.slave_worker_info;
124
125insert into my_non_tx_table(a)
126  select Relay_log_pos from mysql.slave_worker_info;
127
128use test;
129drop table if exists marker_delete;
130
131delete from performance_schema.setup_actors
132  where user in ('XXX', 'YYY', 'ZZZ');
133
134delete from performance_schema.setup_objects
135  where object_schema like 'DB%';
136
137use test;
138drop table if exists marker_end;
139
140drop database my_local_db;
141
142# The content of the binlog dumped in the result file
143# should not contain any references to non-replicated tables.
144
145--source include/show_binlog_events.inc
146
147