Databases Reference
In-Depth Information
Missing Temporary Tables
Temporary tables are handy for some uses, but unfortunately they're incompatible with
statement-based replication. If a replica crashes, or if you shut it down, any temporary
tables the replica thread was using disappear. When you restart the replica, any further
statements that refer to the missing temporary tables will fail.
There's no safe way to use temporary tables on the master with statement-based rep-
lication. Many people love temporary tables dearly, so it can be hard to convince them
of this, but it's true.
20
No matter how briefly they exist, temporary tables make it dif-
ficult to stop and start replicas and to recover from crashes. This is true even if you use
them only within a single transaction. (It's slightly less problematic to use temporary
tables on a replica, where they can be convenient, but if the replica is itself a master,
the problem still exists.)
If replication stops because the replica can't find a temporary table after a restart, there
are really only a couple of things to do: you can skip the errors that occur, or you can
manually create a table that has the same name and structure as the now-vanished
temporary table. Either way, your data will likely become different on the replica if any
write queries refer to the temporary table.
It's not as hard as it seems to eliminate temporary tables. The two most useful properties
of temporary tables are as follows:
• They're visible only to the connection that created them, so they don't conflict with
other connections' temporary tables of the same names.
• They go away when the connection closes, so you don't have to remove them
explicitly.
You can emulate these properties easily by reserving a database exclusively for
pseudotemporary tables, where you'll create permanent tables instead. You just have
to choose unique names for them. Fortunately, that's pretty easy to do: simply append
the connection ID to the table name. For example, where you used to execute
CREATE
TEMPORARY TABLE top_users(...)
, now you can execute
CREATE TABLE temp
.top_users_1234(...)
, where
1234
is the value returned by
CONNECTION_ID()
. After your
application is done with the pseudotemporary table, you can either drop it or let a
cleanup process remove it instead. Having the connection ID in the table name makes
it easy to determine which tables are not in use anymore—you can get a list of active
connections from
SHOW PROCESSLIST
and compare it to the connection IDs in the table
names.
21
20. We've had people stubbornly try all sorts of ways to work around this, but there is no way to make
temporary tables safe for statement-based replication. Period. No matter what you're thinking of, we've
proven it won't work.
21.
pt-find
—yet another tool in the Percona Toolkit—can remove pseudotemporary tables easily with the
--
connection-id
and --
server-id
options.
