MySQL Bugs: #49019: Mixing self-logging eng. and regular eng. does not switch to row in mixed mode

Bug #49019	Mixing self-logging eng. and regular eng. does not switch to row in mixed mode
Submitted:	24 Nov 2009 2:11	Modified:	14 Oct 2010 12:25
Reporter:	Alfranio Tavares Correia Junior	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1+, 5.5.99-m3	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	mixing engines, replication, row

Description:
Reading from a self-logging engine and updating a Innodb engine generates changes that are written to the binary log in the statement format and may make slaves diverge. In the mixed mode, such changes should be written to the binary log in the row format.

Note that the issue does not happen if we mix a self-logging engine and MyIsam
as this case is caught by checking the mixture of non-transactional and transactional engines.

How to repeat:
---- TEST CASE ----
--source include/have_ndb.inc
--source include/have_binlog_format_mixed_or_row.inc
--source include/ndb_master-slave.inc
--source include/have_innodb.inc

CREATE TABLE ndb_t (a int)  ENGINE = NDB;
CREATE TABLE ino_t (a int)  ENGINE = Innodb;

BEGIN;
INSERT INTO ndb_t VALUES(1);
INSERT INTO ndb_t VALUES(2);
INSERT INTO ino_t SELECT * FROM ndb_t;
COMMIT;

SELECT * FROM ino_t;
SELECT * FROM ndb_t;

SHOW BINLOG EVENTS;

sync_slave_with_master;

SELECT * FROM ino_t;
SELECT * FROM ndb_t;

connection master;

DROP TABLE ndb_t, ino_t;

exit;

---- RESULT ----

CREATE TABLE ndb_t (a int)  ENGINE = NDB;                                                                      
CREATE TABLE ino_t (a int)  ENGINE = Innodb;                                                                   
BEGIN;                                                                                                         
INSERT INTO ndb_t VALUES(1);                                                                                   
INSERT INTO ndb_t VALUES(2);                                                                                   
INSERT INTO ino_t SELECT * FROM ndb_t;                                                                         
COMMIT;                                                                                                        
SELECT * FROM ino_t;                                                                                           
a                                                                                                              
1                                                                                                              
2                                                                                                              
SELECT * FROM ndb_t;                                                                                           
a                                                                                                              
1                                                                                                              
2                                                                                                              
SHOW BINLOG EVENTS;                                                                                            
Log_name        Pos     Event_type      Server_id       End_log_pos     Info                                   
master-bin.000001       4       Format_desc     1       107     Server ver: 5.1.41-debug-log, Binlog ver: 4    
master-bin.000001       107     Query   1       210     use `test`; CREATE TABLE ndb_t (a int)  ENGINE = NDB   
master-bin.000001       210     Query   1       316     use `test`; CREATE TABLE ino_t (a int)  ENGINE = Innodb
master-bin.000001       316     Query   1       384     BEGIN                                                  
master-bin.000001       384     Query   1       484     use `test`; INSERT INTO ino_t SELECT * FROM ndb_t      
master-bin.000001       484     Query   1       553     COMMIT                                                 
master-bin.000001       553     Query   1       617     BEGIN                                                  
master-bin.000001       617     Table_map       1       661     table_id: 44 (test.ndb_t)                      
master-bin.000001       661     Table_map       1       723     table_id: 16 (mysql.ndb_apply_status)          
master-bin.000001       723     Write_rows      1       782     table_id: 16                                   
master-bin.000001       782     Write_rows      1       821     table_id: 44 flags: STMT_END_F                 
master-bin.000001       821     Query   1       886     COMMIT                                                 
SELECT * FROM ino_t;                                                                                           
a                                                                                                              
SELECT * FROM ndb_t;                                                                                           
a                                                                                                              
2                                                                                                              
1                                                                                                              
DROP TAB

Suggested fix:
Please, either change the decide_logging_format to 

1 - mark all statement that changes a regular engine (i.e. non-self-logging engine) and is executed after any statement that changes a self-logging engine as unsafe;

2 - or at least mark INSERT INTO <regular-table> SELECT * FROM <self-logging-table> as unsafe.

It is not possible to implement the suggested fix 1 as the handlerton which is a singleton and provides information on the engine does not provide any information on the logging capabilities of the engine.

To implement the fix 1, we would need something like what follows:

bool trans_has_updated_self_logging_table(THD *thd)
{
  Ha_trx_info *ha_info;

  for (ha_info= thd->transaction.stmt.ha_list; ha_info; ha_info= ha_info->next())
  {
    handlerton *ht= ha_info->ht();
    if (ha_info->is_trx_read_write() && ht != binlog_hton &&
       (ht->flags && HA_HAS_OWN_BINLOGGING))
      return (TRUE);
  }
  return (FALSE);
}

The suggested fix 2, it is possible and would look like to what follows:

