Debian Codegraph @

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

raw
1 #
2 # BUG#49562: SBR out of sync when using numeric data types + user variable
3 #
4
5 -- source include/not_group_replication_plugin.inc
6 -- source include/have_binlog_format_statement.inc
7 -- source include/force_myisam_default.inc
8 -- source include/have_myisam.inc
9
10 -- source include/master-slave.inc
11
12 ## Setup user variables for several numeric types, so that we get
13 ## coverage on the User_var_log_event different val types
14
15 -- let $max_unsigned_long= 18446744073709551615
16 -- let $min_signed_long= -9223372036854775808
17 -- eval SET @positive= $max_unsigned_long
18 -- eval SET @negative= $min_signed_long
19
20 CREATE TABLE t1 (`tinyint` TINYINT,
21 `smallint` SMALLINT,
22 `mediumint` MEDIUMINT,
23 `integer` INTEGER,
24 `bigint` BIGINT,
25 `utinyint` TINYINT UNSIGNED,
26 `usmallint` SMALLINT UNSIGNED,
27 `umediumint` MEDIUMINT UNSIGNED,
28 `uinteger` INTEGER UNSIGNED,
29 `ubigint` BIGINT UNSIGNED,
30 `double` DOUBLE,
31 `float` FLOAT,
32 `real` REAL(30,2),
33 `decimal` DECIMAL(30,2)) ENGINE = MyISAM;
34
35 -- disable_warnings
36
37 -- echo ### insert max unsigned
38 -- echo ### a) declarative
39 -- eval INSERT IGNORE INTO t1 VALUES ($max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long,$max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long);
40
41 -- echo #########################################
42 -- echo #### [ on master ]
43 -- query_vertical SELECT * FROM t1
44 --source include/sync_slave_sql_with_master.inc
45 -- echo #### [ on slave ]
46 -- query_vertical SELECT * FROM t1
47 -- echo #########################################
48 -- connection master
49 -- echo ## assertion: master and slave tables are in sync
50 -- let $diff_tables=master:t1,slave:t1
51 -- source include/diff_tables.inc
52 -- connection master
53 TRUNCATE t1;
54
55 -- echo ### b) user var
56 INSERT IGNORE INTO t1 VALUES (@positive,
57 @positive,
58 @positive,
59 @positive,
60 @positive,
61 @positive,
62 @positive,
63 @positive,
64 @positive,
65 @positive,
66 @positive,
67 @positive,
68 @positive,
69 @positive);
70
71 -- echo #########################################
72 -- echo #### [ on master ]
73 -- query_vertical SELECT * FROM t1
74 --source include/sync_slave_sql_with_master.inc
75 -- echo #### [ on slave ]
76 -- query_vertical SELECT * FROM t1
77 -- echo #########################################
78 -- connection master
79 -- echo ## assertion: master and slave tables are in sync
80 -- let $diff_tables=master:t1,slave:t1
81 -- source include/diff_tables.inc
82 -- connection master
83 TRUNCATE t1;
84
85
86 -- echo ### insert min signed
87 -- echo ### a) declarative
88 -- eval INSERT IGNORE INTO t1 VALUES ($min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long,$min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long);
89
90 -- echo #########################################
91 -- echo #### [ on master ]
92 -- query_vertical SELECT * FROM t1
93 --source include/sync_slave_sql_with_master.inc
94 -- echo #### [ on slave ]
95 -- query_vertical SELECT * FROM t1
96 -- echo #########################################
97 -- connection master
98 -- echo ## assertion: master and slave tables are in sync
99 -- let $diff_tables=master:t1,slave:t1
100 -- source include/diff_tables.inc
101 -- connection master
102 TRUNCATE t1;
103
104 -- echo ### b) user var
105 INSERT IGNORE INTO t1 VALUES (@negative,
106 @negative,
107 @negative,
108 @negative,
109 @negative,
110 @negative,
111 @negative,
112 @negative,
113 @negative,
114 @negative,
115 @negative,
116 @negative,
117 @negative,
118 @negative);
119
120 -- echo #########################################
121 -- echo #### [ on master ]
122 -- query_vertical SELECT * FROM t1
123 --source include/sync_slave_sql_with_master.inc
124 -- echo #### [ on slave ]
125 -- query_vertical SELECT * FROM t1
126 -- echo #########################################
127 -- connection master
128
129 -- echo ## assertion: master and slave tables are in sync
130 -- let $diff_tables=master:t1,slave:t1
131 -- source include/diff_tables.inc
132 -- connection master
133 TRUNCATE t1;
134
135 -- echo ## check: contents of both tables master's and slave's
136 -- enable_warnings
137
138 ## cleanup
139 -- connection master
140 DROP TABLE t1;
141 --source include/sync_slave_sql_with_master.inc
142
143 #####################################################################
144 #
145 # BUG#51426
146 #
147 #####################################################################
148 --source include/rpl_reset.inc
149 -- connection master
150
151 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
152
153 CREATE TABLE t1 ( c INT, PRIMARY KEY (c)) Engine=MyISAM;
154
155 # offending trigger that would reset the unsigned flag for aux before
156 # binlogging of User_var_log_event would take place.
157 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ;
158
159 SET @aux = 10294947273192243200;
160 SET @aux1= @aux;
161 -- error ER_DUP_ENTRY
162 INSERT INTO t1 VALUES (@aux) , (@aux1);
163
164 --source include/sync_slave_sql_with_master.inc
165
166 -- echo ## assertion: master and slave tables are in sync
167 -- let $diff_tables=master:t1,slave:t1
168 -- source include/diff_tables.inc
169
170 --connection master
171 DROP TRIGGER tr1;
172 DROP TABLE t1;
173
174 SET sql_mode = default;
175
176 --source include/sync_slave_sql_with_master.inc
177 --source include/rpl_end.inc