Debian Codegraph @

mysql-test/suite/rpl/r/rpl_user_variables.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 include/sync_slave_sql_with_master.inc
7 reset master;
8 include/stop_slave.inc
9 RESET MASTER;
10 include/start_slave.inc
11 create table t1(n char(30)) CHARACTER SET latin1;
12 set @i1:=12345678901234, @i2:=-12345678901234, @i3:=0, @i4:=-1;
13 set @s1:='This is a test', @r1:=12.5, @r2:=-12.5;
14 set @n1:=null;
15 set @s2:='', @s3:='abc\'def', @s4:= 'abc\\def', @s5:= 'abc''def';
16 insert into t1 values (@i1), (@i2), (@i3), (@i4);
17 insert into t1 values (@r1), (@r2);
18 insert into t1 values (@s1), (@s2), (@s3), (@s4), (@s5);
19 insert into t1 values (@n1);
20 insert into t1 values (@n2);
21 insert into t1 values (@a:=0), (@a:=@a+1), (@a:=@a+1);
22 Warnings:
23 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
24 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
25 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
26 insert into t1 values (@a+(@b:=@a+1));
27 Warnings:
28 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
29 set @q:='abc';
30 insert t1 values (@q), (@q:=concat(@q, 'n1')), (@q:=concat(@q, 'n2'));
31 Warnings:
32 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
33 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
34 set @a:=5;
35 insert into t1 values (@a),(@a);
36 select * from t1 where n = '<nonexistant>';
37 n
38 insert into t1 values (@a),(@a),(@a*5);
39 SELECT * FROM t1 ORDER BY n;
40 n
41 NULL
42 NULL
43 NULL
44 NULL
45 NULL
46
47 -1
48 -12.5
49 -12345678901234
50 0
51 0
52 1
53 12.5
54 12345678901234
55 2
56 5
57 5
58 5
59 abc
60 abc'def
61 abc'def
62 abcn1
63 abcn1n2
64 abc\def
65 This is a test
66 include/sync_slave_sql_with_master.inc
67 SELECT * FROM t1 ORDER BY n;
68 n
69 NULL
70 NULL
71 NULL
72 NULL
73 NULL
74
75 -1
76 -12.5
77 -12345678901234
78 0
79 0
80 1
81 12.5
82 12345678901234
83 2
84 5
85 5
86 5
87 abc
88 abc'def
89 abc'def
90 abcn1
91 abcn1n2
92 abc\def
93 This is a test
94 insert into t1 select * FROM (select @var1 union select @var2) AS t2;
95 drop table t1;
96 End of 4.1 tests.
97 DROP TABLE IF EXISTS t20;
98 DROP TABLE IF EXISTS t21;
99 DROP PROCEDURE IF EXISTS test.insert;
100 CREATE TABLE t20 (a VARCHAR(20));
101 CREATE TABLE t21 (a VARCHAR(20));
102 CREATE PROCEDURE test.insert()
103 BEGIN
104 IF (@VAR)
105 THEN
106 INSERT INTO test.t20 VALUES ('SP_TRUE');
107 ELSE
108 INSERT INTO test.t20 VALUES ('SP_FALSE');
109 END IF;
110 END|
111 CREATE TRIGGER test.insert_bi BEFORE INSERT
112 ON test.t20 FOR EACH ROW
113 BEGIN
114 IF (@VAR)
115 THEN
116 INSERT INTO test.t21 VALUES ('TRIG_TRUE');
117 ELSE
118 INSERT INTO test.t21 VALUES ('TRIG_FALSE');
119 END IF;
120 END|
121 include/sync_slave_sql_with_master.inc
122 SET @VAR=0;
123 CALL test.insert();
124 SET @VAR=1;
125 CALL test.insert();
126 On master: Check the tables for correct data
127 SELECT * FROM t20;
128 a
129 SP_FALSE
130 SP_TRUE
131 SELECT * FROM t21;
132 a
133 TRIG_FALSE
134 TRIG_TRUE
135 include/sync_slave_sql_with_master.inc
136 On slave: Check the tables for correct data and it matches master
137 SELECT * FROM t20;
138 a
139 SP_FALSE
140 SP_TRUE
141 SELECT * FROM t21;
142 a
143 TRIG_FALSE
144 TRIG_TRUE
145 DROP TABLE t20;
146 DROP TABLE t21;
147 DROP PROCEDURE test.insert;
148 DROP TABLE IF EXISTS t1;
149 DROP FUNCTION IF EXISTS test.square;
150 CREATE TABLE t1 (i INT);
151 CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN
152 (@var * @var);
153 SET @var = 1;
154 INSERT INTO t1 VALUES (square());
155 SET @var = 2;
156 INSERT INTO t1 VALUES (square());
157 SET @var = 3;
158 INSERT INTO t1 VALUES (square());
159 SET @var = 4;
160 INSERT INTO t1 VALUES (square());
161 SET @var = 5;
162 INSERT INTO t1 VALUES (square());
163 On master: Retrieve the values from the table
164 SELECT * FROM t1;
165 i
166 1
167 4
168 9
169 16
170 25
171 include/sync_slave_sql_with_master.inc
172 On slave: Retrieve the values from the table and verify they are the same as on master
173 SELECT * FROM t1;
174 i
175 1
176 4
177 9
178 16
179 25
180 DROP TABLE t1;
181 DROP FUNCTION test.square;
182 DROP TABLE IF EXISTS t1;
183 DROP FUNCTION IF EXISTS f1;
184 DROP FUNCTION IF EXISTS f2;
185 CREATE TABLE t1(a int);
186 CREATE FUNCTION f1() returns int deterministic BEGIN
187 return @a;
188 END |
189 CREATE FUNCTION f2() returns int deterministic BEGIN
190 IF (@b > 0) then
191 SET @c = (@a + @b);
192 else
193 SET @c = (@a - 1);
194 END if;
195 return @c;
196 END |
197 include/sync_slave_sql_with_master.inc
198 SET @a=500;
199 INSERT INTO t1 values(f1());
200 SET @b = 125;
201 SET @c = 1;
202 INSERT INTO t1 values(f2());
203 On master: Retrieve the values from the table
204 include/sync_slave_sql_with_master.inc
205 SELECT * from t1;
206 a
207 500
208 625
209 On slave: Check the tables for correct data and it matches master
210 SELECT * from t1;
211 a
212 500
213 625
214 DROP TABLE t1;
215 DROP FUNCTION f1;
216 DROP FUNCTION f2;
217 DROP TABLE IF EXISTS t1;
218 DROP TABLE IF EXISTS t2;
219 CREATE TABLE t1 (i int);
220 CREATE TABLE t2 (k int);
221 CREATE trigger t1_bi before INSERT on t1 for each row BEGIN
222 INSERT INTO t2 values (@a);
223 SET @a:=42;
224 INSERT INTO t2 values (@a);
225 END |
226 include/sync_slave_sql_with_master.inc
227 SET @a:=100;
228 INSERT INTO t1 values (5);
229 On master: Check to see that data was inserted correctly in both tables
230 SELECT * from t1;
231 i
232 5
233 SELECT * from t2;
234 k
235 100
236 42
237 include/sync_slave_sql_with_master.inc
238 On slave: Check the tables for correct data and it matches master
239 SELECT * from t1;
240 i
241 5
242 SELECT * from t2;
243 k
244 100
245 42
246 drop table t1, t2;
247 create table t1(a int, b int);
248 prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
249 Warnings:
250 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
251 set @x=1;
252 execute s1 using @x;
253 select * from t1;
254 a b
255 2 1
256 include/sync_slave_sql_with_master.inc
257 select * from t1;
258 a b
259 2 1
260 drop table t1;
261 create table t1(a int);
262 insert into t1 values (1),(2);
263 prepare s1 from 'insert into t1 select a from t1 limit ?';
264 First, execute query using a signed integer
265 set @x=CAST(1 AS SIGNED);
266 execute s1 using @x;
267 Next, execute query using an unsigned integer
268 set @x=CAST(1 AS UNSIGNED);
269 execute s1 using @x;
270 select * from t1;
271 a
272 1
273 2
274 1
275 1
276 include/sync_slave_sql_with_master.inc
277 select * from t1;
278 a
279 1
280 2
281 1
282 1
283 drop table t1;
284 End of 5.0 tests.
285 DROP FUNCTION IF EXISTS f1;
286 DROP FUNCTION IF EXISTS f2;
287 CREATE TABLE t1 (i INT);
288 CREATE FUNCTION f1() RETURNS INT RETURN @a;
289 CREATE
290 FUNCTION f2() RETURNS INT BEGIN
291 INSERT INTO t1 VALUES (10 + @a);
292 RETURN 0;
293 END|
294 include/sync_slave_sql_with_master.inc
295 SET @a:=123;
296 SELECT f1(), f2();
297 f1() f2()
298 123 0
299 On master: Check to see that data was inserted correctly
300 INSERT INTO t1 VALUES(f1());
301 SELECT * FROM t1;
302 i
303 133
304 123
305 include/sync_slave_sql_with_master.inc
306 On slave: Check the table for correct data and it matches master
307 SELECT * FROM t1;
308 i
309 133
310 123
311 DROP FUNCTION f1;
312 DROP FUNCTION f2;
313 DROP TABLE t1;
314 include/sync_slave_sql_with_master.inc
315 include/rpl_end.inc