• unknown's avatar
    BUG#34768 - nondeterministic INSERT using LIMIT logged in stmt mode if · 1626b42c
    unknown authored
                binlog_format=mixed
    
    Statement-based replication of DELETE ... LIMIT, UPDATE ... LIMIT,
    INSERT ... SELECT ... LIMIT is not safe as order of rows is not
    defined.
    
    With this fix, we issue a warning that this statement is not safe to
    replicate in statement mode, or go to row-based mode in mixed mode.
    
    Note that we may consider a statement as safe if ORDER BY primary_key
    is present. However it may confuse users to see very similiar statements
    replicated differently.
    
    Note 2: regular UPDATE statement (w/o LIMIT) is unsafe as well, but
    this patch doesn't address this issue. See comment from Kristian
    posted 18 Mar 10:55.
    
    
    mysql-test/suite/binlog/r/binlog_stm_ps.result:
      Updated a test case according to fix for BUG#34768:
      INSERT ... SELECT ... LIMIT is now replicated in row mode.
    mysql-test/suite/binlog/r/binlog_unsafe.result:
      A test case for BUG#34768.
    mysql-test/suite/binlog/t/binlog_unsafe.test:
      A test case for BUG#34768.
    sql/sql_delete.cc:
      Statement-based replication of DELETE ... LIMIT is not safe as order of
      rows is not defined, so in mixed mode we go to row-based.
    sql/sql_insert.cc:
      Statement-based replication of INSERT ... SELECT ... LIMIT is not safe
      as order of rows is not defined, so in mixed mode we go to row-based.
    sql/sql_update.cc:
      Statement-based replication of UPDATE ... LIMIT is not safe as order of
      rows is not defined, so in mixed mode we go to row-based.
    1626b42c
binlog_unsafe.result 1.35 KB