default_tmp_storage_engine variable was introduced in 5.6.3, allowing the configuration of the default engine for temporary tables. This seems to be in the direction, as I commented before, of making MyISAM an optional engine. In 5.7, a separate tablespace is being created to hold those tables in order to reduce its performance penalty (those tables do not need to be redone if the server crashes, so extra writes are avoided).

However, I have seen many people assuming that because default_tmp_storage_engine has the value “InnoDB”, all temporary tables are created in InnoDB format in 5.6. This is not true: first, because implicit temporary tables are still being created in memory using …

It all started with a goal to make InnoDB temporary tables more effective. Temporary table semantics are blessed with some important characteristics that can help us simplify lot of operations.

• Temporary tables are not visible across connections
• Temporary tables lifetime is limited to connection lifetime (unless user explicitly drops it).

What does this means in to InnoDB ?

• REDO logging can be avoided for temporary tables and related objects since temporary tables do not survive a shutdown or crash.
• Temporary table definitions can be maintained in-memory without persisting to the disk.
• Locking constraints can be relaxed since only one client can see these tables.
• Change buffering can be avoided since the majority of temporary tables are short-lived.

In order to implement these changes in InnoDB we took a bit different approach:

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

1. Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
2. Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
3. Master: INSERT INTO rpltmpbreak VALUES (1);
4. Slave: SHOW SLAVE STATUS \G

If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but nevertheless it’s quite …