MySQL Bugs: #45827: Stmt using two autoinc values does not produce unsafe warning

Bug #45827	Stmt using two autoinc values does not produce unsafe warning
Submitted:	29 Jun 2009 15:13	Modified:	8 Mar 2010 0:14
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:	Daogang Qu	CPU Architecture:	Any
Tags:	autoincrement, unsafe, warning

Description:
Statements may be marked "unsafe", meaning that writing them to the binlog in statement format may cause the slave to diverge. If binlog_format=MIXED, then unsafe statements are logged in row format. If binlog_format=STATEMENT, then unsafe statements produce a warning.

One type of unsafe statement is statements that use two autoincrement values. Currently, however, such statements only cause the statement to be logged in row format if binlog_format=MIXED. They do not cause a warning to be generated if binlog_format=STATEMENT.

How to repeat:
source include/have_binlog_format_statement.inc;

CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);

# The purpose of this function is to insert into t1 so that the second
# column is auto_increment'ed.
DELIMITER |;
CREATE FUNCTION func_modify_t1 ()
RETURNS INT
BEGIN
  INSERT INTO t1 SET a = 1;
  RETURN 0;
END|
DELIMITER ;|

# The following statement causes auto-incrementation of both t1 and
# t2.  It is logged in statement format, but does not produce a
# warning.
INSERT INTO t2 SET a = func_modify_t1();

SET SESSION binlog_format = MIXED;
# Logged in row format.
INSERT INTO t2 SET a = func_modify_t1();

source include/show_binlog_events.inc;

exit;

Suggested fix:
Unsafe statements should be detected before warnings are issued. The solution is therefore to integrate has_two_write_locked_tables_with_auto_increment into decide_logging_format.

This can be fixed after BUG#39934 is fixed.

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

3605 Dao-Gang.Qu@sun.com	2009-09-16
      Bug #45827  Stmt using two autoinc values does not produce unsafe warning
      
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
      
      To fix the problem, mark the statement as unsafe in statement and row mode too.
     @ mysql-test/suite/rpl/r/rpl_unsafe_warning.result
        Test result for bug#45827
     @ mysql-test/suite/rpl/t/rpl_unsafe_warning.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ sql/sql_base.cc
        Get rid of the check of BINLOG_FORMAT_MIXED

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

3605 Dao-Gang.Qu@sun.com	2009-09-16
      Bug #45827  Stmt using two autoinc values does not produce unsafe warning
      
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
      
      To fix the problem, mark the statement as unsafe in statement and row mode too.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Test result for bug#45827
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ sql/sql_base.cc
        Get rid of the check of BINLOG_FORMAT_MIXED

The sr51mru tag should be replaced by a 5.4 tag, because the bug fixing will bring lots of unsafe warnings, and these unsafe warnings just may be suppressed 
due to new 'unsafe warning suppression' feature is introduced on 5.4.

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

3605 Dao-Gang.Qu@sun.com	2009-09-22
      Bug #45827  Stmt using two autoinc values does not produce unsafe warning
      
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
      
      To fix the problem, mark the statement as unsafe in statement mode too.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Test result for bug#45827
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ sql/sql_base.cc
        Reomved the 'set_current_stmt_binlog_row_based_if_mixed' function 
        for producing unsafe warnings by executing 'decide_logging_format' 
        function later in statement mode.

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

3605 Dao-Gang.Qu@sun.com	2009-09-27
      Bug #45827  Stmt using two autoinc values does not produce unsafe warning
      
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
      
      To fix the problem, mark the statement as unsafe in statement mode too.
     @ mysql-test/extra/rpl_tests/rpl_insert_id.test
        The test case is updated due to a patch of bug#45827.
     @ mysql-test/r/merge_recover.result
        The test result is updated due to a patch of bug#45827.
     @ mysql-test/suite/backup_ptr/t/backup_ptr_objects.test
        The test case is updated due to a patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        The test result is updated due to a patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Test result for bug#45827
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ mysql-test/suite/rpl/t/rpl_trigger.test
        The test case is updated due to a patch of bug#45827.
     @ sql/sql_base.cc
        Reomved the 'set_current_stmt_binlog_row_based_if_mixed' function 
        for producing unsafe warnings by executing 'decide_logging_format' 
        function later in statement mode.

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

