summaryrefslogtreecommitdiff
path: root/mysql-test/suite/perfschema/r/rpl_statements.result
blob: 431b8445309a6872aeadba0cc4ec6f9944396a43 (plain)
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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
include/master-slave.inc
[connection master]

#
# STEP 1 - CREATE AND REPLICATE TEST TABLES
#
connection master;

*** Create test tables

show variables like 'binlog_format%';
Variable_name	Value
binlog_format	MIXED
drop table if exists test.marker;
select thread_id into @my_thread_id
from performance_schema.threads
where processlist_id = connection_id();
create table test.marker(s1 int) engine=innodb;
connection slave;

*** Clear statement events

#
# STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE
#
connection master;

insert into test.marker values (0);

connection slave;

*** Verify row, get replication thread id, clear statement events

select thread_id into @slave_thread_id from performance_schema.events_statements_history
where sql_text like '%marker%';
*** Verify row inserted on master was replicated
select count(*) = 1 as 'Expect 1' from test.marker;
Expect 1
1
*** Clear statement events

#
# STEP 3 - PERFORM DML STATEMENTS ON MASTER
#
connection master;

show variables like 'binlog_format%';
Variable_name	Value
binlog_format	MIXED
*** Clear statement events

*** Create/drop table, create/drop database

create database marker1_db;
create database marker2_db;
create table marker1_db.table1 (s1 int) engine=innodb;
create table marker2_db.table1 (s1 int) engine=innodb;
create table marker2_db.table2 (s1 int) engine=innodb;

*** Transaction
start transaction;
insert into marker1_db.table1 values (1), (2), (3);
insert into marker2_db.table1 values (1), (2), (3);
commit;

*** Alter
alter table marker1_db.table1 add column (s2 varchar(32));

*** Insert, Update
start transaction;
insert into marker1_db.table1 values (4, 'four'), (5, 'five'), (6, 'six');
update marker1_db.table1 set s1 = s1 + 1;
commit;

*** Rollback
start transaction;
insert into marker1_db.table1 values (7, 'seven'), (8, 'eight'), (9, 'nine');
rollback;

*** Autocommit, Delete, Drop
delete from marker1_db.table1 where s1 > 4;
drop table marker2_db.table1;
drop database marker2_db;

