Friday, December 30, 2005

function WELL_KNOWN_FUNC does not exist

That came today in a newsgroup. A user was complaining that he was able to encrypt and decrypt some data with an early release of MySQL 5.0 and not being able to decrypt it with the latest one.
The message he received was, allegedly
function AES_DECRYPT does not exist
That puzzled me, of course. I have the latest MySQL version installed in one of my servers, and I tried it straight away:

select version(), AES_DECRYPT(AES_ENCRYPT('abc','y'),'y');
+----------------+-----------------------------------------+
| version() | AES_DECRYPT(AES_ENCRYPT('abc','y'),'y') |
+----------------+-----------------------------------------+
| 5.0.17-max-log | abc |
+----------------+-----------------------------------------+

So the function does exist. But what was the poster complaining about? Perhaps he did not report the error message correctly.
Let's try to put a space between the function name and the parenthesis.

select version(), AES_DECRYPT (AES_ENCRYPT('abc','y'),'y');
ERROR 1305 (42000): FUNCTION test.AES_DECRYPT does not exist

Aha! That was the problem. The message was missing one piece that would have given away the answer immediately.
The problem was that he was calling the function with a space between name and parenthesis and the manual tells you how it will accepted by the interpteter.
If you are used to spaces in front of parentheses and you'd like the interpreter to make some additional effort before reporting failure, you can always use the sql mode to inform the server of your preferences. Either start the server with the option --sql-mode=IGNORE_SPACE, or change it run-time:

set sql_mode='IGNORE_SPACE';

# or if you have the SUPER privilege:
set global sql_mode='IGNORE_SPACE';

select version(), AES_DECRYPT (AES_ENCRYPT('abc','y'),'y');
+----------------+------------------------------------------+
| version() | AES_DECRYPT (AES_ENCRYPT('abc','y'),'y') |
+----------------+------------------------------------------+
| 5.0.17-max-log | abc |
+----------------+------------------------------------------+

2 comments:

Tocke-San said...

I've encountered the same behaivour on a WIN2K and from my tests it seems to be a problem with the phpmyadmin and the formatting of queries.

If I copy and paste the query into the commandline client the query is run smothly but in phpmyadmin it somehow picks up a space and outputs:
function databasename.AES_DECRYPT() does not exist

Funmarkaz said...

I was facing the same problem in using 'MAX'. then i removed the space between func name and the parenthesis and i got it solved.

Vote on Planet MySQL