mysqlmysql-udf

What happens when MySQL adds a function with the same name as your own?

I use sha3 hashes in php (7.2) and I was going to continue using them in MySQL, but MySQL doesn't have support for them yet.

If I write my own UDF and call it, say, sha3, but then some future MySQL version adds a native sha3 function, what happens?

I'll probably name it something else anyway to avoid the possible conflict, but this could happen with any function eventually, so I'm certainly curious.

Just in case anyone is looking for sha3 support in MySQL, I have written a MySQL UDF that does exactly that using rhash that intends to function exactly like the native sha2 function, which you can download here (installation and usage instructions are in the comments) https://gist.github.com/BrianLeishman/a0f40e7a0a87a7069c5c56a768ff3179

Also, it's worth noting that sha3 is supposed to be faster than sha2 (I think), but my function is 4x as slow as native sha2 (when generating 100,000 hashes), but hopefully the future native sha3 will solve that issue.

I've added a separate UDF for returning the hashes without hex encoding called unhex_sha3, which is should literally act as unhex(sha3(..., and this version is almost exactly native speed (compared to sha2), since I can avoid the pointless translation between hex encoding and back.

https://gist.github.com/BrianLeishman/d7903a4acba75707c05fc581e1c714c3

Solution

  • From function name resolution guideline:

    Avoid creating UDFs or stored functions that have the same name as a built-in function. ...

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