MySQL Bugs: #17671: UDF: CREATE FUNCTION binlogging problems (stmt-based and row-based)

Bug #17671	UDF: CREATE FUNCTION binlogging problems (stmt-based and row-based)
Submitted:	23 Feb 2006 14:00	Modified:	6 May 2007 3:08
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:	Linux (linux)
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
If you follow the steps to set up a sample UDF, at
http://dev.mysql.com/doc/refman/5.0/en/udf-compiling.html
and execute on the master:
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
then
1) in statement-based binlogging mode, this statement is not written to the binary log
so if later on the master one does INSERT INTO t SELECT metaphon("hi") then slave will complain.
2) in row-based binlogging mode, the modifications does by the statement to mysql.func are binlogged, but this is not enough for the UDF to "exist" on the slave as the slave needs to do some "re-loading" (like when RBR slave inserts into mysql.user it calls acl_reload()); so currently if on the slave one does SELECT metaphon("hi") it will fail.

How to repeat:
see description

Suggestion how to handle CREATE FUNCTION (for UDF's)
- Log it to the binary log on master
- On slave, execute it and generate warning if CREATE FUNCTION doesn't work, but don't stop replication. Reason for this is:
-- The UDF may never need to exist on the slave if the master uses row based logging
-- The UDF may never be used for the databases that is logged.
-- The UDF function may be incompiled into the slave.

If there later would be a replication statement that used the not created function on the slave, then we should generate a fatal error and stop.

The above should be resonable safe as we will detect any errors that would lead to data corruption.

Behavior wise, we must have a standard behavior for all plugin types (so what we decide here, must also hold for LOAD MODULE).

One other note, CREATE FUNCTION should always be replicated based on statement, its tables should never be replicated via RBR.

The requested functionality implemented in the patch for WL#3629

Patch for this bug included in WL#3629. Patch pushed 29 Mar 2007.

Pushed into 5.1.18-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented fix in 5.1.18 changelog. Documented associated feature changes in Events, INFORMATION_SCHEMA, and Replication chapters of 5.1 Manual.