PXC AUTO_INCREMENT values don't replicate to MyISAM tables correctly

[Daniel Nichter]

To reproduce:

1. Create a MyISAM table with an AUTO_INCREMENT column on node1.

2. INSERT INTO <that table> (auto_inc_col) VALUES (NULL);

3. The new auto-inc value on the node is 1, but it is 2, 3, or other values on other nodes.

This only affect MyISAM tables, regardless of binlog_format (if that affects PXC/Galera at all). Change the same table to InnoDB and there's no problem.

[Daniel Nichter (daniel-nichter)]

We've had about 8 months of experience with PXC now, and the verdict seems clear: PXC and MyISAM are not a good combination, and afaik there's no big push from users to make it work, i.e. the 99% use-case is PXC with InnoDB. So I'm going to WontFix this bug in Percona Toolkit.

[Krunal Bauskar (krunal-bauskar)]

Mixing Galera with MyISAM is of-course not part of best-practice but I assume the support is left out so that end-user can easily migrate from MyISAM to InnoDB.

Anyway,

I tried the given use-case with latest 5.6 trunk

1. Start node-1,2
2. node-1: create myisam table t1 with auto-inc, insert (NULL)
3. node-2: no table t1 with as wsrep_replicate_myisam is OFF by default.
4. node-1: set wsrep_replicate_myisam = 1. create myisam table t2 (note you can't use t1 it is not replicated)
5. node-1: insert (NULL)
6. node-2: table and insert are visible so replication with auto-inc column succeed.

IMPORTANT: there are lot of other issues with use of MyISAM that original exist in upstream.
(For example: what happen in-case you continue to insert into t1 post replicate_myisam = 1)
node-2: doesn't have the table so node-2 generate error if it get insert stmt on t1 as t1 doesn't exist.
What if I now create t1 (engine=innodb) on node-2 and insert (NULL) on node-1. This passes when it should ideally fail
as both tables are of different engine-type.

There are lot of other such interesting observations and so the support for MyISAM is experimental.

Anyway, the main issue is working with latest 5.6 so I will close this bug.

[Shahriyar Rzayev (rzayev-sehriyar)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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