Debian Codegraph @

mysql-test/suite/perfschema/r/rpl_statements.result

raw
1 include/master-slave.inc
2 [connection master]
3 #
4 #
5 # STEP 1 - CREATE AND REPLICATE TEST TABLES
6 #
7 connection master;
8 #
9 # *** Create test tables
10 #
11 show global variables like 'binlog_format%';
12 Variable_name Value
13 binlog_format MIXED
14 drop table if exists test.marker;
15 select thread_id into @my_thread_id
16 from performance_schema.threads
17 where processlist_id = connection_id();
18 create table test.marker(s1 int) engine=innodb;
19 connection slave;
20 #
21 # *** Clear statement events
22 #
23 #
24 # STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE
25 #
26 connection master;
27 #
28 insert into test.marker values (0);
29 #
30 connection slave;
31 #
32 # *** Verify row, get replication thread id, clear statement events
33 #
34 select thread_id into @slave_thread_id from performance_schema.events_statements_history
35 where sql_text like '%marker%';
36 # *** Verify row inserted on master was replicated
37 select count(*) = 1 as 'Expect 1' from test.marker;
38 Expect 1
39 1
40 # *** Clear statement events
41 #
42 #
43 # STEP 3 - PERFORM DML STATEMENTS ON MASTER
44 #
45 connection master;
46 #
47 show global variables like 'binlog_format%';
48 Variable_name Value
49 binlog_format MIXED
50 # *** Clear statement events
51 #
52 # *** Create/drop table, create/drop database
53 #
54 create database marker1_db;
55 create database marker2_db;
56 create table marker1_db.table1 (s1 int) engine=innodb;
57 create table marker2_db.table1 (s1 int) engine=innodb;
58 create table marker2_db.table2 (s1 int) engine=innodb;
59 #
60 # *** Transaction
61 start transaction;
62 insert into marker1_db.table1 values (1), (2), (3);
63 insert into marker2_db.table1 values (1), (2), (3);
64 commit;
65 #
66 # *** Alter
67 alter table marker1_db.table1 add column (s2 varchar(32));
68 #
69 # *** Insert, Update
70 start transaction;
71 insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six');
72 update marker1_db.table1 set s1 = s1 + 1;
73 commit;
74 #
75 # *** Rollback
76 start transaction;
77 insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine');
78 rollback;
79 #
80 # *** Autocommit, Delete, Drop
81 delete from marker1_db.table1 where s1 > 4;
82 drop table marker2_db.table1;
83 drop database marker2_db;
84 #
85 # *** Examine statements events that will be compared on the slave
86 #
87 select thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text from performance_schema.events_statements_history_long
88 where sql_text like '%marker%' order by event_id;
89 thread_id event_id event_name current_schema digest_text sql_text
90 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker1_db` create database marker1_db
91 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker2_db` create database marker2_db
92 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker1_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker1_db.table1 (s1 int) engine=innodb
93 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table1 (s1 int) engine=innodb
94 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table2` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table2 (s1 int) engine=innodb
95 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3)
96 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3)
97 [THREAD_ID] [EVENT_ID] statement/sql/alter_table test ALTER TABLE `marker1_db` . `table1` ADD COLUMN ( `s2` VARCHARACTER (?) ) alter table marker1_db.table1 add column (s2 varchar(32))
98 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six')
99 [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1
100 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine')
101 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4
102 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` drop table marker2_db.table1
103 [THREAD_ID] [EVENT_ID] statement/sql/drop_db test DROP SCHEMA `marker2_db` drop database marker2_db
104 #
105 #
106 # STEP 4 - REPLICATE STATEMENT EVENTS ON MASTER TO SLAVE
107 #
108 #
109 # *** Store statement events in holding table, then replicate
110 #
111 #
112 # Create table to hold statement events for later comparison on the slave
113 #
114 create table test.master_events_statements_history_long as
115 (select thread_id, event_id, event_name, sql_text, digest, digest_text, current_schema, rows_affected
116 from performance_schema.events_statements_history_long
117 where (thread_id=@my_thread_id and digest_text like '%marker%'));
118 #
119 #
120 # STEP 5 - VERIFY DML AND DDL STATEMENT EVENTS ON SLAVE
121 #
122 connection slave;
123 #
124 # *** List statement events from master
125 #
126 select thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text from master_events_statements_history_long order by event_id;
127 thread_id event_id event_name current_schema digest_text sql_text
128 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker1_db` create database marker1_db
129 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker2_db` create database marker2_db
130 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker1_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker1_db.table1 (s1 int) engine=innodb
131 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table1 (s1 int) engine=innodb
132 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table2` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table2 (s1 int) engine=innodb
133 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3)
134 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3)
135 [THREAD_ID] [EVENT_ID] statement/sql/alter_table test ALTER TABLE `marker1_db` . `table1` ADD COLUMN ( `s2` VARCHARACTER (?) ) alter table marker1_db.table1 add column (s2 varchar(32))
136 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six')
137 [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1
138 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine')
139 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4
140 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` drop table marker2_db.table1
141 [THREAD_ID] [EVENT_ID] statement/sql/drop_db test DROP SCHEMA `marker2_db` drop database marker2_db
142 #
143 # *** List statement events on slave
144 #
145 select thread_id, event_id, rpad(event_name, 28, ' ') event_name, rpad(current_schema, 10, ' ') current_schema, rpad(digest_text, 72, ' ') digest_text, sql_text from performance_schema.events_statements_history_long
146 where thread_id = @slave_thread_id and sql_text like '%marker%' order by event_id;
147 thread_id event_id event_name current_schema digest_text sql_text
148 [THREAD_ID] [EVENT_ID] statement/sql/create_db marker1_db CREATE SCHEMA `marker1_db` create database marker1_db
149 [THREAD_ID] [EVENT_ID] statement/sql/create_db marker2_db CREATE SCHEMA `marker2_db` create database marker2_db
150 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker1_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker1_db.table1 (s1 int) engine=innodb
151 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table1` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table1 (s1 int) engine=innodb
152 [THREAD_ID] [EVENT_ID] statement/sql/create_table test CREATE TABLE `marker2_db` . `table2` ( `s1` INTEGER ) ENGINE = `innodb` create table marker2_db.table2 (s1 int) engine=innodb
153 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3)
154 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3)
155 [THREAD_ID] [EVENT_ID] statement/sql/alter_table test ALTER TABLE `marker1_db` . `table1` ADD COLUMN ( `s2` VARCHARACTER (?) ) alter table marker1_db.table1 add column (s2 varchar(32))
156 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (...) /* , ... */ insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six')
157 [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1
158 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4
159 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` DROP TABLE `marker2_db`.`table1` /* generated by server */
160 [THREAD_ID] [EVENT_ID] statement/sql/drop_db marker2_db DROP SCHEMA `marker2_db` drop database marker2_db
161 #
162 # *** Compare master and slave events
163 #
164
165 # *** Event name comparison - expect 0 mismatches
166
167 select thread_id, event_id, event_name, digest_text, sql_text from performance_schema.events_statements_history_long t1
168 where t1.thread_id = @slave_thread_id and
169 sql_text like '%marker%' and
170 not exists (select * from master_events_statements_history_long t2 where t2.event_name = t1.event_name);
171 thread_id event_id event_name digest_text sql_text
172
173 # *** Statement digest comparison - expect 1 mismatch for DROP TABLE
174
175 select thread_id, event_id, event_name, digest, digest_text, sql_text from performance_schema.events_statements_history_long t1
176 where t1.thread_id = @slave_thread_id and
177 sql_text like '%marker%' and
178 not exists (select * from master_events_statements_history_long t2 where t2.digest = t1.digest);
179 thread_id event_id event_name digest digest_text sql_text
180 #
181 #
182 # STEP 6 - DISABLE REPLICATED STATEMENT EVENTS ON SLAVE
183 #
184 update performance_schema.setup_instruments set enabled='no', timed='no'
185 where name like '%statement/abstract/relay_log%';
186 select * from performance_schema.setup_instruments where name like '%statement/abstract/relay_log%';
187 NAME ENABLED TIMED
188 statement/abstract/relay_log NO NO
189 #
190 #
191 # STEP 7 - UPDATE TABLES ON MASTER, REPLICATE
192 #
193 connection master;
194 #
195 # *** Clear statement events
196 # *** Update some tables, then replicate
197 #
198 insert into marker1_db.table1 values (999, '999'), (998, '998'), (997, '997');
199 #
200 #
201 # STEP 8 - VERIFY TABLE UPDATES FROM MASTER, EXPECT NO STATEMENT EVENTS ON SLAVE
202 #
203 connection slave;
204 #
205 # *** Confirm rows were replicated
206 #
207 select * from marker1_db.table1 where s1 > 900 order by s1;
208 s1 s2
209 997 997
210 998 998
211 999 999
212 #
213 # *** Confirm that are no statements events from the replication thread
214 #
215 select * from performance_schema.events_statements_history_long
216 where thread_id = @slave_thread_id;
217 THREAD_ID EVENT_ID END_EVENT_ID EVENT_NAME SOURCE TIMER_START TIMER_END TIMER_WAIT LOCK_TIME SQL_TEXT DIGEST DIGEST_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME OBJECT_INSTANCE_BEGIN MYSQL_ERRNO RETURNED_SQLSTATE MESSAGE_TEXT ERRORS WARNINGS ROWS_AFFECTED ROWS_SENT ROWS_EXAMINED CREATED_TMP_DISK_TABLES CREATED_TMP_TABLES SELECT_FULL_JOIN SELECT_FULL_RANGE_JOIN SELECT_RANGE SELECT_RANGE_CHECK SELECT_SCAN SORT_MERGE_PASSES SORT_RANGE SORT_ROWS SORT_SCAN NO_INDEX_USED NO_GOOD_INDEX_USED NESTING_EVENT_ID NESTING_EVENT_TYPE NESTING_EVENT_LEVEL
218 #
219 #
220 # STEP 9 - CLEAN UP
221 #
222 #
223 include/rpl_end.inc