mysql 创建函数是报错!
Last_IO_Error:
Last_SQL_Errno: 1418
Last_SQL_Error: Error '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)' on query. Default database: 'kf'. Query: 'CREATE DEFINER=`root`@`localhost` FUNCTION `func_supername`(siteids VARCHAR(32)) RETURNS varchar(300) CHARSET utf8
BEGIN DECLARE superpid int DEFAULT 0;
DECLARE ssname varchar(255) DEFAULT NULL;
set superpid = (select (case when t2.pid is null then 0 else t2.pid end) spid from t2d_enterpriseinfo t1 left JOIN t2d_site_classify t2 on t1.classifyid = t2.id where siteid = siteids); set ssname = (select t2.name from t2d_enterpriseinfo t1 left JOIN t2d_site_classify t2 on t1.classifyid = t2.id where siteid = siteids);
解决办法:
mysql> show variables like ' log_bin_trust_function_creators';
Empty set (0.00 sec)
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.6.2.1
Master_User: slave3
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000423
Read_Master_Log_Pos: 95536598
Relay_Log_File: mysql-relay-bin.001260
Relay_Log_Pos: 972250501
Relay_Master_Log_File: mysql-bin.000422
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 972250338
Relay_Log_Space: 1169284268
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 10902
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0