3623 Dao-Gang.Qu@sun.com	2009-09-28
      Bug #45827  Stmt using two autoinc values does not produce unsafe warning
      
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
      
      To fix the problem, mark the statement as unsafe in statement mode too.
     @ mysql-test/extra/rpl_tests/rpl_insert_id.test
        The test case is updated due to a patch of bug#45827.
     @ mysql-test/r/merge_recover.result
        The test result is updated due to a patch of bug#45827.
     @ mysql-test/suite/backup_ptr/t/backup_ptr_objects.test
        The test case is updated due to a patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        The test result is updated due to a patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Test result for bug#45827
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ mysql-test/suite/rpl/t/rpl_trigger.test
        The test case is updated due to a patch of bug#45827.
     @ sql/sql_base.cc
        Reomved the 'set_current_stmt_binlog_row_based_if_mixed' function 
        for producing unsafe warnings by executing 'decide_logging_format' 
        function later in statement mode.

It is pushed to mysql-pe.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)

Documented bugfix in the 6.0.14 changelog as follows:

        Statements that updated AUTO_INCREMENT columns in multiple
        tables were logged using the row-based format when
        --binlog_format was set to MIXED, but did not cause a warning to
        be generated when --binlog_format was set to STATEMENT.

Set status to NDI, waiting for additional merges. (Will this be pushed to additional trees? If so, which ones? If this won't be pushed to additional trees, it can simply be closed.)

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

3140 Dao-Gang.Qu@sun.com	2009-11-13
      Backport Bug #45827  Stmt using two autoinc values does not produce unsafe warning
            
      One statement that have more than one different tables to update with 
      autoinc columns just was marked as unsafe in mixed mode, so the unsafe 
      warning can't be produced in statement mode.
            
      To fix the problem, mark the statement as unsafe in statement mode too.
     @ mysql-test/extra/rpl_tests/rpl_insert_id.test
        The test case is updated due to the patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        The test result is updated due to the patch of bug#45827.
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Test result for bug#45827.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Added test to verify if stmt that have more than one
        different tables to update with autoinc columns will 
        produce unsafe warning
     @ mysql-test/suite/rpl/r/rpl_stm_auto_increment_bug33029.result
        The test result is updated due to the patch of bug#45827.
     @ mysql-test/suite/rpl/t/rpl_trigger.test
        The test case is updated due to the patch of bug#45827.
     @ mysql-test/suite/rpl/t/rpl_variables_stm.test
        The test case is updated due to the patch of bug#45827.
     @ sql/sql_base.cc
        Reomved the 'set_current_stmt_binlog_row_based_if_mixed' function 
        for producing unsafe warnings by executing 'decide_logging_format' 
        function later in statement mode

Pushed into 6.0.14-alpha (revid:aelkin@mysql.com-20091202145207-zjr6kdpwm5z5jj2z) (version source revid:aelkin@mysql.com-20091202145207-zjr6kdpwm5z5jj2z) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:aelkin@mysql.com-20091201190718-ls6a6i82bs4vovf9) (version source revid:aelkin@mysql.com-20091201190718-ls6a6i82bs4vovf9) (merge vers: 5.6.0-beta) (pib:13)

Also documented in the 5.6.0 changelog; re-closed.

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

3126 Sven Sandberg	2009-12-14
      Post-push fixes after wrong merge from 5.1->rep+2->rep+3.
      Problem: The test was written before BUG#45827 was fixed.
      The test contained code that assumed the wrong behavior,
      pre-BUG#45827. Then, the fix for BUG#45827 was merged
      from 5.1-rep+2 to 5.1-rep+3. Since the test case assumed
      the wrong behavior, it failed. This should have been fixed
      by making the test assume the correct behavior, but was
      fixed by updating the result file to assert failure.
      Fix 1: fix the test to assume correct behavior
      (post-BUG#45827), update result file.
      Fix 2: make test fail with 'die' instead of 'exit' when
      wrong behavior is detected. Thus, the test cannot be
      silenced with a wrong result file in case the behavior
      will change again.
     @ mysql-test/extra/rpl_tests/create_recursive_construct.inc
        Replaced 'exit' by 'die' to avoid similar
     @ mysql-test/suite/binlog/r/binlog_unsafe.result
        Updated result file.
     @ mysql-test/suite/binlog/t/binlog_unsafe.test
        Since BUG#45827 is now fixed, we need to update the test case
        in two places where it expects the wrong result because of
        BUG#45827.

The above patch is not for this bug, it just contains a changeset comment referring to this bug.

Pushed into 6.0.14-alpha (revid:alik@ibmvm-20100118120357-hnzhgadkpzqfnvsc) (version source revid:alik@ibmvm-20100118115413-kd3klpine09yyktw) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100118120111-73dulkgc893it4r9) (version source revid:alik@ibmvm-20100118115335-0stecyzftqm7bqx6) (pib:16)

Already documented, see previous comments.

Set back to Closed state.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@linux-rbsx-20100118220048-5vnyqi5ghsbgmdsd) (merge vers: 5.5.99-m3) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.