1include/master-slave.inc 2[connection master] 3connection slave; 4reset master; 5connection master; 6create table t1(n char(30)); 7set @i1:=12345678901234, @i2:=-12345678901234, @i3:=0, @i4:=-1; 8set @s1:='This is a test', @r1:=12.5, @r2:=-12.5; 9set @n1:=null; 10set @s2:='', @s3:='abc\'def', @s4:= 'abc\\def', @s5:= 'abc''def'; 11insert into t1 values (@i1), (@i2), (@i3), (@i4); 12insert into t1 values (@r1), (@r2); 13insert into t1 values (@s1), (@s2), (@s3), (@s4), (@s5); 14insert into t1 values (@n1); 15insert into t1 values (@n2); 16insert into t1 values (@a:=0), (@a:=@a+1), (@a:=@a+1); 17insert into t1 values (@a+(@b:=@a+1)); 18set @q:='abc'; 19insert t1 values (@q), (@q:=concat(@q, 'n1')), (@q:=concat(@q, 'n2')); 20set @a:=5; 21insert into t1 values (@a),(@a); 22select * from t1 where n = '<nonexistant>'; 23n 24connection master1; 25insert into t1 values (@a),(@a),(@a*5); 26SELECT * FROM t1 ORDER BY n; 27n 28NULL 29NULL 30NULL 31NULL 32NULL 33 34-1 35-12.5 36-12345678901234 370 380 391 4012.5 4112345678901234 422 435 445 455 46abc 47abc'def 48abc'def 49abcn1 50abcn1n2 51abc\def 52This is a test 53connection slave; 54SELECT * FROM t1 ORDER BY n; 55n 56NULL 57NULL 58NULL 59NULL 60NULL 61 62-1 63-12.5 64-12345678901234 650 660 671 6812.5 6912345678901234 702 715 725 735 74abc 75abc'def 76abc'def 77abcn1 78abcn1n2 79abc\def 80This is a test 81connection master; 82insert into t1 select * FROM (select @var1 union select @var2) AS t2; 83drop table t1; 84End of 4.1 tests. 85DROP TABLE IF EXISTS t20; 86DROP TABLE IF EXISTS t21; 87DROP PROCEDURE IF EXISTS test.insert; 88CREATE TABLE t20 (a VARCHAR(20)); 89CREATE TABLE t21 (a VARCHAR(20)); 90CREATE PROCEDURE test.insert() 91BEGIN 92IF (@VAR) 93THEN 94INSERT INTO test.t20 VALUES ('SP_TRUE'); 95ELSE 96INSERT INTO test.t20 VALUES ('SP_FALSE'); 97END IF; 98END| 99CREATE TRIGGER test.insert_bi BEFORE INSERT 100ON test.t20 FOR EACH ROW 101BEGIN 102IF (@VAR) 103THEN 104INSERT INTO test.t21 VALUES ('TRIG_TRUE'); 105ELSE 106INSERT INTO test.t21 VALUES ('TRIG_FALSE'); 107END IF; 108END| 109connection slave; 110connection master; 111SET @VAR=0; 112CALL test.insert(); 113SET @VAR=1; 114CALL test.insert(); 115Check the tables for correct data 116SELECT * FROM t20; 117a 118SP_FALSE 119SP_TRUE 120SELECT * FROM t21; 121a 122TRIG_FALSE 123TRIG_TRUE 124connection slave; 125Check the tables for correct data and it matches master 126SELECT * FROM t20; 127a 128SP_FALSE 129SP_TRUE 130SELECT * FROM t21; 131a 132TRIG_FALSE 133TRIG_TRUE 134connection master; 135DROP TABLE t20; 136DROP TABLE t21; 137DROP PROCEDURE test.insert; 138DROP TABLE IF EXISTS t1; 139DROP FUNCTION IF EXISTS test.square; 140CREATE TABLE t1 (i INT); 141CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN 142(@var * @var); 143SET @var = 1; 144INSERT INTO t1 VALUES (square()); 145SET @var = 2; 146INSERT INTO t1 VALUES (square()); 147SET @var = 3; 148INSERT INTO t1 VALUES (square()); 149SET @var = 4; 150INSERT INTO t1 VALUES (square()); 151SET @var = 5; 152INSERT INTO t1 VALUES (square()); 153Retrieve the values from the table 154SELECT * FROM t1; 155i 1561 1574 1589 15916 16025 161connection slave; 162Retrieve the values from the table and verify they are the same as on master 163SELECT * FROM t1; 164i 1651 1664 1679 16816 16925 170connection master; 171DROP TABLE t1; 172DROP FUNCTION test.square; 173DROP TABLE IF EXISTS t1; 174DROP FUNCTION IF EXISTS f1; 175DROP FUNCTION IF EXISTS f2; 176CREATE TABLE t1(a int); 177CREATE FUNCTION f1() returns int deterministic BEGIN 178return @a; 179END | 180CREATE FUNCTION f2() returns int deterministic BEGIN 181IF (@b > 0) then 182SET @c = (@a + @b); 183else 184SET @c = (@a - 1); 185END if; 186return @c; 187END | 188connection slave; 189connection master; 190SET @a=500; 191INSERT INTO t1 values(f1()); 192SET @b = 125; 193SET @c = 1; 194INSERT INTO t1 values(f2()); 195Retrieve the values from the table 196connection slave; 197connection master; 198SELECT * from t1; 199a 200500 201625 202connection slave; 203Check the tables for correct data and it matches master 204SELECT * from t1; 205a 206500 207625 208connection master; 209DROP TABLE t1; 210DROP FUNCTION f1; 211DROP FUNCTION f2; 212DROP TABLE IF EXISTS t1; 213DROP TABLE IF EXISTS t2; 214CREATE TABLE t1 (i int); 215CREATE TABLE t2 (k int); 216CREATE trigger t1_bi before INSERT on t1 for each row BEGIN 217INSERT INTO t2 values (@a); 218SET @a:=42; 219INSERT INTO t2 values (@a); 220END | 221connection slave; 222connection master; 223SET @a:=100; 224INSERT INTO t1 values (5); 225Check to see that data was inserted correctly in both tables 226SELECT * from t1; 227i 2285 229SELECT * from t2; 230k 231100 23242 233connection slave; 234Check the tables for correct data and it matches master 235SELECT * from t1; 236i 2375 238SELECT * from t2; 239k 240100 24142 242connection master; 243drop table t1, t2; 244connection master; 245create table t1(a int, b int); 246prepare s1 from 'insert into t1 values (@x:=@x+1, ?)'; 247set @x=1; 248execute s1 using @x; 249select * from t1; 250a b 2512 1 252connection slave; 253connection slave; 254select * from t1; 255a b 2562 1 257connection master; 258drop table t1; 259connection master; 260create table t1(a int); 261insert into t1 values (1),(2); 262prepare s1 from 'insert into t1 select a from t1 limit ?'; 263set @x='1.1'; 264execute s1 using @x; 265select * from t1; 266a 2671 2682 2691 270connection slave; 271connection slave; 272select * from t1; 273a 2741 2752 2761 277connection master; 278drop table t1; 279End of 5.0 tests. 280DROP FUNCTION IF EXISTS f1; 281DROP FUNCTION IF EXISTS f2; 282CREATE TABLE t1 (i INT); 283CREATE FUNCTION f1() RETURNS INT RETURN @a; 284CREATE 285FUNCTION f2() RETURNS INT BEGIN 286INSERT INTO t1 VALUES (10 + @a); 287RETURN 0; 288END| 289connection slave; 290connection master; 291SET @a:=123; 292SELECT f1(), f2(); 293f1() f2() 294123 0 295Check to see that data was inserted correctly 296INSERT INTO t1 VALUES(f1()); 297SELECT * FROM t1; 298i 299133 300123 301connection slave; 302Check the table for correct data and it matches master 303SELECT * FROM t1; 304i 305133 306123 307connection master; 308DROP FUNCTION f1; 309DROP FUNCTION f2; 310DROP TABLE t1; 311connection slave; 312include/rpl_end.inc 313