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

Answer 1

• 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.