1##################################################################### 2# Author: Chuck Bell # 3# Date: 2006-12-21 # 4# Purpose: To test that UDFs are replicated in both row based and # 5# statement based format. This tests work completed in WL#3629. # 6# # 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##################################################################### 10 11--source include/have_udf.inc 12--source include/master-slave.inc 13 14disable_query_log; 15call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 16enable_query_log; 17 18# 19# To run this tests the "sql/udf_example.c" need to be compiled into 20# udf_example.so and LD_LIBRARY_PATH should be setup to point out where 21# the library are. 22# 23 24connection master; 25--disable_warnings 26drop table if exists t1; 27--enable_warnings 28 29# 30# Test 1) Test UDFs via loadable libraries 31# 32--echo "*** Test 1) Test UDFs via loadable libraries *** 33--enable_info 34--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB 35eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; 36--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB 37eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"; 38--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB 39--error ER_CANT_FIND_DL_ENTRY 40eval CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"; 41--replace_column 3 UDF_LIB 42SELECT * FROM mysql.func ORDER BY name; 43--disable_info 44 45save_master_pos; 46connection slave; 47sync_with_master; 48 49# Check to see that UDF CREATE statements were replicated 50--enable_info 51--replace_column 3 UDF_LIB 52SELECT * FROM mysql.func ORDER BY name; 53--disable_info 54 55connection master; 56 57# Use the UDFs to do something 58--enable_info 59eval CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=$engine_type; 60--disable_warnings 61INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00)); 62INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00)); 63INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00)); 64INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00)); 65SELECT * FROM t1 ORDER BY sum; 66--enable_warnings 67--disable_info 68 69sync_slave_with_master; 70 71# Check to see if data was replicated 72--enable_info 73SELECT * FROM t1 ORDER BY sum; 74 75# Check to see that the functions are available for execution on the slave 76SELECT myfunc_int(25); 77SELECT myfunc_double(75.00); 78--disable_info 79 80connection master; 81 82# Drop the functions 83--enable_info 84DROP FUNCTION myfunc_double; 85DROP FUNCTION myfunc_int; 86SELECT * FROM mysql.func ORDER BY name; 87--disable_info 88 89sync_slave_with_master; 90 91# Check to see if the UDFs were dropped on the slave 92--enable_info 93SELECT * FROM mysql.func ORDER BY name; 94--disable_info 95 96connection master; 97 98# Cleanup 99--enable_info 100DROP TABLE t1; 101--disable_info 102 103# 104# Test 2) Test UDFs with SQL body 105# 106--echo "*** Test 2) Test UDFs with SQL body *** 107--enable_info 108CREATE FUNCTION myfuncsql_int(i INT) RETURNS INTEGER DETERMINISTIC RETURN i; 109CREATE FUNCTION myfuncsql_double(d DOUBLE) RETURNS INTEGER DETERMINISTIC RETURN d * 2.00; 110SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 111--disable_info 112 113sync_slave_with_master; 114 115# Check to see that UDF CREATE statements were replicated 116--enable_info 117SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 118--disable_info 119 120connection master; 121 122# Use the UDFs to do something 123--enable_info 124eval CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=$engine_type; 125INSERT INTO t1 VALUES(myfuncsql_int(100), myfuncsql_double(50.00)); 126INSERT INTO t1 VALUES(myfuncsql_int(10), myfuncsql_double(5.00)); 127INSERT INTO t1 VALUES(myfuncsql_int(200), myfuncsql_double(25.00)); 128INSERT INTO t1 VALUES(myfuncsql_int(1), myfuncsql_double(500.00)); 129SELECT * FROM t1 ORDER BY sum; 130--disable_info 131 132sync_slave_with_master; 133 134# Check to see if data was replicated 135--enable_info 136SELECT * FROM t1 ORDER BY sum; 137--disable_info 138 139connection master; 140 141# Modify the UDFs to add a comment 142--enable_info 143ALTER FUNCTION myfuncsql_int COMMENT "This was altered."; 144ALTER FUNCTION myfuncsql_double COMMENT "This was altered."; 145SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 146--disable_info 147 148sync_slave_with_master; 149 150# Check to see if data was replicated 151--enable_info 152SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 153 154# Check to see that the functions are available for execution on the slave 155SELECT myfuncsql_int(25); 156SELECT myfuncsql_double(75.00); 157--disable_info 158 159connection master; 160 161# Drop the functions 162--enable_info 163DROP FUNCTION myfuncsql_double; 164DROP FUNCTION myfuncsql_int; 165SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 166--disable_info 167 168sync_slave_with_master; 169 170# Check to see if the UDFs were dropped on the slave 171--enable_info 172SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 173--disable_info 174 175connection master; 176 177# Cleanup 178--enable_info 179DROP TABLE t1; 180--disable_info 181