Mysql – Truncate temporary table commits transaction

ddlinnodbMySQLrollbacktransaction

Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction?

Does this mean that the only way to have a fresh temporary table without committing the current transaction is to run (after above DDL statement):

DELETE FROM temp-table-name;

This TRUNCATE auto-commit behavior caused a rather nasty bug in my app — the rollback didn't go all the way up because of the "TRUNCATE barrier" 😉

Best Answer

It is true that CREATE TEMPORARY TABLE IF NOT EXISTS.. is DDL but did you know that TRUNCATE TABLE is also itself DDL ??? I have mentioned this many times over the years ...

Feb 12, 2014 : row locking within ACID transaction innodb
May 23, 2013 : TRUNCATE TABLE statement sometimes hangs
Jul 09, 2012 : What can cause TRUNCATE TABLE to take a really long time?
Jan 17, 2012 ; Problem with InnoDB "per table" file sizes
Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around

The MySQL Documentation on TRUNCATE TABLE says the following:

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.6:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

Truncate operations cause an implicit commit, and so cannot be rolled back.

Truncation operations cannot be performed if the session holds an active table lock.

TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”

As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

So, to answer your question, the way you are handling it is the only way.

Related Solutions

Sql-server – SQL Server (2012) non-blocking DDL

No there's no way of configuring SQL Server to do what you want to do.

Under snapshot isolation the call to sp_tables gets blocked waiting for a shared key lock on one of the system base tables (sysschobjs) when doing a SELECT from sys.all_objects

The Using Row Versioning-based Isolation Levels topic in BOL does say:

SQL Server does not keep multiple versions of system metadata. Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata.

Even under TRANSACTION ISOLATION LEVEL READ UNCOMMITTED the call to sp_tables ends up blocking however, whilst the straight forward SELECT ... FROM sys.all_objects is no longer blocked the same query references the HAS_PERMS_BY_NAME function in the WHERE clause. This appears to start a system transaction (CMetadataAccessor::CMetadataAcce) at a higher isolation level and ends up getting blocked waiting for a shared key lock on sysschobjs again.

MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time

Here is the nature of table creation with CREATE TEMPORARY TABLE

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

My guess would be you must have done the following sequence of events:

You ran CREATE TEMPORARY TABLE
Terminated DB Connection (which makes the temporary table drop immediately)
You had to run CREATE TEMPORARY TABLE again because he table did not exist

I have seen this problem victimize many developers, especially when it comes to MySQL Replication. For example, if you run STOP SLAVE;, the SQL Thread will close. Any tables create with CREATE TEMPORARY TABLE will be dropped. When you run START SLAVE;, MySQL Replication breaks instantly because the table needed for INSERTs, UPDATEs, or DELETEs no longer exist. I have had to perform mysqlbinlog dumps on relay logs to locate the original CREATE TEMPORARY TABLE statements, run them as CREATE TABLE in order for the table to remain accessible to other DB Connections, including the SQL Thread when START SLAVE; is reissued.

As for your Questions

Is there a way to tell MySQL that the creation of the temporary table and the SELECT query are just executed if the table does not exist?
Maybe with an if-condition?
Or can I check to some information schema if a temp-table exists?

You cannot check the information_schema for temp table existence. In the OS, there is no manifested .frm in datadir. You could find it in the folder defined in tmpdir.

Here is an example: I will create a temporary table called test.junk and try to locate the whereabouts of the table. (Note: I am using MySQL for Windows)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create temporary table junk (a int) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table junk\G
*************************** 1. row ***************************
       Table: junk
Create Table: CREATE TEMPORARY TABLE `junk` (
  `a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> select count(1) from information_schema.tables
-> where table_schema='test' and table_name='junk';
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.08 sec)

mysql> show variables like 'datadir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | C:\MySQL_5.5.12\data\ |
+---------------+-----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'tmpdir';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| tmpdir        | C:\Users\redwards\AppData\Local\Temp |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>

The table is not visible in INFORMATION_SCHEMA.TABLES. Let's locate the table in the OS

C:\MySQL_5.5.12\data\test>cd
C:\MySQL_5.5.12\data\test

C:\MySQL_5.5.12\data\test>dir junk.*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\MySQL_5.5.12\data\test

File Not Found

C:\MySQL_5.5.12\data\test>dir C:\Users\redwards\AppData\Local\Temp\*.frm
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\Users\redwards\AppData\Local\Temp

10/19/2012  11:04 AM             8,554 #sql7ac_3_0.frm
               1 File(s)          8,554 bytes
               0 Dir(s)  190,200,049,664 bytes free

C:\MySQL_5.5.12\data\test>dir C:\Users\redwards\AppData\Local\Temp\*.MY*
 Volume in drive C has no label.
 Volume Serial Number is 2C92-485B

 Directory of C:\Users\redwards\AppData\Local\Temp

10/19/2012  11:04 AM                 0 #sql7ac_3_0.MYD
10/19/2012  11:04 AM             1,024 #sql7ac_3_0.MYI
               2 File(s)          1,024 bytes
               0 Dir(s)  190,200,049,664 bytes free

C:\MySQL_5.5.12\data\test>

There is the temp table, in the tmpdir folder. When I closed the mysql client and run dir C:\Users\redwards\AppData\Local\Temp\*.frm, the table vanished.

RECOMMENDATION

You can do one of two things

Create the temp table, load it with data, keep the same DB Connection around, and then access the data with the same ProcessID.
Do CREATE TEMPORARY TABLE IF NOT EXISTS (This is really a band-aid to improper design but may circumvent the problem).

UPDATE 2012-12-18 13:06 EDT

Further Observation #1

If you stayed with the same DB Connection and still have this problem, here something else not previously mentioned: You gave the temp table an index. That should be a UNIQUE INDEX or PRIMARY KEY.

Further Observation #2

You could shift the operation from a CREATE TABLE ... SELECT to CREATE TABLE ... INSERT IGNORE

Further Observation #3

If you want to use temp tables, please specify MyISAM. InnoDB has a tendency to leave pigeon holes in the data dictionary with ibdata1 in the event of a crash or upon DB Connection termination.

With these three(3) observations in mind, here is my recommended change

CREATE TEMPORARY TABLE IF NOT EXISTS cache
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO cache SELECT id FROM table WHERE xyz;

CREATE TEMPORARY TABLE IF NOT EXISTS cache
(id int(11) NOT NULL, PRIMARY KEY (id)) ENGINE=MyISAM;
INSERT IGNORE INTO cache SELECT id FROM table WHERE xyz;

Give it a Try !!!