XtraDB: creation of MyISAM temporary table causes error messages in log

[Veshij]

I have 3-nodes XtraDB cluster and i want to migrate applications to this cluster.
Some code creates temporary MyISAM tables and it causes error messages in log on all cluster nodes.

Testcase:

root@db-x01f:~# mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1045801
Server version: 5.6.20-68.0-56-log Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126
...
mysql> show variables like 'wsrep_replicate_myisam';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| wsrep_replicate_myisam | ON |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE `t` ( id int(8)) ENGINE=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

Error message (appears on all other nodes in cluster):

2014-11-11 23:57:16 18553 [ERROR] Slave SQL: Error 'Table 'test.t' doesn't exist' on query. Default database: 'test'. Query: 'insert into t values(1)', Error_code: 1146
2014-11-11 23:57:16 18553 [Warning] WSREP: RBR event 1 Query apply warning: 1, 2102331
2014-11-11 23:57:16 18553 [Warning] WSREP: Ignoring error for TO isolated action: source: f4057a94-69ba-11e4-a327-264938fa2dc9 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 1045801 trx_id: -1 seqnos (l: 2127680, g: 2102331, s: 2102330, d: 2102330, ts: 1151432616722208)

Versions:
root@db-x01f:~# dpkg -l | grep percona
ii percona-repo-config 1.2 all Configures Percona mirror repo
ii percona-toolkit 2.2.11 all Advanced MySQL and system command-line tools
ii percona-xtrabackup 2.2.5-5027-1.trusty amd64 Open source backup tool for InnoDB and XtraDB
ii percona-xtradb-cluster-client-5.6 5.6.20-25.7-886.trusty amd64 Percona XtraDB Cluster database client binaries
ii percona-xtradb-cluster-common-5.6 5.6.20-25.7-886.trusty amd64 Percona XtraDB Cluster database common files (e.g. /etc/mysql/my.cnf)
ii percona-xtradb-cluster-galera-3.x 3.7.3256.trusty amd64 Galera components of Percona XtraDB Cluster
ii percona-xtradb-cluster-server-5.6 5.6.20-25.7-886.trusty amd64 Percona XtraDB Cluster database server binaries
root@mtacs-x01f:~#

[Raghavendra D Prabhu (raghavendra-prabhu)]

Temporary tables are not replicated in ROW based replication (hence in Galera).

[Veshij (gu39)]

I understand it, but i think i shouldn't have error messages on other boxes in cluster.

[Raghavendra D Prabhu (raghavendra-prabhu)]

The errors are ignored though, so it shouldn't cause any disruption. However, the error message is valid since all MyISAM commands are executed as TOI, ie. they are replicated to all nodes before run locally, so it cannot be conditonally replicated based on whether table exists on local box.

[Shahriyar Rzayev (rzayev-sehriyar)]

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