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

from oop php tutorial
I do not yet understand the .sql file so I copy paste the query and the message

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

Open in new window



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)

Open in new window

LVL 1
rgb192Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Julian HansenConnect With a Mentor Commented:
So then it has nothing to do with your SQL Workbench and is instead some setting on the server.

Maybe this article is of use http://www.jamediasolutions.com/blog/deterministic-no-sql-or-reads-sql-data-in-its-declaration.html

Failing that have you tried

DELIMITER $$ CREATE FUNCTION return_distance(

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

Open in new window

0
 
Julian HansenCommented:
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;

Open in new window

0
 
rgb192Author Commented:
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)
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Julian HansenCommented:
Weird - works here - what version of MySQL are you on?
0
 
rgb192Author Commented:
MySQL Version :
5.5.24
0
 
rgb192Author Commented:
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
0
 
Julian HansenCommented:
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 ?
0
 
rgb192Author Commented:
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?
0
 
rgb192Author Commented:
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.
0
All Courses

From novice to tech pro — start learning today.