3 ms·

fwiw, CREATE [TEMPORARY] TABLE AS SELECT can be problematic in MySQL even without Galera. It is outright disallowed if MySQL 5.6+ Global Transaction ID (GTID) i

fwiw, CREATE [TEMPORARY] TABLE AS SELECT can be problematic in MySQL even without Galera. It is outright disallowed if MySQL 5.6+ Global Transaction ID (GTID) is enabled, for example. The issue is that it's combining DDL and DML in a single statement/transaction, but DDL is still not yet inherently transactional in MySQL. The work-around is to just to use two statements, CREATE TABLE (or possibly CREATE TABLE LIKE) and then a separate INSERT...SELECT. That all said, yes, Galera should ideally handle this gracefully instead of ever deadlocking. But I don't have enough Galera experience to comment on that aspect.

clon 9y ago
The docs state that creating tables is not supported, but I am unsure about the '[TEMPORARY]' part. In practice, it is not verboten, see below for 5.6: mysql> SHOW VARIABLES LIKE '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_deployment_step | OFF | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 9 rows in set (0.00 sec) mysql> CREATE TABLE test AS SELECT 1; ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1. mysql> CREATE TEMPORARY TABLE test AS SELECT 1; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 So perhaps it is still 'not supported', as in our experience it does indeed cause some issues, probably avoidable if you are willing to do dirty reads (READ COMMITTED). I wonder if REPEATABLE READ is a good default anyway.

clon 9y ago

The docs state that creating tables is not supported, but I am unsure about the '[TEMPORARY]' part. In practice, it is not verboten, see below for 5.6: mysql> SHOW VARIABLES LIKE '%gtid%'; +---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | binlog_gtid_simple_recovery | OFF | | enforce_gtid_consistency | ON | | gtid_deployment_step | OFF | | gtid_executed | | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | simplified_binlog_gtid_recovery | OFF | +---------------------------------+-----------+ 9 rows in set (0.00 sec) mysql> CREATE TABLE test AS SELECT 1; ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1. mysql> CREATE TEMPORARY TABLE test AS SELECT 1; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 So perhaps it is still 'not supported', as in our experience it does indeed cause some issues, probably avoidable if you are willing to do dirty reads (READ COMMITTED). I wonder if REPEATABLE READ is a good default anyway.