1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
|
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
set binlog_format=row;
drop table if exists t1;
"*** Test 1) Test UDFs via loadable libraries ***
"Running on the master"
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
affected rows: 0
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
affected rows: 0
CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
ERROR HY000: Can't find symbol 'myfunc_nonexist' in library
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
myfunc_double 1 UDF_LIB function
myfunc_int 2 UDF_LIB function
affected rows: 2
"Running on the slave"
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
myfunc_double 1 UDF_LIB function
myfunc_int 2 UDF_LIB function
affected rows: 2
"Running on the master"
CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM;
affected rows: 0
INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00));
affected rows: 1
INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00));
affected rows: 1
INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00));
affected rows: 1
INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00));
affected rows: 1
SELECT * FROM t1 ORDER BY sum;
sum price
1 48.5
10 48.75
100 48.6
200 49
affected rows: 4
"Running on the slave"
SELECT * FROM t1 ORDER BY sum;
sum price
1 48.5
10 48.75
100 48.6
200 49
affected rows: 4
SELECT myfunc_int(25);
myfunc_int(25)
25
affected rows: 1
SELECT myfunc_double(75.00);
myfunc_double(75.00)
50.00
affected rows: 1
"Running on the master"
DROP FUNCTION myfunc_double;
affected rows: 0
DROP FUNCTION myfunc_int;
affected rows: 0
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
affected rows: 0
"Running on the slave"
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
affected rows: 0
"Running on the master"
DROP TABLE t1;
affected rows: 0
"*** Test 2) Test UDFs with SQL body ***
"Running on the master"
CREATE FUNCTION myfuncsql_int(i INT) RETURNS INTEGER DETERMINISTIC RETURN i;
affected rows: 0
CREATE FUNCTION myfuncsql_double(d DOUBLE) RETURNS INTEGER DETERMINISTIC RETURN d * 2.00;
affected rows: 0
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00
test myfuncsql_int FUNCTION i INT RETURN i
affected rows: 2
"Running on the slave"
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00
test myfuncsql_int FUNCTION i INT RETURN i
affected rows: 2
"Running on the master"
CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM;
affected rows: 0
INSERT INTO t1 VALUES(myfuncsql_int(100), myfuncsql_double(50.00));
affected rows: 1
INSERT INTO t1 VALUES(myfuncsql_int(10), myfuncsql_double(5.00));
affected rows: 1
INSERT INTO t1 VALUES(myfuncsql_int(200), myfuncsql_double(25.00));
affected rows: 1
INSERT INTO t1 VALUES(myfuncsql_int(1), myfuncsql_double(500.00));
affected rows: 1
SELECT * FROM t1 ORDER BY sum;
sum price
1 1000
10 10
100 100
200 50
affected rows: 4
"Running on the slave"
SELECT * FROM t1 ORDER BY sum;
sum price
1 1000
10 10
100 100
200 50
affected rows: 4
"Running on the master"
ALTER FUNCTION myfuncsql_int COMMENT "This was altered.";
affected rows: 0
ALTER FUNCTION myfuncsql_double COMMENT "This was altered.";
affected rows: 0
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 This was altered.
test myfuncsql_int FUNCTION i INT RETURN i This was altered.
affected rows: 2
"Running on the slave"
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 This was altered.
test myfuncsql_int FUNCTION i INT RETURN i This was altered.
affected rows: 2
SELECT myfuncsql_int(25);
myfuncsql_int(25)
25
affected rows: 1
SELECT myfuncsql_double(75.00);
myfuncsql_double(75.00)
150
affected rows: 1
"Running on the master"
DROP FUNCTION myfuncsql_double;
affected rows: 0
DROP FUNCTION myfuncsql_int;
affected rows: 0
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
affected rows: 0
"Running on the slave"
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
affected rows: 0
"Running on the master"
DROP TABLE t1;
affected rows: 0
set binlog_format=statement;
drop table if exists t1;
"*** Test 1) Test UDFs via loadable libraries ***
"Running on the master"
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
affected rows: 0
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
affected rows: 0
CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
ERROR HY000: Can't find symbol 'myfunc_nonexist' in library
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
myfunc_double 1 UDF_LIB function
myfunc_int 2 UDF_LIB function
affected rows: 2
"Running on the slave"
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
myfunc_double 1 UDF_LIB function
myfunc_int 2 UDF_LIB function
affected rows: 2
"Running on the master"
CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM;
affected rows: 0
INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00));
Warnings:
Warning 1592 Statement is not safe to log in statement format.
affected rows: 1
INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00));
Warnings:
Warning 1592 Statement is not safe to log in statement format.
affected rows: 1
INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00));
Warnings:
Warning 1592 Statement is not safe to log in statement format.
affected rows: 1
INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00));
Warnings:
Warning 1592 Statement is not safe to log in statement format.
affected rows: 1
SELECT * FROM t1 ORDER BY sum;
sum price
1 48.5
10 48.75
100 48.6
200 49
affected rows: 4
"Running on the slave"
SELECT * FROM t1 ORDER BY sum;
sum price
1 48.5
10 48.75
100 48.6
200 49
affected rows: 4
SELECT myfunc_int(25);
myfunc_int(25)
25
affected rows: 1
SELECT myfunc_double(75.00);
myfunc_double(75.00)
50.00
affected rows: 1
"Running on the master"
DROP FUNCTION myfunc_double;
affected rows: 0
DROP FUNCTION myfunc_int;
affected rows: 0
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
affected rows: 0
"Running on the slave"
SELECT * FROM mysql.func ORDER BY name;
name ret dl type
affected rows: 0
"Running on the master"
DROP TABLE t1;
affected rows: 0
"*** Test 2) Test UDFs with SQL body ***
"Running on the master"
CREATE FUNCTION myfuncsql_int(i INT) RETURNS INTEGER DETERMINISTIC RETURN i;
affected rows: 0
CREATE FUNCTION myfuncsql_double(d DOUBLE) RETURNS INTEGER DETERMINISTIC RETURN d * 2.00;
affected rows: 0
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00
test myfuncsql_int FUNCTION i INT RETURN i
affected rows: 2
"Running on the slave"
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00
test myfuncsql_int FUNCTION i INT RETURN i
affected rows: 2
"Running on the master"
CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM;
affected rows: 0
INSERT INTO t1 VALUES(myfuncsql_int(100), myfuncsql_double(50.00));
affected rows: 1
INSERT INTO t1 VALUES(myfuncsql_int(10), myfuncsql_double(5.00));
affected rows: 1
INSERT INTO t1 VALUES(myfuncsql_int(200), myfuncsql_double(25.00));
affected rows: 1
INSERT INTO t1 VALUES(myfuncsql_int(1), myfuncsql_double(500.00));
affected rows: 1
SELECT * FROM t1 ORDER BY sum;
sum price
1 1000
10 10
100 100
200 50
affected rows: 4
"Running on the slave"
SELECT * FROM t1 ORDER BY sum;
sum price
1 1000
10 10
100 100
200 50
affected rows: 4
"Running on the master"
ALTER FUNCTION myfuncsql_int COMMENT "This was altered.";
affected rows: 0
ALTER FUNCTION myfuncsql_double COMMENT "This was altered.";
affected rows: 0
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 This was altered.
test myfuncsql_int FUNCTION i INT RETURN i This was altered.
affected rows: 2
"Running on the slave"
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 This was altered.
test myfuncsql_int FUNCTION i INT RETURN i This was altered.
affected rows: 2
SELECT myfuncsql_int(25);
myfuncsql_int(25)
25
affected rows: 1
SELECT myfuncsql_double(75.00);
myfuncsql_double(75.00)
150
affected rows: 1
"Running on the master"
DROP FUNCTION myfuncsql_double;
affected rows: 0
DROP FUNCTION myfuncsql_int;
affected rows: 0
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
affected rows: 0
"Running on the slave"
SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name;
db name type param_list body comment
affected rows: 0
"Running on the master"
DROP TABLE t1;
affected rows: 0
|