MySQL – Incorrect Parameter Count for Function Error

MySQLmysql-5.5mysql-5.7

I'm migrating my webapp from local to DigitalOcean, and I'm having a problem using a routine I've created. It works fine locally(version 5.5.50) but on the online MySQL(version 5.7.13) it gives me the following error:

#1582 – Incorrect parameter count in the call to native function 'DISTANCE'

even tho the call is right.

I have deleted the function and it still gives me the same error, therefore I think it's not even being able to find my function.

I've tried to grant all privileges to my user and it's still not working.

Best Answer

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]

OPTION #1 : Change old_password Protocol for EC2

Stop replication to the RDS Slave
As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0;
Start replication to the RDS Slave

OPTION #2 : Lower the Security for EC2

Stop replication to the RDS Slave
As root@localhost run this : SET GLOBAL secure_auth = OFF;
Start replication to the RDS Slave

For more information, please read the old_passwords and secure_auth MySQL Documentation.

Give it a Try !!!

UPDATE 2017-07-20 16:32 EDT

Neither of these methods work for MySQL 5.7 as both options are deprecated.

MySQL – Using ‘system pwd’ Command with CONCAT() Function

system is not a recognized MySQL server command. This will only work on the MySQL command line interface "mysql". With a command like this:

mysql> system pwd
/tmp

you will get the client's current directory, nothing to do with the server. You cannot use that information on server side, for executing queries. If you want to concat the pwd with something else, use a script at client side (bash, php). For example:

$ echo $(pwd)test
/tmptest
$ mysql  -e "SELECT concat('$(pwd)', 'test')" # there is not reason to do this at all
+------------------------+
| concat('/tmp', 'test') |
+------------------------+
| /tmptest               |
+------------------------+

While there is a custom non-standard UDF library to make MySQL server interact with the operating system, I do not see a valid use in your case.

If you want to know the directory where MySQL is installed, where the datafiles are, etc., you can execute:

mysql> SHOW GLOBAL VARIABLES like '%dir';
+---------------------------+--------------------------+
| Variable_name             | Value                    |
+---------------------------+--------------------------+
| basedir                   | /usr/local/mysql         |
| datadir                   | /usr/local/mysql/data/   |
...