Don't replicate MyISAM DDL (CREATE/DROP) when wsrep-replicate-myisam is OFF

Bug #1402338 reported by Raghavendra D Prabhu on 2014-12-14
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Undecided
Unassigned
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC
Status tracked in 5.6
5.5
Fix Released
Wishlist
Raghavendra D Prabhu
5.6
Fix Released
Wishlist
Raghavendra D Prabhu

Bug Description

Not to replicate CREATE/DROP etc.

Related branches

Alex Yurchenko (ayurchen) wrote :

what's the rationale? how DDLs are related to MyISAM?

@Alex,

Some user reports were that they didn't want create table for myisam to succeed and later, the DML on those tables to fail (or cluster inconsistency) when wsrep-replicate-myisam was OFF.

summary: - Don't allow DDL (CREATE/DROP) when wsrep-replicate-myisam is OFF
+ Don't replicate MyISAM DDL (CREATE/DROP) when wsrep-replicate-myisam is
+ OFF
description: updated
Alex Yurchenko (ayurchen) wrote :

right

Frederic Descamps (lefred) wrote :

and what about upgrades ? will those change happen on system tables anyway ?

@Fred,

From mysql_upgrade.c I see:

  /*
    Master and slave should be upgraded separately. All statements executed
    by mysql_upgrade will not be binlogged.
    'SET SQL_LOG_BIN=0' is executed before any other statements.
   */

So, the standard mysql convention seems to ask for separate mysql_upgrade for every node.

Note that, PXC major upgrade states to run mysql_upgrade while it is running with none provider, essentially not replicating it.

Now, if a node is ugpraded and started, while there are other nodes in the cluster with older system tables, then it is required that no system table changes (DML) are made (through add user etc.) unless all nodes are upgraded.

Frederic Descamps (lefred) wrote :

Thank you for the reply

Kasi (vissu-jntk) wrote :

So only drop statement is going to be replicated but not create table statement in 5.5.41-25.11 version and both create table and drop table statements are not going to be replicated in 5.6.22-25.8. is my understanding correct?

Krunal Bauskar (krunal-bauskar) wrote :

Kasi,

Seems like there was an in-consistency introduced in recent reversion of 5.6.
Patch with updated semantics has been pushed to latest 5.6 which will be part of next 5.6 release.

Let me re-quote the new/updated semantics for clarity here:

      Existing or Updated Semantics:

      a. DDL statement: CREATE/DROP/TRUNCATE/.... for engine=myisam
         Will be replicated ir-respective of wsrep_replicate_myisam settings.
         This is done to ensure that name of the object is blocked so that user
         can't create another object with same name on replicated node (with or
         without different schema structure).
         [No change in innodb semantics]

      b. DML statement: INSERT/UPDATE/DELETE/SELECT .... for engine=myisam
         Will be replicated only if wsrep_replicate_myisam = 1.
         Expect node in-consistency if user try to run part of workload with
         wsrep_replicate_myisam = 0 and rest of the part with
         wsrep_replicate_myisam = 1

      c. SST xfer:
         A newly booted cluster node that get full SST will also get MyISAM table
         (schema + data) ir-respective of wsrep_replicate_myisam setting.

      d. enforce_storage_engine (options specific to PXC)
         If this option is set and sql_mode doesn't conflict with the setting
         (set sql_mode = "") then even if user request creation of MyISAM table it
         will be auto-converted to SE dictated by enforce_storage_engine option.
         Replication status is captured even before enforce storage engine variable
         modifies the table execution path and so for replication original statement
         as issued by user is used. If this option is set for #node-1 and not for
         #node-2 then expect in-consistency in cluster operation as #node-1 would
         end up auto-updating SE for the table to "InnoDB" but #node-2 may continue
         to create the same table with original user specified engine.
         (This limitation should be documented)

      e. CTAS statement: This is executed and handled differently as it involves
         DML + DDL. CTAS semantics are already confusing in upstream.
         PXC ensures that if the CTAS "select" has myisam table only
         (or no table select 1) such queries are not replicated.

         Why is this so ?
         - If there is involvement of innodb table then trx context has to be setup
           and wsrep-plugin is design to work only if trx context is setup.

         Then how come other myisam stmt replicated ?
         - Using TOI and CTAS doesn't use TOI.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1181

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers