Bug #45756 DDL statements not always in binary logs of other SQL Nodes on slave cluster
Submitted: 25 Jun 2009 15:34 Modified: 17 Sep 2009 13:31
Reporter: Geert Vanderkelen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Replication Severity:S3 (Non-critical)
Version:mysql-5.1-telco-6.2 OS:Any
Assigned to: Frazer Clement CPU Architecture:Any
Tags: injector, ndb, replication

[25 Jun 2009 15:34] Geert Vanderkelen
Description:
If you have two clusters with geographical replication between then, and the slave has 2 SQL nodes, the second one will not get DDL statements in its binary logs unless the 1st (active) Slave SQL node is also binary logging.
Data changes (DML) are not an issue.

How to repeat:
* Cluster A: SQL Node M doing binary logging, act as Master
* Cluster B: SQL Node S1 is Slave for M,  SQL Node S2 is stand-by.

If SQL Node S1 does not have binary logging enabled and --log-slave-updates, then S2 will not get any DDL changes in its binary logs. DML is working.
If SQL Node S1 has binary logging enabled with --log-slave-updates, then S2 will also get the DDL changes.

For example, when doing a CREATE TABLE on Cluster A, SQL Node M will binary log it and on Cluster B it will be correctly replicated. However, since S1 is not doing binary logging, the S2 (which is binary logging) doesn't get the CREATE TABLE. INSERT for example still works. 

Suggested fix:
* I think if DML is working, then DDL should work as well.
* Workaround: enable binary logging on the Slave with --log-slave-updates.
[25 Jun 2009 15:36] Geert Vanderkelen
Verified using MySQL Cluster 6.3.22 and 6.3.23..
[13 Aug 2009 13:14] Oli Sennhauser
Can repro as Geert described.
Cluster 1 (Master 1), Cluster 2 (Slave 1 (active), Slave 2 (not active))
Slave 1:
#log-bin       = bin-log
#log-slave-updates
Slave 2:
log-bin       = bin-log
log-slave-updates
Inserting row on Master 1, binlog of Slave 2 increases:
-rw-rw---- 1 mysql dba 2977 2009-08-13 15:04 bin-log.000003
-rw-rw---- 1 mysql dba 3321 2009-08-13 15:05 bin-log.000003
Creating table on Master 1, binlog of Slave 2 does NOT increase:
-rw-rw---- 1 mysql dba 3665 2009-08-13 15:07 bin-log.000003
-rw-rw---- 1 mysql dba 3665 2009-08-13 15:07 bin-log.000003
[3 Sep 2009 16:54] 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/82353

2976 Alfranio Correia	2009-09-03
      BUG#45756 DDL statements not always in binary logs of other SQL Nodes on slave cluster
[7 Sep 2009 12:08] Frazer Clement
Changing version to 6.2 as that is the lowest affected version
[8 Sep 2009 13:32] Frazer Clement
Proposed patch against 6.2

Attachment: bug45756.patch (text/x-patch), 75.46 KiB.

[8 Sep 2009 14:19] Frazer Clement
New patch proposed against 6.2.

This patch modifies ha_ndbcluster_binlog.cc in the following way :
 - When a thread is inserting a description of a schema operation into ndb_schema, set the AnyValue as follows
   if slave_thread
     setAnyValue(replicationEvent->serverId)
   else if (SQL_LOG_BIN == 0)
     setAnyValue(ANYVALUE_FOR_NOLOGGING)
   else
     setAnyValue(0);

 - When the Binlog Injector is receiving an event describing an insert into ndb_schema it decides whether to record it in the Binlog based on the event's AnyValue as follows :
   if AnyValue == 0
     // Event originated on this cluster, Binlog it
   else if AnyValue == ANYVALUD_FOR_NOLOGGING
     // Event is not for logging, don't log it
   else
     // Event has a 'serverId' and must have been applied by a slave
     if (log_slave_updates == On)
       // We are logging slave updates, log it
     else
       // Don't log it

These changes align the binlogging of DDL with the behaviour of the binlogging of DML.
e.g. :
  - DDL + DML are logged with the serverId of the server that *writes* the Binlog
  - DDL + DML are logged by any attached MySQLD with binlogging on
  - Replicated DDL+DML are logged by any attached MySQLD with binlogging and log_slave_updates on.
  - Replicated DDL+DML are logged with the serverId of the original binlogging (Master) server by any attached MySQLD with binlogging and log_slave_updates on

Note that this implies that the same DDL/DML event is logged with a different server_id in each Binlog on the 'source Cluster' that records it.  This is in alignment with the way DML events are recorded today.

