enforce_storage_engine=Innodb not compatible with wsrep_replicate_myisam

Bug #1435482 reported by CodyC on 2015-03-23
12
This bug affects 2 people
Affects 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
5.6
Fix Released
Undecided
Raghavendra D Prabhu

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

CodyC (cody-casterline) wrote :

@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.

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.

CodyC (cody-casterline) wrote :

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!

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.

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

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

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

Other bug subscribers