Debian Codegraph @

mysql-test/suite/rpl/t/rpl_session_var.test

raw
1 # Replication of session variables.
2 # FOREIGN_KEY_CHECKS is tested in rpl_insert_id.test
3
4 --source include/not_group_replication_plugin.inc
5 source include/master-slave.inc;
6
7 disable_query_log;
8 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
9 enable_query_log;
10
11 drop table if exists t1;
12 create table t1(a varchar(100),b int);
13 set @@session.sql_mode=pipes_as_concat;
14 insert into t1 values('My'||'SQL', 1);
15 set @@session.sql_mode=default;
16 insert into t1 values('1'||'2', 2);
17 select * from t1 where b<3 order by a;
18 --source include/sync_slave_sql_with_master.inc
19 select * from t1 where b<3 order by a;
20 connection master;
21 # if the slave does the next sync_with_master fine, then it means it accepts the
22 # two lines of ANSI syntax below, which is what we want to check.
23 set @@session.sql_mode=ignore_space;
24 insert into t1 values(LTRIM (' MySQL'), 3);
25 set @@session.sql_mode=ansi_quotes;
26 create table "t2" ("a" int);
27 drop table t1, t2;
28 set @@session.sql_mode=default;
29 create table t1(a int auto_increment primary key);
30 create table t2(b int, a int);
31
32 #
33 # To verify Bug#19641806 SQL_AUTO_IS_NULL IS NOT REPLICATED, FAILURE
34 # IN RPL_SESSION_VAR AND RPL_INSERT_ID
35 # The bug manifested in various scenarios incl when the slave applier thread
36 # resumes having a next event to take on dependent on LAST_INSERT_ID.
37 # After fixing the event - INSERT..SELECT..where a-is-null - is processed correctly,
38 # which is proved with diffing the master and slave tables.
39 #
40
41 # The slave applier gets stopped and master prepares the required event.
42 --source include/sync_slave_sql_with_master.inc
43 --source include/stop_slave_sql.inc
44
45 --connection master
46 set @sav.sql_auto_is_null=@@session.sql_auto_is_null;
47 set @@session.sql_auto_is_null=1;
48 insert into t1 values(null);
49 insert into t2 select 1,a from t1 where a is null;
50 # Prove of a regular case where last_insert_id() is computed
51 # without any hint of @@session.last_insert_id.
52 --let $assert_text= set @@session.last_insert_id must be found in results of the insert..select
53 --let $assert_cond=`select count(*)=1 from t2 where a=last_insert_id()`
54 --source include/assert.inc
55
56 # Explicitly set @session.last_insert_id makes the select
57 # to find the last inserted value even when it was another connection
58 # that inserted it.
59 insert into t1 values(null);
60 --let $last_insert_id=`select last_insert_id()`
61
62 --connection master1
63 set @@session.sql_auto_is_null=1;
64 --eval set @@session.last_insert_id=$last_insert_id;
65 insert into t2 select 1,a from t1 where a is null;
66
67 --connection master
68 # Prove of a "hinted" last_insert_id case.
69 --let $assert_text= set @@session.last_insert_id must be found in results of the insert..select
70 --let $assert_cond=`select count(*)=1 from t2 where a=last_insert_id()`
71 --source include/assert.inc
72 insert into t1 values(null);
73 set @@session.sql_auto_is_null=0;
74 insert into t2 select 1,a from t1 where a is null;
75 insert into t1 values(null);
76 set @@session.last_insert_id=last_insert_id();
77 insert into t2 select 1,a from t1 where a is null;
78 --let $assert_text= set @@session.last_insert_id must *not* be found in results of the insert..select
79 --let $assert_cond=`select count(*)=0 from t2 where a=@@session.last_insert_id`
80 --source include/assert.inc
81 set @@session.sql_auto_is_null= @sav.sql_auto_is_null;
82
83 # The slave applier restarts with the first of the above queries' events
84 # which satisfy the mentioned above failure scenario.
85 --connection slave
86 --source include/start_slave_sql.inc
87
88 --connection master
89 --source include/sync_slave_sql_with_master.inc
90
91 # Bug#19641806 fixing correctness proof
92 let $diff_tables= master:t2, slave:t2;
93 --source include/diff_tables.inc
94
95 # Explicit mysqlbinlog testing in Bug#19641806 fixes is similar
96 # to the "hinted" last_insert_id above.
97 # An auto-inc INSERT is separated is followed by INSERT-SELECT
98 # in a new binlog file.
99 # The INSERT-SELECT binlog file is replayed, clearly by a different session,
100 # and correctly.
101 --connection master
102 delete from t1;
103 delete from t2;
104 insert into t1 values(null);
105 flush logs;
106 set @sav.sql_auto_is_null=@@session.sql_auto_is_null;
107 set @@session.sql_auto_is_null=1;
108 insert into t2 select 1,a from t1 where a is null;
109 set @@session.sql_auto_is_null= @sav.sql_auto_is_null;
110
111 --let $MYSQLD_DATADIR=`select @@datadir`
112 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
113 flush logs;
114 delete from t2;
115 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file > $MYSQLTEST_VARDIR/tmp/bug19641806.sql
116 --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug19641806.sql
117 --remove_file $MYSQLTEST_VARDIR/tmp/bug19641806.sql
118
119 # The same positive assert as above. This piece of test is ineffective
120 # with GTID is ON due to the ineffective replay from the binlog. The
121 # client set @@last_insert_id in GTID mode is tested above on l.63,
122 # see connection master1.
123 --let $assert_text= set @@session.last_insert_id must be found in binlog results of the insert..select
124 --let $assert_cond=`select @@global.gtid_mode = 'ON' or count(*)=1 from t2 where a=last_insert_id()`
125 --source include/assert.inc
126
127 --source include/sync_slave_sql_with_master.inc
128 let $diff_tables= master:t2, slave:t2;
129 --source include/diff_tables.inc
130 #
131 # eof of bug19641806 tests
132 #
133
134 # Cleanup
135 --connection master
136 drop table t1,t2;
137 --source include/sync_slave_sql_with_master.inc
138
139 --source include/rpl_end.inc