Bug #60791 CREATE FUNCTION fails after binary restore as mysql thinks function is installed
Submitted: 7 Apr 2011 10:54 Modified: 29 Mar 2012 19:26
Reporter: Kai Noetzel Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:Server version: 5.1.41-3ubuntu12.10+gf20 OS:Linux (Ubuntu Lucid 64-bit)
Assigned to: CPU Architecture:Any

[7 Apr 2011 10:54] Kai Noetzel
Description:
I had murmur_hash installed as UDF used for maatkits mk-checksum-table.
The function is available on the origin server (master).

After restoring a binary backup to a different server (slave) I wanted to install the same UDF again to be able to use mk-checksum-table again.

When trying to install the UDF MySQL tells me:

mysql mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'murmur_udf.so'"
ERROR 1125 (HY000) at line 1: Function 'murmur_hash' already exists

When trying to use the function on the server it tells me:

SELECT MURMUR_HASH('hello', 'world');
ERROR 1305 (42000): FUNCTION MURMUR_HASH does not exist

I've seen there has been an issue 5.0.0-alpha http://bugs.mysql.com/bug.php?id=2485 maybe it's related.

How to repeat:
Install UDF on origin server. Do binary backup, restore binary backup on different server. Try to install UDF again.
[7 Apr 2011 11:44] Valeriy Kravchuk
If you restored binary backup, why do you need to create function again? All you need is to put .so with implementation into proper place...
[7 Apr 2011 12:04] Kai Noetzel
That's what I thought first and tried as well. The murmur_udf.so is placed in /usr/lib/mysql/plugin as it should be, but as said if I try to use it mysql is telling me it doesn't exist.

SELECT MURMUR_HASH('hello', 'world');
ERROR 1305 (42000): FUNCTION MURMUR_HASH does not exist
[7 Apr 2011 12:29] Kai Noetzel
Nevermind, I found a work around. I have to delete it first from the 'func' table in the MySQL DB where it was still listed.

After a restart of the MySQL server I was able to install the UDF again and then I was able to use it as well.

Still I'm not sure if this is working as intended as the entry in the 'func' table is still the same and the place where the murmur_udf.so was placed has not changed. Anyone knows what is different?
[29 Feb 2012 19:26] Sveta Smirnova
Thank you for the feedback.

Is still interesting if this issue is repeatable. How did you do binary backup? Is it repeatable with current version 5.1.61?
[1 Apr 2012 11:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".