PXC AUTO_INCREMENT values don't replicate to MyISAM tables correctly

Bug #1080913 reported by Daniel Nichter on 2012-11-19
This bug affects 3 people
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Percona Toolkit moved to https://jira.percona.com/projects/PT
Won't Fix
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC
Won't Fix

Bug Description

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.

Changed in percona-toolkit:
status: New → Confirmed
tags: added: auto-inc
removed: auto-increment
Changed in galera:
status: New → Confirmed
Changed in percona-toolkit:
milestone: none → 2.2.1
importance: Undecided → High
Changed in codership-mysql:
status: New → Confirmed
no longer affects: galera
tags: added: pxc
Changed in percona-toolkit:
milestone: 2.2.1 → none
summary: - AUTO_INCREMENT values don't replicate to MyISAM tables correctly
+ PXC AUTO_INCREMENT values don't replicate to MyISAM tables correctly
Changed in percona-toolkit:
importance: High → Undecided
Daniel Nichter (daniel-nichter) wrote :

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.

Changed in percona-toolkit:
status: Confirmed → Won't Fix
Krunal Bauskar (krunal-bauskar) wrote :

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.


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.

Changed in percona-xtradb-cluster:
status: Confirmed → Won't Fix

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

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

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

Other bug subscribers