diff options
Diffstat (limited to 'mysql-test/suite/perfschema/r/rpl_statements.result')
| -rw-r--r-- | mysql-test/suite/perfschema/r/rpl_statements.result | 241 |
1 files changed, 241 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/r/rpl_statements.result b/mysql-test/suite/perfschema/r/rpl_statements.result new file mode 100644 index 00000000000..e271cd2a7fa --- /dev/null +++ b/mysql-test/suite/perfschema/r/rpl_statements.result @@ -0,0 +1,241 @@ +include/master-slave.inc +[connection master] + +# +# STEP 1 - CREATE AND REPLICATE TEST TABLES +# + +************** +*** 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; +************** +*** SLAVE *** +************** + +*** Clear statement events + +# +# STEP 2 - REPLICATE ONE ROW ON MASTER TO GET REPLICATION THREAD ID ON SLAVE +# +************** +*** MASTER *** +************** + +insert into test.marker values (0); + +************** +*** 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 +# +************** +*** 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 +# +************** +*** 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 +[THREAD_ID] [EVENT_ID] statement/sql/drop_table [DIGEST] DROP TABLE `marker2_db` . `table1` DROP TABLE `marker2_db`.`table1` /* generated by server */ + +# +# 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 +# +************** +*** 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 +# +************** +*** 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 |