mysql自定义函数与log_bin_trust_function_creators参数

sweli

mysql自定义函数与log_bin_trust_function_creators参数
2011年12月29日
　　 问题1：
　　root@hupan 10:54:58>CREATE FUNCTION getDistance(x1 double,y1 double,x2 double,y2 double)
　　-> RETURNS double
　　-> BEGIN
　　-> declare Flattening,er,pix,z1,z2,d1,b1,b2,l1,l2,theta,dista nce double;
　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
　　root@hupan 10:54:58>set Flattening=298.257223563002;
　　ERROR 1193 (HY000): Unknown system variable 'Flattening'
　　root@hupan 10:54:58>set er=6378.137;
　　ERROR 1193 (HY000): Unknown system variable 'er'
　　root@hupan 10:54:58>set pix=3.1415926535;
　　ERROR 1193 (HY000): Unknown system variable 'pix'
　　root@hupan 10:54:58>set b1 = (0.5-x1/180)*pix;
　　ERROR 1193 (HY000): Unknown system variable 'b1'
　　root@hupan 10:54:58>set l1 = (y1/180)*pix;
　　ERROR 1193 (HY000): Unknown system variable 'l1'
　　root@hupan 10:54:58>set b2 = (0.5-x2/180)*pix;
　　ERROR 1193 (HY000): Unknown system variable 'b2'
　　root@hupan 10:54:58>set l2 = (y2/180)*pix;
　　ERROR 1193 (HY000): Unknown system variable 'l2'
　　root@hupan 10:54:58>set x1 = er*COS(l1)*SIN(b1);
　　ERROR 1193 (HY000): Unknown system variable 'x1'
　　root@hupan 10:54:58>set y1 = er*SIN(l1)*SIN(b1);
　　ERROR 1193 (HY000): Unknown system variable 'y1'
　　root@hupan 10:54:58>set z1 = er*COS(b1);
　　ERROR 1193 (HY000): Unknown system variable 'z1'
　　root@hupan 10:54:58>set x2 = er*COS(l2)*SIN(b2);
　　ERROR 1193 (HY000): Unknown system variable 'x2'
　　root@hupan 10:54:58>set y2 = er*SIN(l2)*SIN(b2);
　　ERROR 1193 (HY000): Unknown system variable 'y2'
　　root@hupan 10:54:58>set z2 = er*COS(b2);
　　ERROR 1193 (HY000): Unknown system variable 'z2'
　　root@hupan 10:54:58>set d1 = sqrt((x1-x2)*(x1-x2)+(y1-y2)*(y1-y2)+(z1-z2)*(z1-z 2));
　　ERROR 1193 (HY000): Unknown system variable 'd1'
　　root@hupan 10:54:58>set theta= acos((er*er+er*er-d1*d1)/(2*er*er));
　　ERROR 1193 (HY000): Unknown system variable 'theta'
　　root@hupan 10:54:58>set distance= theta*er;
　　ERROR 1193 (HY000): Unknown system variable 'distance'
　　root@hupan 10:54:58>RETURN distance;
　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURN distance' at line 1
　　root@hupan 10:54:58>END;
　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1
　　root@hupan 10:54:58>delemiter ;;
　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delemiter' at line 1
　　ERROR:
　　No query specified
　　root@hupan 10:54:58>delemiter ;
　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delemiter' at line 1
　　//
　　root@hupan 05:20:05>show variables like '%commit%';
　　+--------------------------------+-------+
　　| Variable_name                  | Value |
　　+--------------------------------+-------+
　　| autocommit                     | ON    |
　　| innodb_commit_concurrency      | 0     |
　　| innodb_flush_log_at_trx_commit | 2     |
　　+--------------------------------+-------+
　　3 rows in set (0.00 sec)
　　自动提交时不是将函数作为一个整体声明所以报错--------解决：用delimiter 修改结束符定义，将函数作为整体进行说明
　　问题2：
　　root@hupan 10:55:51>delimiter ;;
　　root@hupan 10:55:53>CREATE FUNCTION getDistance(x1 double,y1 double,x2 double,y2 double)
　　-> RETURNS double
　　-> BEGIN
　　-> declare Flattening,er,pix,z1,z2,d1,b1,b2,l1,l2,theta,dista nce double;
　　-> set Flattening=298.257223563002;
　　-> set er=6378.137;
　　-> set pix=3.1415926535;
　　-> set b1 = (0.5-x1/180)*pix;
　　-> set l1 = (y1/180)*pix;
　　-> set b2 = (0.5-x2/180)*pix;
　　-> set l2 = (y2/180)*pix;
　　-> set x1 = er*COS(l1)*SIN(b1);
　　-> set y1 = er*SIN(l1)*SIN(b1);
　　-> set z1 = er*COS(b1);
　　-> set x2 = er*COS(l2)*SIN(b2);
　　-> set y2 = er*SIN(l2)*SIN(b2);
　　-> set z2 = er*COS(b2);
　　-> set d1 = sqrt((x1-x2)*(x1-x2)+(y1-y2)*(y1-y2)+(z1-z2)*(z1-z 2));
　　-> set theta= acos((er*er+er*er-d1*d1)/(2*er*er));
　　-> set distance= theta*er;
　　-> RETURN distance;
　　-> END;
　　-> delimiter ;;
　　ERROR 1418 (HY000): 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)
　　root@hupan 10:55:53>delimiter ;
　　//
　　root@hupan 10:55:58>show variables like 'log_bin_trust_function_creators';
　　+---------------------------------+-------+
　　| Variable_name                   | Value |
　　+---------------------------------+-------+
　　| log_bin_trust_function_creators | OFF   |
　　+---------------------------------+-------+
　　1 row in set (0.00 sec)
　　log_bin_trust_function_creators
　　Command-Line Format    --log-bin-trust-function-creators        
　　Option-File Format    log-bin-trust-function-creators        
　　Option Sets Variable    Yes, log_bin_trust_function_creators         
　　Variable Name    log_bin_trust_function_creators        
　　Variable Scope    Global        
　　Dynamic Variable    Yes        
　　Permitted Values         
　　Type    boolean        
　　Default    FALSE     
　　This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 18.7, “Binary Logging of Stored Programs”.
　　主要是考虑binlog安全，如果没开binlog的话这个值是无意义的；打开binlog后需要超级权限才能定义，并且在sql上也有需求
　　ERROR 1418 (HY000): 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)
　　这个报错目前知道可以通过两个方式解决：
　　RETURNS    double  【READS SQL DATA/DETERMINISTIC】
　　即在自定义函数后增加一个只读数据或者指定字符的信息
　　参数参考手册：
　　http://dev.mysql.com/doc/refman/5.1/en/server-syst em-variables.html#sysvar_log_bin_trust_function_cre ators