*** Examine statements events that will be compared on the slave

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
where sql_text like '%marker%' order by event_id;
thread_id	event_id	event_name	current_schema	digest_text	sql_text
[THREAD_ID]	[EVENT_ID]	statement/sql/create_db     	test      	CREATE SCHEMA `marker1_db`                                              	create database marker1_db
[THREAD_ID]	[EVENT_ID]	statement/sql/create_db     	test      	CREATE SCHEMA `marker2_db`                                              	create database marker2_db
[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
[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
[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
[THREAD_ID]	[EVENT_ID]	statement/sql/insert        	test      	INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */              	insert into marker1_db.table1 values (1), (2), (3)
[THREAD_ID]	[EVENT_ID]	statement/sql/insert        	test      	INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */              	insert into marker2_db.table1 values (1), (2), (3)
[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))
[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')
[THREAD_ID]	[EVENT_ID]	statement/sql/update        	test      	UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ?                      	update marker1_db.table1 set s1 = s1 + 1
[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')
[THREAD_ID]	[EVENT_ID]	statement/sql/delete        	test      	DELETE FROM `marker1_db` . `table1` WHERE `s1` > ?                      	delete from marker1_db.table1 where s1 > 4
[THREAD_ID]	[EVENT_ID]	statement/sql/drop_table    	test      	DROP TABLE `marker2_db` . `table1`                                      	drop table marker2_db.table1
[THREAD_ID]	[EVENT_ID]	statement/sql/drop_db       	test      	DROP SCHEMA `marker2_db`                                                	drop database marker2_db

#
# STEP 4 - REPLICATE STATEMENT EVENTS ON MASTER TO SLAVE
#

*** Store statement events in holding table, then replicate

#
# Create table to hold statement events for later comparison on the slave
#
create table test.master_events_statements_history_long as
(select thread_id, event_id, event_name, sql_text, digest, digest_text, current_schema, rows_affected
from performance_schema.events_statements_history_long
where (thread_id=@my_thread_id and digest_text like '%marker%'));

#
# STEP 5 - VERIFY DML AND DDL STATEMENT EVENTS ON SLAVE
#
connection slave;

*** List statement events from master

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;
thread_id	event_id	event_name	current_schema	digest_text	sql_text
[THREAD_ID]	[EVENT_ID]	statement/sql/create_db     	test      	CREATE SCHEMA `marker1_db`                                              	create database marker1_db
[THREAD_ID]	[EVENT_ID]	statement/sql/create_db     	test      	CREATE SCHEMA `marker2_db`                                              	create database marker2_db
[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
[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
[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
[THREAD_ID]	[EVENT_ID]	statement/sql/insert        	test      	INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */              	insert into marker1_db.table1 values (1), (2), (3)
[THREAD_ID]	[EVENT_ID]	statement/sql/insert        	test      	INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */              	insert into marker2_db.table1 values (1), (2), (3)
[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))
[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')
[THREAD_ID]	[EVENT_ID]	statement/sql/update        	test      	UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ?                      	update marker1_db.table1 set s1 = s1 + 1
[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')
[THREAD_ID]	[EVENT_ID]	statement/sql/delete        	test      	DELETE FROM `marker1_db` . `table1` WHERE `s1` > ?                      	delete from marker1_db.table1 where s1 > 4
[THREAD_ID]	[EVENT_ID]	statement/sql/drop_table    	test      	DROP TABLE `marker2_db` . `table1`                                      	drop table marker2_db.table1
[THREAD_ID]	[EVENT_ID]	statement/sql/drop_db       	test      	DROP SCHEMA `marker2_db`                                                	drop database marker2_db

*** List statement events on slave

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
where thread_id = @slave_thread_id and sql_text like '%marker%' order by event_id;
thread_id	event_id	event_name	current_schema	digest_text	sql_text
[THREAD_ID]	[EVENT_ID]	statement/sql/create_db     	marker1_db	CREATE SCHEMA `marker1_db`                                              	create database marker1_db
[THREAD_ID]	[EVENT_ID]	statement/sql/create_db     	marker2_db	CREATE SCHEMA `marker2_db`                                              	create database marker2_db
[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
[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
[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
[THREAD_ID]	[EVENT_ID]	statement/sql/insert        	test      	INSERT INTO `marker1_db` . `table1` VALUES (?) /* , ... */              	insert into marker1_db.table1 values (1), (2), (3)
[THREAD_ID]	[EVENT_ID]	statement/sql/insert        	test      	INSERT INTO `marker2_db` . `table1` VALUES (?) /* , ... */              	insert into marker2_db.table1 values (1), (2), (3)
[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))
[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')
[THREAD_ID]	[EVENT_ID]	statement/sql/update        	test      	UPDATE `marker1_db` . `table1` SET `s1` = `s1` + ?                      	update marker1_db.table1 set s1 = s1 + 1
[THREAD_ID]	[EVENT_ID]	statement/sql/delete        	test      	DELETE FROM `marker1_db` . `table1` WHERE `s1` > ?                      	delete from marker1_db.table1 where s1 > 4
[THREAD_ID]	[EVENT_ID]	statement/sql/drop_table    	test      	DROP TABLE `marker2_db` . `table1`                                      	DROP TABLE `marker2_db`.`table1` /* generated by server */
[THREAD_ID]	[EVENT_ID]	statement/sql/drop_db       	marker2_db	DROP SCHEMA `marker2_db`                                                	drop database marker2_db

*** Compare master and slave events


*** Event name comparison - expect 0 mismatches

select thread_id, event_id, event_name, digest_text, sql_text from performance_schema.events_statements_history_long t1
where t1.thread_id = @slave_thread_id and
sql_text like '%marker%' and
not exists (select * from master_events_statements_history_long t2 where t2.event_name = t1.event_name);
thread_id	event_id	event_name	digest_text	sql_text

*** Statement digest comparison - expect 1 mismatch for DROP TABLE

select thread_id, event_id, event_name, digest, digest_text, sql_text from performance_schema.events_statements_history_long t1
where t1.thread_id = @slave_thread_id and
sql_text like '%marker%' and
not exists (select * from master_events_statements_history_long t2 where t2.digest = t1.digest);
thread_id	event_id	event_name	digest	digest_text	sql_text

#
# STEP 6 - DISABLE REPLICATED STATEMENT EVENTS ON SLAVE
#
update performance_schema.setup_instruments set enabled='no', timed='no'
  where name like '%statement/abstract/relay_log%';
select * from performance_schema.setup_instruments where name like '%statement/abstract/relay_log%';
NAME	ENABLED	TIMED
statement/abstract/relay_log	NO	NO

#
# STEP 7 - UPDATE TABLES ON MASTER, REPLICATE
#
connection master;

*** Clear statement events
*** Update some tables, then replicate

insert into marker1_db.table1 values (999, '999'), (998, '998'), (997, '997');

#
# STEP 8 - VERIFY TABLE UPDATES FROM MASTER, EXPECT NO STATEMENT EVENTS ON SLAVE
#
connection slave;

*** Confirm rows were replicated

select * from marker1_db.table1 where s1 > 900 order by s1;
s1	s2
997	997
998	998
999	999

*** Confirm that are no statements events from the replication thread

select * from performance_schema.events_statements_history_long
where thread_id = @slave_thread_id;
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

#
# STEP 9 - CLEAN UP
#

include/rpl_end.inc