Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC

enforce_storage_engine=Innodb not compatible with wsrep_replicate_myisam

Bug #1435482 reported by CodyC on 2015-03-23

This bug affects 2 people

	Status	Importance	Assigned to	Milestone
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC	Status tracked in 5.6
5.5	Fix Released	Undecided	Raghavendra D Prabhu	Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC 5.5.41-25.11
5.6	Fix Released	Undecided	Raghavendra D Prabhu	Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC 5.6.24-25.11

Bug Description

We're running a PXC cluster with enforce_storage_engine=Innodb (because some 3rd-party software still insists on using MyISAM, which would break replication.)

This worked great in 5.6.21. Tables created as myISAM were transparently converted to InnoDB and properly replicated to the whole cluster.

But in 5.6.22, replication fails and the cluster is stopped.

Steps to reproduce:

1. Configure PXC replication on 5.6.22 w/ these additional options:

enforce_storage_engine=Innodb
sqlmode=

2. Create MyISAM table.
3. Put data in the table
4. Crash

Sample logs:

from node 1:

2015-03-23 17:11:32 1926 [Note] WSREP: Cannot replicate MyISAM DDL for [redacted: schema.table_name] with wsrep_replicate_myisam OFF

2015-03-23 17:11:34 1926 [Note] WSREP: forgetting 1ec1728d (ssl://[redacted])

2015-03-23 17:11:34 1926 [Note] WSREP: (e3ab5450, 'ssl://0.0.0.0:4567') address 'ssl://[redacted]' pointing to uuid e3ab5450 is blackl
isted, skipping

from node 2:
2015-03-23 17:11:33 1915 [Warning] WSREP: BF applier failed to open_and_lock_tables: 1146, fatal: 0 wsrep = (exec_mode: 1 conflict_state: 0 seqno: 317)
2015-03-23 17:11:33 1915 [ERROR] Slave SQL: Error executing row event: 'Table '[redacted: schema.table_name]' doesn't exist', Error_code: 1146
2015-03-23 17:11:33 1915 [Warning] WSREP: RBR event 3 Write_rows apply warning: 1146, 317
2015-03-23 17:11:33 1915 [Warning] WSREP: Failed to apply app buffer: seqno: 317, status: 1
at galera/src/trx_handle.cpp:apply():351
Retrying 2th time

Revision history for this message

CodyC (cody-casterline) wrote on 2015-03-23:

Oops, here's the link to the bug mentioned in the title:
https://code.launchpad.net/~percona-core/percona-xtradb-cluster/bug-1402338-5.5/+merge/245523

Revision history for this message

Raghavendra D Prabhu (raghavendra-prabhu) wrote on 2015-03-23:

@CodyC,

So, as I see here, with those options, myisam table is converted to innodb on host where it is executed. However, this is done *after* TOI hence not replicated even though the table created is innodb.

The workaround I see in this case is to set wsrep-replicate-myisam globally to ON whenever you have DDL. Since you have enforced_storage_engine set to InnoDB, you shouldn't have any MyISAM traffic anyway.

Revision history for this message

Raghavendra D Prabhu (raghavendra-prabhu) wrote on 2015-03-23:

As mentioned earlier,

#ifdef WITH_WSREP
        /* in STATEMENT format, we probably have to replicate also temporary
           tables, like mysql replication does
        */
      if (!thd->is_current_stmt_binlog_format_row() ||
          !(create_info.options & HA_LEX_CREATE_TMP_TABLE))
       WSREP_TO_ISOLATION_BEGIN(create_table->db, create_table->table_name,
                                 NULL)
#endif /* WITH_WSREP */
        /* Regular CREATE TABLE */
        res= mysql_create_table(thd, create_table,
                                &create_info, &alter_info);

The check for enforce_storage_engine is done after TOI in mysql_create_table. Hence, this check cannot be done for TOI.

Revision history for this message

CodyC (cody-casterline) wrote on 2015-03-23:

Sorry, I'm only a user, I'm not familiar w/ MySQL internals. What's TOI? Total Order Isolation?

Thanks for the workaround suggestion! We'll test it and see if that works for us. It seems quite counter-intuitive to need to enable myisam replication to get innodb replication to work, though!

Revision history for this message

Raghavendra D Prabhu (raghavendra-prabhu) wrote on 2015-03-23:

Yes, TOI is Total Order Isolation. The reason for workaround is that replication of DDL under TOI is taken by said statement is executed (to ensure statement is executed at same time on all nodes).

Hence, when you try to create myisam table, for TOI it is still a myisam table and enforce_storage_engine comes into picture only later.

Nevertheless, it should be possible to check for enforce_storage_engine specifically in TOI.

Raghavendra D Prabhu (raghavendra-prabhu) on 2015-03-23

summary:	- Regression: bug-1402338-5.5 breaks replication with - enforce_storage_engine=Innodb + Regression: enforce_storage_engine=Innodb not compatible with + wsrep_replicate_myisam
summary:	- Regression: enforce_storage_engine=Innodb not compatible with - wsrep_replicate_myisam + enforce_storage_engine=Innodb not compatible with wsrep_replicate_myisam

Revision history for this message

Shahriyar Rzayev (rzayev-sehriyar) wrote on 2018-01-18:

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

Report a bug

This report contains Public information

Everyone can see this information.

You are

Subscribing...

Edit bug mail

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.