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 15:00	Modified:	6 May 2007 5:08
Reporter:	Guilhem Bichot
Status:	Closed
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:	Linux (linux)
Assigned to:	Chuck Bell	Target Version:

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.