1. Check whether the function is Enabled:
Mysql> show variables like '% func % ';
+ ----------------------------------------- + ------- +
| Variable_name | Value |
+ ----------------------------------------- + ------- +
| Log_bin_trust_function_creators | ON |
+ ----------------------------------------- + ------- +
1 row in set (0.02 sec)
2. If the Value of Value is OFF, enable it.
Mysql> set global log_bin_trust_function_creators = 1;
Use app02
Delimiter //
Mysql> drop function if exists 'testhanshu ';
Create function 'testhanshu' ('tustate' int)
RETURNS varchar (2000)
BEGIN
Declare oneAddr varchar (200) default '';
Declare allAddr varchar (2000) default '';
DECLARE done int default false;
Declare curl CURSOR for select utruename from tsys_user where ustate = tustate;
Declare continue handler for not found set done = 1;
Open curl;
REPEAT
FETCH curl INTO oneAddr;
If not done THEN
Set oneAddr = CONCAT (oneAddr ,';');
Set allAddr = CONCAT (allAddr, oneAddr );
End if;
UNTIL done end repeat;
Close curl;
RETURN allAddr;
END;
Mysql> select testhanshu (1 );
Java. SQL. SQLException: This function has none of DETERMINISTIC, no SQL, or reads SQL DATA in its declaration and binary logging is enabled (you * might * want to use the less safe log_bin_trust_function_creators variable)
There are three solutions:
1. log on to the MySql client and run: set global log_bin_trust_function_creators = 1;
2. When logging on to the MySQL server, add the "-- log-bin-trust-function-creators = 1" parameter when starting the service and set it to 1.
3. In the [mysqld] section of my. ini (my. cnf), add log-bin-trust-function-creators = 1.