=== modified file 'sql/sql_class.cc'
--- sql/sql_class.cc    2009-11-30 18:20:26 +0000
+++ sql/sql_class.cc    2009-12-21 08:51:11 +0000
@@ -3525,8 +3525,13 @@
         flags_some_set |= flags;
       }
       if (prev_access_table && prev_access_table->file->ht != table->table->file->ht)
-        mixed_engine= mixed_engine || (prev_access_table->file->has_transactions() !=
-                      table->table->file->has_transactions());
+        mixed_engine= mixed_engine ||
+                      (prev_access_table->file->has_transactions() !=
+                      table->table->file->has_transactions()) ||
+                      ((prev_access_table->file->ha_table_flags() &
+                      HA_HAS_OWN_BINLOGGING) !=
+                      (table->table->file->ha_table_flags() &
+                      HA_HAS_OWN_BINLOGGING));
       prev_access_table= table->table;
     }

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

2974 Alfranio Correia	2010-02-22
      BUG#49019 Mixing self-logging eng. and regular eng. does not switch to row in mixed mode
      
      Reading from a self-logging engine and updating a transactional engine such as Innodb
      generates changes that are written to the binary log in the statement format and may
      make slaves diverge. In the mixed mode, such changes should be written to the binary
      log in the row format.
      
      Note that the issue does not happen if we mix a self-logging engine and MyIsam
      as this case is caught by checking the mixture of non-transactional and transactional
      engines.
      
      So, we classify a mixed statement where one reads from NDB and writes into another 
      engine as unsafe:
      
      if (multi_engine && flags_some_set & HA_HAS_OWN_BINLOGGING)
        lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE);
     @ mysql-test/suite/rpl_ndb/r/rpl_ndb_mixed_engines_transactions.result
        Augmented test case to check mixed statements
     @ mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_engines_transactions.test
        Augmented test case to check mixed statements
     @ sql/share/errmsg-utf8.txt
        Added ER_BINLOG_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE
     @ sql/sql_class.cc
        Redefined flags' name in order to have two sets of flags: (i) flags that are checked when there
        is a write operation; (ii) flags that are checked regardless of the type of the operation.
        
        Classified a mixed statement where one reads from NDB and writes into another engine as unsafe:
        
          if (multi_engine && flags_some_set & HA_HAS_OWN_BINLOGGING)
            lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE);
     @ sql/sql_lex.cc
        Added error ER_BINLOG_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE
     @ sql/sql_lex.h
        Added BINLOG_STMT_UNSAFE_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Documented in the 5.5.5 and 6.0.14 changelogs as follows:

        Reading from a table that used a self-logging storage engine and
        updating a table that used a transactional engine (such as
        InnoDB) generated changes that were written to the binary log
        using statement format which could make slaves diverge. However,
        when using mixed logging format, such changes should be written
        to the binary log using row format. (This issue did not occur
        when reading from tables using a self-logging engine and
        updating MyISAM tables, as this was already handled by checking
        for combinations of non-transactional and transactional
        engines.) Now such statements are classified as unsafe, and in
        mixed mode, cause a switch to row-based logging.

Set to Need Merge status, waiting for push to 5.1, since this is needed for Cluster (and vice versa).

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

3374 Alfranio Correia	2010-05-09
      BUG#49019 Mixing self-logging eng. and regular eng. does not switch to row in mixed mode
      
      Backport of the patch for 5.1-bugteam.

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

3384 Alfranio Correia	2010-05-16
      BUG#49019 Mixing self-logging eng. and regular eng. does not switch to row in mixed
      mode
            
      Post-push fix after backporting the patch to 5.1-bugteam.

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

3384 Alfranio Correia	2010-05-16
      BUG#49019 Mixing self-logging eng. and regular eng. does not switch to row in mixed
      mode
            
      Post-push fix after backporting the patch to 5.1-bugteam:
      
        1 - changed the name of some variables to be equivalent to pe.
        2 - fixed that patch to mark a statement as unsafe when both a
        self-logging eng. and regular eng. are accessed and one of them
        is updated.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:alik@sun.com-20100524190409-5w4l7mje1wk1c90l) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:alexey.kopytov@sun.com-20100523204118-0tl3goawu658rxh6) (merge vers: 5.5.5-m3) (pib:16)

Pushed into 5.1.48 (revid:georgi.kodinov@oracle.com-20100602084411-2yu607bslbmgufl3) (version source revid:alfranio.correia@sun.com-20100516143744-hxwogfjxf9f0ax1a) (merge vers: 5.1.47) (pib:16)

Also documented bugfix in the 5.1.48 changelog.  Closed.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100513074652-0cvlhgkesgbb2bfh) (merge vers: 5.5.5-m3) (pib:21)

No new changelog entry required. Setting back to Closed state.