Bug #48183 statements using fulltext parser plugins should be marked unsafe
Submitted: 20 Oct 2009 13:36 Modified: 10 Jan 2013 9:30
Reporter: Sven Sandberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1+ OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[20 Oct 2009 13:36] Sven Sandberg
Description:
Statements that cannot safely be logged in statement format are marked unsafe. This causes the statement to be logged in row format if binlog_format=mixed and causes a warning to be generated if binlog_format=statement.

Any use of plugins that could affect rows in a table should be marked unsafe because it cannot be determined that the statement will have the same effect on the slave. For example, UDFs are currently marked unsafe.

However, use of fulltext parser plugins is currently not marked as unsafe. This can cause the slave to go out of sync if the plugged-in fulltext parser does not behave consistently on master and slave and the user executes statements on master like:

INSTALL PLUGIN my_parser SONAME 'mypluglib.so';
CREATE TABLE t1 (a CHAR(255), FULLTEXT INDEX (a) WITH PARSER my_parser);
CREATE TABLE t2 (a CHAR(255));
INSERT INTO t2 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('text');

How to repeat:
==== binlog_fulltext.test ====
--source include/have_simple_parser.inc
--source include/have_binlog_format_mixed.inc
--source include/master-slave.inc

INSTALL PLUGIN simple_parser SONAME 'mypluglib.so';
CREATE TABLE t1 (a TEXT, FULLTEXT(a) WITH PARSER simple_parser);
CREATE TABLE t2 (a TEXT);

INSERT INTO t1 VALUES ('test');
INSERT INTO t2 SELECT * FROM t1 WHERE MATCH (a) AGAINST ('test');

# INSERT..SELECT should be logged as row but isn't
SHOW BINLOG EVENTS;

DROP TABLE t1, t2;
UNINSTALL PLUGIN simple_parser;

==== binlog_fulltext-master.opt ====
$SIMPLE_PARSER_OPT

Suggested fix:
Mark statements using fulltext plugin as unsafe.
Note: this must be done before decide_logging_format is called.
[23 Nov 2010 9:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124708

3350 Li-Bing.Song@sun.com	2010-11-23
      Bug#48183 statements using fulltext parser plugins should be marked unsafe
      
      Any use of plugins that could affect rows in a table should be marked unsafe
      because it cannot be determined that the statement will have the same effect
      on the slave. For example, UDFs are currently marked unsafe.  However, use of
      fulltext parser plugins is currently not marked as unsafe.
      
      After this patch, any statement which uses fulltext parser plugins is marked
      unsafe.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning-master.opt
        Add SIMPLE_PARSER_OPT option.
     @ mysql-test/suite/binlog/t/binlog_stm_unsafe_warning.test
        Add test for this bug.
     @ sql/share/errmsg-utf8.txt
        Add ER_BINLOG_UNSAFE_PLUGIN.
     @ sql/sql_base.cc
        Mark current statement is unsafe when setup fulltext parsers.
     @ sql/sql_class.h
        Add auxiliary function to set binlog_unsafe_warning_flags.
     @ sql/sql_lex.cc
        Initilaize unsafe plugin error message.
     @ sql/sql_lex.h
        Add unsafe type BINLOG_STMT_UNSAFE_PLUGIN.
[10 Jan 2013 9:30] Jon Stephens
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
[10 Jan 2013 9:30] Jon Stephens
Fixed in 5.7, documented as follows in the 5.7.1 changelog:

        Because the behavior of the fulltext plugin may vary between
        MySQL servers, it is not possible to guarantee that statements
        using this plugin produce the same results on masters and
        slaves. For this reason, statements depending on the fulltext
        plugin are now marked as unsafe for statement-based logging.
        This means that such statements are logged using row format when
        binlog_format=MIXED, and cause a warning to be generated when
        binlog_format=STATEMENT.

Also noted the change in "Determination of Safe & Unsafe Statements" in the 5.7 Manual.

Closed.