Error Code: 1418. 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_crea

Try Adding DELIMITER around your code i.e

DELIMITER $$
CREATE FUNCTION return_distance (lat_a DOUBLE, long_a DOUBLE, lat_b DOUBLE, long_b DOUBLE) RETURNS DOUBLE  BEGIN  DECLARE distance DOUBLE;  SET distance = SIN(RADIANS(lat_a)) * SIN(RADIANS(lat_b))  + COS(RADIANS(lat_a))  * COS(RADIANS(lat_b))  * COS(RADIANS(long_a - long_b));  RETURN((DEGREES(ACOS(distance))) * 69.09);  END$$
DELIMITER;

Select allOpen in new window

the tutorial had $delimiter around it , but I get same error.


DELIMITER $$
CREATE FUNCTION return_distance (lat_a DOUBLE, long_a DOUBLE, lat_b DOUBLE, long_b DOUBLE) RETURNS DOUBLE  BEGIN  DECLARE distance DOUBLE;  SET distance = SIN(RADIANS(lat_a)) * SIN(RADIANS(lat_b))  + COS(RADIANS(lat_a))  * COS(RADIANS(lat_b))  * COS(RADIANS(long_a - long_b));  RETURN((DEGREES(ACOS(distance))) * 69.09);  END$$
DELIMITER;



Error Code: 1418. 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)

Weird - works here - what version of MySQL are you on?

MySQL Version :
5.5.24

could this be a setting in mysql workbench since I am using
MySQL Version :
5.5.24

I have access to change mysql settings because I am using wamp on my windows7 desktop

I am on 5.0.27
I have also tested on a couple of other servers
5.5.33-29.3 - Success
5.1.58 - Success

Can't really give you any indication as to why it is failing - nothing wrong with the definition.

If you are on WAMP have you tried executing it through PHPMyAdmin ?

in phpmyadmin

Error
SQL query:

DELIMITER $$ CREATE FUNCTION return_distance(

lat_a DOUBLE,
long_a DOUBLE,
lat_b DOUBLE,
long_b DOUBLE
) RETURNS DOUBLE BEGIN DECLARE distance DOUBLE;

SET distance = SIN( RADIANS( lat_a ) ) * SIN( RADIANS( lat_b ) ) + COS( RADIANS( lat_a ) ) * COS( RADIANS( lat_b ) ) * COS( RADIANS( long_a - long_b ) ) ;

RETURN (
(
DEGREES( ACOS( distance ) )
) * 69.09
);

END$$
MySQL said: Documentation

#1418 - 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)


I have access to change mysql settings because I am using wamp on my windows7 desktop so do you know what mysql settings I should change?

SET GLOBAL log_bin_trust_function_creators = 1;

thanks.  I did not get to try your command because
SET GLOBAL log_bin_trust_function_creators = 1;
worked.