michaellunsford Posted May 17, 2009 Share Posted May 17, 2009 I'm doing some research on geocoding and found a mysql script that appears to work fine on my test server, but not on my live server. The test server has mysql 5.0.67, and my live server has 5.0.66a - but I'm thinking this isn't the problem. Nutshell, the query calls a function ATAN2, which returns the error "FUNCTION my_table.ATAN2 does not exist" on my live server, but returns records on my test server. I copied and pasted the same query to both, so I believe I've eliminated query syntax as the problem. Note, I did have to run mysql_fix_privilege_tables on the database recently. The database was and is working fine for everything else I'm doing, but this query revealed some underlying issues. Perhaps I need to run another kind of fix on it as well? Thoughts? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 How are you calling this? ATAN2() is an internal function.... Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 19, 2009 Author Share Posted May 19, 2009 It's for a geo radius search. SELECT `id`, X(gm_coor) AS `latitude`, Y(gm_coor) AS `longitude`, ATAN2( SQRT( POW(COS(RADIANS(30.311325)) * SIN(RADIANS(Y(gm_coor) - -91.909917)), 2) + POW(COS(RADIANS(X(gm_coor))) * SIN(RADIANS(30.311325)) - SIN(RADIANS(X(gm_coor))) * COS(RADIANS(30.311325)) * COS(RADIANS(Y(gm_coor) - -91.909917)), 2)), (SIN(RADIANS(X(gm_coor))) * SIN(RADIANS(30.311325)) + COS(RADIANS(X(gm_coor))) * COS(RADIANS(30.311325)) * COS(RADIANS(Y(gm_coor) - -91.909917))) ) * 6372.795 AS `distance` FROM `my_table` HAVING `distance` < 1 Quote Link to comment Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 What if you just try a simple ATAN2() call? Like the one from the refman? Also, please dump the existing list of function that you've created as mysql 5 objects. Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 19, 2009 Author Share Posted May 19, 2009 Interesting, SELECT ATAN2(PI(),0); (from the refman) returns a value. That query does work on another server just fine, though. Hmmm, what does that mean? I'm not quite sure what you're referring to - dump the existing list of functions that [i've] created. Can you elaborate? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 I mean that maybe someone has made a UDF that's broken. That error message you're getting is funny. Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 19, 2009 Author Share Posted May 19, 2009 how can I do a function dump? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 I'm fairly sure the information_schema has a "routines" table.... EDIT: wait, are you sure you didn't have a space between the function name and the open paren? There's an sql mode that buggers that up.... Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 19, 2009 Author Share Posted May 19, 2009 the information_schema does have a routines table, however, there are no records in it. in fact, none of the tables have any records in them. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 19, 2009 Share Posted May 19, 2009 What about the second thought... about the spaces? Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 20, 2009 Author Share Posted May 20, 2009 Yeah, just checked that. no spaces. sorry for the delayed response - it's been a really long day. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 20, 2009 Share Posted May 20, 2009 Can you post the sql-mode of each server? Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 20, 2009 Author Share Posted May 20, 2009 You're going to have to tell me how to do that. Is it in a table? Quote Link to comment Share on other sites More sharing options...
fenway Posted May 20, 2009 Share Posted May 20, 2009 You're going to have to tell me how to do that. Is it in a table? No, it's in the my.cnf / my.ini file, if it's set. Altenatively, you can simply SELECT @@GLOBAL.sql_mode; Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 20, 2009 Author Share Posted May 20, 2009 the sql query simply returns a blank record. Neither server has a "sql-mode" setting in the my.cnf. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 20, 2009 Share Posted May 20, 2009 Hmm.. I've only ever encountered this when there's a whitespace character hiding somewhere... can you try settting sql_mode = IGNORE_SPACE just to prove me wrong? Quote Link to comment Share on other sites More sharing options...
michaellunsford Posted May 20, 2009 Author Share Posted May 20, 2009 I added "sql_mode = IGNORE_SPACE" to the my.cnf and restarted mysql, but the query "SELECT @@GLOBAL.sql_mode;" still coming back with a blank record. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 22, 2009 Share Posted May 22, 2009 I added "sql_mode = IGNORE_SPACE" to the my.cnf and restarted mysql, but the query "SELECT @@GLOBAL.sql_mode;" still coming back with a blank record. That's odd. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.