Previously, ndb_schema updates were set to ANYVALUE_FOR_NOLOGGING if the executing thread's OPTION_BIN_LOG was false.  This code was probably intended to implement support for SQL_LOG_BIN=0 on MySQLDs originating changes, but it causes schema events to not be binlogged unless the active slave has Binlog activated.
Also, DDL events were previously binlogged with the serverId of the server that originally performed them.  This meant that the binlog injector could not differentiate between local-cluster-sourced DDL and replicated DDL events and therefore didn't know whether to apply the 'only-binlog-if-log-slave-updates-is-on' rule or not.

This fix : 
 1) Modifies the code that updates ndb_schema to allow receiving Binlog_injectors to tell if a schema event is local or replicated and should or should not be logged.
 2) Modifies the binlog injector code to check log_slave_updates for replicated schema events when deciding whether to binlog.

Testing : 
The change in 1) results in the binlog for some existing testcases changing.  In particular, the source Server Id changes from the server that performed the DDL to the server that recorded the Binlog.  e.g. ndb_binlog_ddl_multi, ndb_binlog_log_bin, ndb_binlog_multi.  Result files for these tests are updated.

Some circular replication testcases are modified. rpl_ndb_circular is modified to show the Binlog contents after execution.  This shows that events are binlogged as expected in a circular configuration with log-slave-updates on.  rpl_ndb_circular_2ch is modified to use log-slave-updates in all servers.  This is necessary as the testcase expects replicated events to be present in slave Binlogs.

A new testcase, rpl_ndb_slave_lsu is added which replicates DDL and DML originating at each of 3 master servers on cluster1 to each of 3 slave servers on cluster2.  The different slaves have different log-bin and log-slave-updates settings and the testcase verifies the contents of all the servers binary logs after execution.

Risks :
 a) Upgrade
    DDL originating from an 'old' MySQLD will have the AnyValue set to the old MySQLD's serverid.  To an upgraded MySQLD, this will appear to be a replicated DDL operation, which will not be logged unless log-slave-updates is on.
    This can be worked-around by either :
    i) Upgrade binlogging servers before non-binlogging servers + don't perform DDL on non upgraded servers until all are upgraded.
    ii) Set log_slave_updates to On on binlogging servers before upgrade so that all DDL is captured.

 b) Confusion
    Users may be used to how the system worked previously.  The documentation is not particularly clear, especially w.r.t. circular replication.
[11 Sep 2009 10:35] 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/83034

2993 Frazer Clement	2009-09-11
      Bug#45756 : DDL statements not always in binary logs of other SQL Nodes on slave cluster
      
      - Modify source-server-id mechanism for DDL propagation within cluster to allow Binlog Injector to differentiate between local-cluster-sourced and replication-slave-sourced DDL changes.
      - Modify Binlog Injector to generate DDL Binlog entries for replication-slave-sourced DDL changes iff log-slave-updates is On.
      
      Impact
      - Binlogging of DDL on MySQLDs attached to slave cluster is independent of log-bin and log-slave-updates setting on active slave MySQLD
        This aligns DDL handling behaviour with DML handling behaviour
      - Source server-id for DDL Binlog entries is set to the server-id of the first MySQLD to Binlog the change.
        This aligns DDL handling behaviour with DML handling behaviour
      
      Risks
      - Online upgrade of Binlogging MySQLDs
        DDL applied by non-upgraded MySQLDs will not be logged by upgraded MySQLDs unless the upgraded MySQLDs have log-slave-updates on.
        This is because the upgraded MySQLDs will think that the DDL sourced by locally-attached non-upgraded MySQLDs has been applied by a slave applier.
      
        Workarounds :
        1) Do not perform DDL during online upgrade of attached MySQLDs
           or
        2) Upgrade binlogging MySQLD node last
           or
        3) Reconfigure binlogging MySQLD nodes to set log-slave-updates=On before upgrade
           This may have other effects in circular replication scenarios. 
      added:
        mysql-test/extra/rpl_tests/rpl_ndb_multi_binlog_update.cnf
        mysql-test/extra/rpl_tests/rpl_ndb_multi_binlog_update.test
        mysql-test/suite/rpl_ndb/r/rpl_ndb_slave_lsu.result
        mysql-test/suite/rpl_ndb/t/rpl_ndb_slave_lsu.cnf
        mysql-test/suite/rpl_ndb/t/rpl_ndb_slave_lsu.test
      modified:
        mysql-test/suite/ndb_binlog/r/ndb_binlog_ddl_multi.result
        mysql-test/suite/ndb_binlog/r/ndb_binlog_log_bin.result
        mysql-test/suite/ndb_binlog/r/ndb_binlog_multi.result
        mysql-test/suite/rpl_ndb/r/rpl_ndb_circular.result
        mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test
        mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_2ch.cnf
        sql/ha_ndbcluster_binlog.cc
[11 Sep 2009 11:47] Frazer Clement
Fix pushed to :
 7.1.0
 7.0.8
 6.3.27
 6.2.19
