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