FUNCTION ATAN2 does not exist

[michaellunsford]

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?

[fenway]

How are you calling this? ATAN2() is an internal function....

[michaellunsford]

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

[fenway]

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.

[michaellunsford]

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?

[fenway]

I mean that maybe someone has made a UDF that's broken.  That error message you're getting is funny.

[michaellunsford]

how can I do a function dump?

[fenway]

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....

[michaellunsford]

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.

[fenway]

What about the second thought... about the spaces?

[michaellunsford]

Yeah, just checked that. no spaces. sorry for the delayed response - it's been a really long day.

[fenway]

Can you post the sql-mode of each server?

[michaellunsford]

You're going to have to tell me how to do that. Is it in a table?

[fenway]

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;

[michaellunsford]

the sql query simply returns a blank record. Neither server has a "sql-mode" setting in the my.cnf.

[fenway]

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?

[michaellunsford]

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.

[fenway]

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.