MySQL Bugs: #54702: Different innodb_strict_mode on master and slave causes replication failure

Bug #54702	Different innodb_strict_mode on master and slave causes replication failure
Submitted:	22 Jun 2010 18:29	Modified:	9 Dec 2010 22:28
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.5-m3, 5.1.48	OS:	Any
Assigned to:	Calvin Sun	CPU Architecture:	Any

Description:
If innodb_strict_mode=OFF on master and =ON on slave, any query which causes related warnings on master makes replication fail.

While innodb_strict_mode is declared in the manual to be analogous to strict SQL mode, different values of sql_mode on master and slave do not cause a failure, as session sql_mode is written in the binary log, while innodb_strict_mode is not.

It relates to all versions of InnoDB plugin 1.x that I tried; but now, since in 5.5.5 the default innodb_strict_mode value changed from OFF to ON, it might cause more problems than before.

How to repeat:
# On servers before 5.5.5 run as
# perl ./mysql-test-run.pl --mysqld=--innodb_strict_mode=1 <test name>
# (on 5.5.5 innodb_strict_mode=1 by default)

--source include/master-slave.inc
--source include/have_innodb.inc

--connection master

SET innodb_strict_mode=0;
CREATE TABLE t ( i INT )
   ROW_FORMAT=COMPACT KEY_BLOCK_SIZE=8 ENGINE=InnoDB;

--sync_slave_with_master

Suggested fix:
Write session innodb_strict_mode into binary log along with sql_mode. 
If not possible (and there is no other way to make it foolproof), document as a note/limitation.

I would classify this as a replication bug. The state of the innodb_strict_mode should be shipped to the slave.

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/113475

3477 Andrei Elkin	2010-07-13
      Bug #54702  	Different innodb_strict_mode on master and slave causes replication failure
      
      The bug is due to a lack of query execution context info on the slave side.
      
      The current patch probs a pattern solution that can be elaborated on to cover the ultimate
      general issues of having the complete execution context for a Query (wl#5135).
      While this pattern solution tecnically fixes the imediate issue of the bug, it still should not be viewed
      as a bug-fix. It rather offers
      
      1. to elaborate on plugin interface of the plugin system variables to
         define accessors that aware of bilogging and setters from the server code (esp. replication);
      2. to classify all system variables and associate each with an unique binlog
         identifier that maps to a certain style of intercepting the var value and following 
         binlogging (e.g as  a bit of a bitmap, string etc).
         For instance boolean system vars that gover engine modes (such as innodb_strict_mode)
         can form their own subclass having a one bitmap status var of Query_log_event.
      3. to organize read access to system var:s as plugins' so the server's through well-defined 
         interface (see THDVAR3 and related in the patch) that makes sure an access 
         is memorized for binlogging.
     @ include/mysql/plugin.h
        an example of the accesser macro that expands into calling a function
        carrying a binlog/replication unique identifier for the variable that
        is being accessed (read).
     @ mysql-test/suite/rpl/r/rpl_bug54702_innodb_strict_mode.result
        new results file is added.
     @ mysql-test/suite/rpl/t/rpl_bug54702_innodb_strict_mode.test
        a regression test for the bug is added.
     @ sql/log_event.cc
        master side prepares Query event with a new Q_ENGINE_MODE_CODE status.
        slave side applies the status in assumption of the only one 
        ENGINE_MODE_INNODB_STRICT value of the 64-bits is meaningful.
     @ sql/log_event.h
        Extending the status vars of Query_log_event.
     @ sql/mysql_priv.h
        Allocating a global visible to mysql and engines enum engine_mode_codes declaration in their common header file;
     @ sql/sql_class.cc
        initialization with defaults for engine_mode_inited[] arrays.
     @ sql/sql_class.h
        adding uint64 engine_mode_inited[2] to serve as described in comments.
     @ sql/sql_plugin.cc
        an example of plugin var accessor function that requires the 3rd arg and
        performs a necessary action depending on the value of the 3rd arg.
        A pattern is added to show how to change initialization of a plugin vars if
        a variable requires its read-out value binlogging.
        
        An example value setter function to a plugin variable is added.
     @ sql/sql_plugin.h
        Adding the value setter function to a plug var that can be adapted by slave execution code.
     @ storage/innodb_plugin/handler/ha_innodb.cc
        Modifying declaration and accessor macro for strict_mode innodb_plugin var.
        The new declaration for the var makes sure the var will be associated with an accessor function
        which stores the var's value for eventual embedding into status variable of the replication Query
        event.

The committed patch somehow intervenes into plugin interface. While that idea seems to be only viable approach it should not target at just this specific
use case. Therefore I suggest to follow up with the patch within wl#5135 framework. We might split a sibling WL from that to cover explicitly the engine mode subcase of the whole issue.

Use this one to track change in 5.5 (change the default of innodb_strict_mode to false); replication team agrees to open another one for post-5.5 solution.

Reported BUG#55727 to track the real issue

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)