1 #####################################################################
4 # Purpose: To test that UDFs are replicated in both row based and #
5 # statement based format. This tests work completed in WL#3629. #
7 # This test is designed to exercise two of the three types of UDFs: #
8 # 1) UDFs via loadable libraries, and 2) UDFs with a SQL body. #
9 #####################################################################
11 --source include/have_udf.inc
14 # To run this tests the "sql/udf_example.c" need to be compiled into
15 # udf_example.so and LD_LIBRARY_PATH should be setup to point out where
21 drop table if exists t1;
25 # Test 1) Test UDFs via loadable libraries
27 --echo "*** Test 1) Test UDFs via loadable libraries ***
28 --echo "Running on the master"
30 --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
31 eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_LIB";
32 --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
33 eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB";
34 --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB
35 --error ER_CANT_FIND_DL_ENTRY
36 eval CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB";
37 --replace_column 3 UDF_LIB
38 SELECT * FROM mysql.func ORDER BY name;
45 # Check to see that UDF CREATE statements were replicated
46 --echo "Running on the slave"
48 --replace_column 3 UDF_LIB
49 SELECT * FROM mysql.func ORDER BY name;
54 # Use the UDFs to do something
55 --echo "Running on the master"
57 eval CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=$engine_type;
58 INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00));
59 INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00));
60 INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00));
61 INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00));
62 SELECT * FROM t1 ORDER BY sum;
65 sync_slave_with_master;
67 # Check to see if data was replicated
68 --echo "Running on the slave"
70 SELECT * FROM t1 ORDER BY sum;
72 # Check to see that the functions are available for execution on the slave
73 SELECT myfunc_int(25);
74 SELECT myfunc_double(75.00);
80 --echo "Running on the master"
82 DROP FUNCTION myfunc_double;
83 DROP FUNCTION myfunc_int;
84 SELECT * FROM mysql.func ORDER BY name;
87 sync_slave_with_master;
89 # Check to see if the UDFs were dropped on the slave
90 --echo "Running on the slave"
92 SELECT * FROM mysql.func ORDER BY name;
98 --echo "Running on the master"
104 # Test 2) Test UDFs with SQL body
106 --echo "*** Test 2) Test UDFs with SQL body ***
107 --echo "Running on the master"
109 CREATE FUNCTION myfuncsql_int(i INT) RETURNS INTEGER DETERMINISTIC RETURN i;
110 CREATE FUNCTION myfuncsql_double(d DOUBLE) RETURNS INTEGER DETERMINISTIC RETURN d * 2.00;
111 SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
114 sync_slave_with_master;
116 # Check to see that UDF CREATE statements were replicated
117 --echo "Running on the slave"
119 SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
124 # Use the UDFs to do something
125 --echo "Running on the master"
127 eval CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=$engine_type;
128 INSERT INTO t1 VALUES(myfuncsql_int(100), myfuncsql_double(50.00));
129 INSERT INTO t1 VALUES(myfuncsql_int(10), myfuncsql_double(5.00));
130 INSERT INTO t1 VALUES(myfuncsql_int(200), myfuncsql_double(25.00));
131 INSERT INTO t1 VALUES(myfuncsql_int(1), myfuncsql_double(500.00));
132 SELECT * FROM t1 ORDER BY sum;
135 sync_slave_with_master;
137 # Check to see if data was replicated
138 --echo "Running on the slave"
140 SELECT * FROM t1 ORDER BY sum;
145 # Modify the UDFs to add a comment
146 --echo "Running on the master"
148 ALTER FUNCTION myfuncsql_int COMMENT "This was altered.";
149 ALTER FUNCTION myfuncsql_double COMMENT "This was altered.";
150 SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
153 sync_slave_with_master;
155 # Check to see if data was replicated
156 --echo "Running on the slave"
158 SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
160 # Check to see that the functions are available for execution on the slave
161 SELECT myfuncsql_int(25);
162 SELECT myfuncsql_double(75.00);
168 --echo "Running on the master"
170 DROP FUNCTION myfuncsql_double;
171 DROP FUNCTION myfuncsql_int;
172 SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
175 sync_slave_with_master;
177 # Check to see if the UDFs were dropped on the slave
178 --echo "Running on the slave"
180 SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
186 --echo "Running on the master"