1
by brian
clean slate |
1 |
#############################################################################
|
2 |
# Original Author: JBM #
|
|
3 |
# Original Date: Aug/15/2005 #
|
|
4 |
# Update: 08/29/2005 Comment out sleep. Only needed for debugging #
|
|
5 |
#############################################################################
|
|
6 |
# Note: Many lines are commented out in this test case. These were used for #
|
|
7 |
# creating the test case and debugging and are being left for #
|
|
8 |
# debugging, but they can not be used for the regular testing as the #
|
|
9 |
# Time changes and is not deteministic, so instead we dump both the #
|
|
10 |
# master and slave and diff the dumps. If the dumps differ then the #
|
|
11 |
# test case will fail. To run during diff failuers, comment out the #
|
|
12 |
# diff. #
|
|
13 |
# Test: Tests MySQL stored function using RAND() and a flow control. The #
|
|
14 |
# test inserts rows into a givin table with the function used in #
|
|
15 |
# the insert statement. Depending on the RAND() value returned #
|
|
16 |
# inside the function one set of text is returned. In addition, #
|
|
17 |
# it uses a transaction to see the effect a rollback has on master #
|
|
18 |
# Vs slave. #
|
|
19 |
#############################################################################
|
|
20 |
||
21 |
# Begin clean up test section |
|
22 |
connection master; |
|
23 |
--disable_warnings |
|
24 |
DROP FUNCTION IF EXISTS test.f1; |
|
25 |
DROP TABLE IF EXISTS test.t1; |
|
26 |
||
27 |
--enable_warnings |
|
28 |
||
29 |
||
30 |
eval CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, c CHAR(16),PRIMARY KEY(a))ENGINE=$engine_type; |
|
31 |
||
32 |
delimiter |; |
|
33 |
create function test.f1() RETURNS CHAR(16) |
|
34 |
BEGIN |
|
35 |
DECLARE tmp CHAR(16); |
|
36 |
DECLARE var_name FLOAT; |
|
37 |
SET var_name = RAND(); |
|
38 |
IF var_name > .6 |
|
39 |
THEN SET tmp = 'Texas'; |
|
40 |
ELSE SET tmp = 'MySQL'; |
|
41 |
END IF; |
|
42 |
RETURN tmp; |
|
43 |
END| |
|
44 |
delimiter ;| |
|
45 |
||
46 |
INSERT INTO test.t1 VALUES (null,test.f1()),(null,test.f1()),(null,test.f1()); |
|
47 |
sleep 6; |
|
48 |
INSERT INTO test.t1 VALUES (null,test.f1()),(null,test.f1()),(null,test.f1()); |
|
49 |
sleep 6; |
|
50 |
||
51 |
#Select in this test are used for debugging |
|
52 |
#select * from test.t1; |
|
53 |
#connection slave; |
|
54 |
#select * from test.t1; |
|
55 |
||
56 |
connection master; |
|
57 |
SET AUTOCOMMIT=0; |
|
58 |
START TRANSACTION; |
|
59 |
INSERT INTO test.t1 VALUES (null,test.f1()); |
|
60 |
ROLLBACK; |
|
61 |
SET AUTOCOMMIT=1; |
|
62 |
#select * from test.t1; |
|
63 |
#sleep 6; |
|
64 |
||
65 |
#connection slave; |
|
66 |
#select * from test.t1; |
|
67 |
||
68 |
#connection master; |
|
69 |
||
70 |
#used for debugging |
|
71 |
#show binlog events; |
|
72 |
||
73 |
# time to dump the databases and so we can see if they match |
|
74 |
||
319.1.1
by Grant Limberg
renamed all instances of MYSQL_ to DRIZZLE_ |
75 |
--exec $DRIZZLE_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/func003_master.sql |
76 |
--exec $DRIZZLE_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/func003_slave.sql |
|
1
by brian
clean slate |
77 |
|
78 |
# First lets cleanup |
|
79 |
DROP FUNCTION test.f1; |
|
80 |
DROP TABLE test.t1; |
|
81 |
||
82 |
||
83 |
# the test will show that the diff statement failed and no reject file |
|
84 |
# will be created. You will need to go to the mysql-test dir and diff |
|
85 |
# the files yourself to see what is not matching :-) File are located |
|
86 |
# in $MYSQLTEST_VARDIR/tmp |
|
87 |
||
88 |
diff_files $MYSQLTEST_VARDIR/tmp/func003_master.sql $MYSQLTEST_VARDIR/tmp/func003_slave.sql; |
|
89 |
||
90 |
||
91 |
# End of 5.0 test case |