Debian Codegraph @

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

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6
7 #
8 # STEP 1 - CREATE AND REPLICATE TEST TABLES
9 #
10
11 **************
12 *** MASTER ***
13 **************
14
15 *** Create test tables
16
17 show global variables like '%binlog_format%';
18 Variable_name Value
19 binlog_format MIXED
20 drop table if exists test.marker;
21 select thread_id into @my_thread_id
22 from performance_schema.threads
23 where processlist_id = connection_id();
24 create table test.marker(s1 int) engine=innodb;
25 **************
26 *** SLAVE ***
27 **************
28
29 *** Clear statement events
30
31 #
32 # STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE
33 #
34 **************
35 *** MASTER ***
36 **************
37
38 insert into test.marker values (0);
39
40 **************
41 *** SLAVE ***
42 **************
43
44 *** Verify row, get replication thread id, clear statement events
45
46 select thread_id into @slave_thread_id from performance_schema.events_statements_history
47 where sql_text like '%marker%';
48 *** Verify row inserted on master was replicated
49 select count(*) = 1 as 'Expect 1' from test.marker;
50 Expect 1
51 1
52 *** Clear statement events
53
54 #
55 # STEP 3 - PERFORM DML STATEMENTS ON MASTER
56 #
57 **************
58 *** MASTER ***
59 **************
60
61 show global variables like '%binlog_format%';
62 Variable_name Value
63 binlog_format MIXED
64 *** Clear statement events
65
66 *** Create/drop table, create/drop database
67
68 create database marker1_db;
69 create database marker2_db;
70 create table marker1_db.table1 (s1 int) engine=innodb;
71 create table marker2_db.table1 (s1 int) engine=innodb;
72 create table marker2_db.table2 (s1 int) engine=innodb;
73
74 *** Transaction
75 start transaction;
76 insert into marker1_db.table1 values (1), (2), (3);
77 insert into marker2_db.table1 values (1), (2), (3);
78 commit;
79
80 *** Alter
81 alter table marker1_db.table1 add column (s2 varchar(32));
82
83 *** Insert, Update
84 start transaction;
85 insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six');
86 update marker1_db.table1 set s1 = s1 + 1;
87 commit;
88
89 *** Rollback
90 start transaction;
91 insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine');
92 rollback;
93
94 *** Autocommit, Delete, Drop
95 delete from marker1_db.table1 where s1 > 4;
96 drop table marker2_db.table1;
97 drop database marker2_db;
98
99 *** Examine statements events that will be compared on the slave
100
101 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
102 where sql_text like '%marker%' order by event_id;
103 thread_id event_id event_name current_schema digest_text sql_text
104 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker1_db` create database marker1_db
105 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker2_db` create database marker2_db
106 [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
107 [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
108 [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
109 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3)
110 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3)
111 [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))
112 [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')
113 [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1
114 [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')
115 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4
116 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` drop table marker2_db.table1
117 [THREAD_ID] [EVENT_ID] statement/sql/drop_db test DROP SCHEMA `marker2_db` drop database marker2_db
118
119 #
120 # STEP 4 - REPLICATE STATEMENT EVENTS ON MASTER TO SLAVE
121 #
122
123 *** Store statement events in holding table, then replicate
124
125 #
126 # Create table to hold statement events for later comparison on the slave
127 #
128 create table test.master_events_statements_history_long as
129 (select thread_id, event_id, event_name, sql_text, digest, digest_text, current_schema, rows_affected
130 from performance_schema.events_statements_history_long
131 where (thread_id=@my_thread_id and digest_text like '%marker%'));
132
133 #
134 # STEP 5 - VERIFY DML AND DDL STATEMENT EVENTS ON SLAVE
135 #
136 **************
137 *** SLAVE ***
138 **************
139
140 *** List statement events from master
141
142 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;
143 thread_id event_id event_name current_schema digest_text sql_text
144 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker1_db` create database marker1_db
145 [THREAD_ID] [EVENT_ID] statement/sql/create_db test CREATE SCHEMA `marker2_db` create database marker2_db
146 [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
147 [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
148 [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
149 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3)
150 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3)
151 [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))
152 [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')
153 [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1
154 [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')
155 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4
156 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` drop table marker2_db.table1
157 [THREAD_ID] [EVENT_ID] statement/sql/drop_db test DROP SCHEMA `marker2_db` drop database marker2_db
158
159 *** List statement events on slave
160
161 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
162 where thread_id = @slave_thread_id and sql_text like '%marker%' order by event_id;
163 thread_id event_id event_name current_schema digest_text sql_text
164 [THREAD_ID] [EVENT_ID] statement/sql/create_db marker1_db CREATE SCHEMA `marker1_db` create database marker1_db
165 [THREAD_ID] [EVENT_ID] statement/sql/create_db marker2_db CREATE SCHEMA `marker2_db` create database marker2_db
166 [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
167 [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
168 [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
169 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */ insert into marker1_db.table1 values (1), (2), (3)
170 [THREAD_ID] [EVENT_ID] statement/sql/insert test INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */ insert into marker2_db.table1 values (1), (2), (3)
171 [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))
172 [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')
173 [THREAD_ID] [EVENT_ID] statement/sql/update test UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ? update marker1_db.table1 set s1 = s1 + 1
174 [THREAD_ID] [EVENT_ID] statement/sql/delete test DELETE FROM `marker1_db` . `table1` WHERE `s1` > ? delete from marker1_db.table1 where s1 > 4
175 [THREAD_ID] [EVENT_ID] statement/sql/drop_table test DROP TABLE `marker2_db` . `table1` DROP TABLE `marker2_db`.`table1` /* generated by server */
176 [THREAD_ID] [EVENT_ID] statement/sql/drop_db marker2_db DROP SCHEMA `marker2_db` drop database marker2_db
177
178 *** Compare master and slave events
179
180
181 *** Event name comparison - expect 0 mismatches
182
183 select thread_id, event_id, event_name, digest_text, sql_text from performance_schema.events_statements_history_long t1
184 where t1.thread_id = @slave_thread_id and
185 sql_text like '%marker%' and
186 not exists (select * from master_events_statements_history_long t2 where t2.event_name = t1.event_name);
187 thread_id event_id event_name digest_text sql_text
188
189 *** Statement digest comparison - expect 1 mismatch for DROP TABLE
190
191 select thread_id, event_id, event_name, digest, digest_text, sql_text from performance_schema.events_statements_history_long t1
192 where t1.thread_id = @slave_thread_id and
193 sql_text like '%marker%' and
194 not exists (select * from master_events_statements_history_long t2 where t2.digest = t1.digest);
195 thread_id event_id event_name digest digest_text sql_text
196
197 #
198 # STEP 6 - DISABLE REPLICATED STATEMENT EVENTS ON SLAVE
199 #
200 update performance_schema.setup_instruments set enabled='no', timed='no'
201 where name like '%statement/abstract/relay_log%';
202 select * from performance_schema.setup_instruments where name like '%statement/abstract/relay_log%';
203 NAME ENABLED TIMED PROPERTIES FLAGS VOLATILITY DOCUMENTATION
204 statement/abstract/relay_log NO NO mutable NULL 0 New event just read from the relay log. At this point, the real statement type is unknown, the type will be refined after parsing the event.
205
206 #
207 # STEP 7 - UPDATE TABLES ON MASTER, REPLICATE
208 #
209 **************
210 *** MASTER ***
211 **************
212
213 *** Clear statement events
214 *** Update some tables, then replicate
215
216 insert into marker1_db.table1 values (999, '999'), (998, '998'), (997, '997');
217
218 #
219 # STEP 8 - VERIFY TABLE UPDATES FROM MASTER, EXPECT NO STATEMENT EVENTS ON SLAVE
220 #
221 **************
222 *** SLAVE ***
223 **************
224
225 *** Confirm rows were replicated
226
227 select * from marker1_db.table1 where s1 > 900 order by s1;
228 s1 s2
229 997 997
230 998 998
231 999 999
232
233 *** Confirm that are no statements events from the replication thread
234
235 select * from performance_schema.events_statements_history_long
236 where thread_id = @slave_thread_id;
237 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 STATEMENT_ID CPU_TIME MAX_CONTROLLED_MEMORY MAX_TOTAL_MEMORY EXECUTION_ENGINE
238
239 #
240 # STEP 9 - CLEAN UP
241 #
242
243 include/rpl_end.inc