PXC INSERT triggers on MyISAM tables insert additional rows on InnoDB tables

[Daniel Nichter]

This is probably related to bug 1064057, but it's also different:

CREATE TABLE `old` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

CREATE TABLE `new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

             Trigger: foo_ins
               Event: INSERT
               Table: old
           Statement: insert into new (id) values (NEW.id)
              Timing: AFTER
             Created: NULL
            sql_mode:
             Definer: msandbox@%
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

INSERT INTO old VALUES (NULL); -- on the node where those ^ are created, yields:

+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

But on another node:

+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.01 sec)

+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

So table `new` on other nodes inserts an additional row. (The additional row also has the wrong auto-inc value due to bug 1080913).

[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)]

It is not advisable to mix MyISAM and InnoDB tables while using galera replication.
Also, support for MyISAM is experimental and so bare minimum esp. with MyISAM + InnoDB + trigger it sounds like a complex workload for bare minimum support.

Will close this bug as WONTFIX.

[Shahriyar Rzayev (rzayev-sehriyar)]

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