The problem is that there was a native function distance()
added in MySQL 5.7.5, which takes 2 arguments:
Distance() was added in MySQL 5.7.5.
Distance() is deprecated as of MySQL 5.7.6 and will be removed in a future MySQL release. Use ST_Distance() instead.
It will be removed in a later release, but unfortunately you would need to rename your function. Had you not dropped it, you could have changed the function call in the app to include the schema:
The preceding function name resolution rules have implications for upgrading to versions of MySQL that implement new built-in functions:
If you have already created a user-defined function with a given name and upgrade MySQL to a version that implements a new built-in function with the same name, the UDF becomes inaccessible. To correct this, use DROP FUNCTION to drop the UDF and CREATE FUNCTION to re-create the UDF with a different nonconflicting name. Then modify any affected code to use the new name.
If a new version of MySQL implements a built-in function with the same name as an existing stored function, you have two choices: Rename the stored function to use a nonconflicting name, or change calls to the function so that they use a schema qualifier (that is, use schema_name.func_name() syntax). In either case, modify any affected code accordingly.
[source]
This sounds like all your .ibd
file have been detached. Why ?
Please look at the InnoDB Architecture from Percona CTO Vadim Tkachenko
ibdata1
has an internal data dictionary that registers every .ibd
by a unique tablespace_id. Somewhere along the line, all the tables in the trouble database have become detach from the data dictionary.
I wrote about this years ago
To rightfully give credit where credit is due, I learned about this concept of detached .ibd
files and trying to connect them back to the data dictionary from a blogpost written by Chris Calendar back in Feb 2009. I once helped client recover 30 tables with the information from his post (See my post ERROR Cannot find or open table? on what I did to help that client). Please read his biog and then my posts on what you need to try.
As for the why the table would be come detached, here is an example
- Your datadir is
/var/lib/mysql
- Your have a database called
mydb
- You have a table called
mytable
If you create an InnoDB table called mytable
in the mydb
database
- You get two files
/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd
.ibd
file would be assigned a tablespace_id.
If you drop that table and create it again, the table would have a different tablespace_id.
Now, picture this:
- You make a backup of the database folder
/var/lib/mysql/mydb
with the tablespace_id of 27 on that one table
- You drop the table and create it (TRUNCATE TABLE internally does the same thing), ending up with tablespace_id of 28
- You restore the backup of the database folder
/var/lib/mysql/mydb
with the tablespace_id of 27 on that one table
This causes the detachment because table mydb.mytable
has the old tablespace_id of 27 while the data dictionary within ibdata1
has 28.
Since you said you didn't move or copy anything, then you may have too many InnoDB tables. Each InnoDB table needs two file handles to be opened and accessed.
I hope I have given your the information you need to help you recover your database.
Best Answer
ST_Distance_Sphere()
is special optimization on a sphere. If you want to calculate using an spheroid just useST_Distance().
From the docs onST_Distance()
An ellipsoid is a spheroid where two axes have the same length.