[15 Sep 2009 11:38] 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/83262

2994 Martin Skold	2009-09-15 [merge]
      Merge
      added:
        mysql-test/extra/rpl_tests/rpl_ndb_multi_binlog_update.cnf
        mysql-test/extra/rpl_tests/rpl_ndb_multi_binlog_update.test
        mysql-test/suite/rpl_ndb/r/rpl_ndb_slave_lsu.result
        mysql-test/suite/rpl_ndb/t/rpl_ndb_slave_lsu.cnf
        mysql-test/suite/rpl_ndb/t/rpl_ndb_slave_lsu.test
      modified:
        mysql-test/suite/ndb_binlog/r/ndb_binlog_ddl_multi.result
        mysql-test/suite/ndb_binlog/r/ndb_binlog_log_bin.result
        mysql-test/suite/ndb_binlog/r/ndb_binlog_multi.result
        mysql-test/suite/rpl_ndb/r/rpl_ndb_circular.result
        mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test
        mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_2ch.cnf
        sql/ha_ndbcluster_binlog.cc
        storage/ndb/src/kernel/blocks/ERROR_codes.txt
        storage/ndb/src/kernel/blocks/dbtc/DbtcMain.cpp
        storage/ndb/test/ndbapi/testNodeRestart.cpp
        storage/ndb/test/run-test/daily-basic-tests.txt
        storage/ndb/test/run-test/daily-devel-tests.txt
[15 Sep 2009 12:27] 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/83270

2995 Martin Skold	2009-09-15 [merge]
      Merge
      added:
        mysql-test/extra/rpl_tests/rpl_ndb_multi_binlog_update.cnf
        mysql-test/extra/rpl_tests/rpl_ndb_multi_binlog_update.test
        mysql-test/suite/rpl_ndb/r/rpl_ndb_slave_lsu.result
        mysql-test/suite/rpl_ndb/t/rpl_ndb_slave_lsu.cnf
        mysql-test/suite/rpl_ndb/t/rpl_ndb_slave_lsu.test
      modified:
        mysql-test/suite/ndb_binlog/r/ndb_binlog_ddl_multi.result
        mysql-test/suite/ndb_binlog/r/ndb_binlog_log_bin.result
        mysql-test/suite/ndb_binlog/r/ndb_binlog_multi.result
        mysql-test/suite/rpl_ndb/r/rpl_ndb_circular.result
        mysql-test/suite/rpl_ndb/t/rpl_ndb_circular.test
        mysql-test/suite/rpl_ndb/t/rpl_ndb_circular_2ch.cnf
        sql/ha_ndbcluster_binlog.cc
        storage/ndb/src/kernel/blocks/ERROR_codes.txt
        storage/ndb/src/kernel/blocks/dbtc/DbtcMain.cpp
        storage/ndb/test/ndbapi/testNodeRestart.cpp
        storage/ndb/test/run-test/daily-basic-tests.txt
        storage/ndb/test/run-test/daily-devel-tests.txt
[17 Sep 2009 13:31] Jon Stephens
Documented bugfix in the NDB-6.2.19, 6.3.27, and 7.0.8 changelogs, as follows:

        In a MySQL Cluster acting as a replication slave had multiple
        SQL nodes, only the SQL node receiving events directly from the
        master recorded DDL statements in its binary logs unless this
        SQL node had binary logging enabled; other SQL nodes in the
        slave cluster failed to log DDL statements, regardless of their
        individual --log-bin settings.

        The fix for this issue aligns binary logging of DDL statements
        with that of DML statements. In particular, you should take note
        of the following:

            DDL and DML statements on the master are logged with 
            the server ID of the server that actually writes the 
            log.

            DDL and DML statements on the master cluster are logged 
            by any attached mysqld that has binary logging enabled.

            Replicated DDL and DML statements on the slave are 
            logged by any attached mysqld that has both --log-bin 
            and --log-slave-updates enabled.
          
            Replicated DDL and DML statements are logged with the server
            ID of the original (master) MySQL server by any attached
            MySQLD that has both --log-bin and --log-slave-updates
            enabled.
                  
          Affect on upgrades

          When upgrading from a previous MySQL Cluster release, you
          should perform either one of the following:
          
                i. Upgrade servers that are performing binary logging
                before those that are not; do not perform any DDL on
                "old" SQL nodes until all SQL nodes have been upgraded.
              
                ii. Make sure that --log-slave-updates is
                enabled on all SQL nodes performing binary logging 
                prior to the upgrade, so that all DDL is captured.
              
          NOTE. Logging of DML statements was not affected by this 
          issue.

Closed.

--

NB: Some additional work remain to be done on related documentation in the Manual; this is being handled in BUG#46762.