How to work with MySQL temp tables in a Galera Cluster?

Question

I am scaling my application from one MySQL server to a 3 node MySQL Galera Gluster. Aparently temporary tables do not get replicated.

Is there a common workaround this problem?

My current code looks like this:

        $stmt = "
            CREATE TEMPORARY TABLE tmp (`city_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL ,INDEX ( `city_id` ) )
        ";          
        db_query($stmt);
        # travel tips
        $stmt = "
            INSERT INTO tmp
            SELECT city_id
            FROM $DB.$T33 g
            WHERE g.country_code = '$country[code]'
            GROUP BY city_id
        ";          
        execute_stmt($stmt, $link);

The error message is:

Error: 1146 Table 'test.tmp' doesn't exist

score 4 · Accepted Answer · answered Sep 05 '15 at 15:03

CREATE TEMPORARY TABLE creates a table visible only to the session where it was created. No other connections can see it.
A single connection stays connected to one node.

With those two in mind, it does not matter whether such a table is replicated.

ROW based replication is a requirement of Galera.
MyISAM tables are not replicated.

With those two additional bullet items, it does not even matter if the TEMPORARY TABLE is ENGINE=MyISAM (or MEMORY).

Back to your problem. What do db_query and execute_stmt do?

Do they also connect to the server? Not good. Have only one connection for your program.
Do they go through some form of Proxy before getting to a Galera node? It would not be good for it to be switching nodes.

How to work with MySQL temp tables in a Galera Cluster?

1 Answers1