Bug #49174 Possible documentation bug for CREATE [TEMPORARY] TABLE

• Submitted: 28 Nov 2009 4:39
• Modified: 28 Nov 2009 4:51
• Reporter: Roel Van de Paar
• Status: Not a Bug
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.1.4
• OS: Any
• CPU Architecture: Any

[28 Nov 2009 4:39] Roel Van de Paar

Description:
This page:
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

States:
'CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.'

This does not seem correct.

How to repeat:
mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | C:\Windows\TEMP |
+---------------+-----------------+
1 row in set (0.00 sec)
---
C:\Windows\Temp>dir #*
 Volume in drive C is SYS
 Volume Serial Number is 34A4-C4D1

 Directory of C:\Windows\Temp

File Not Found
---
mysql> CREATE TEMPORARY TABLE TESTDISK (ID INT) ENGINE=MYISAM;
Query OK, 0 rows affected (0.01 sec)
---
C:\Windows\Temp>dir #*
 Volume in drive C is SYS
 Volume Serial Number is 34A4-C4D1

 Directory of C:\Windows\Temp

20/11/2009  01:57 PM             8,556 #sqlf08_4_c.frm
20/11/2009  01:57 PM                 0 #sqlf08_4_c.MYD
20/11/2009  01:57 PM             1,024 #sqlf08_4_c.MYI
               3 File(s)          9,580 bytes
               0 Dir(s)  20,547,649,536 bytes free

Suggested fix:
Changed documentation, maybe after verification why it was listed like this originally.

[28 Nov 2009 4:43] Roel Van de Paar

Verifying as D2 (Behavior contrary to documentation)

Same on Linux:

mysql> SHOW GLOBAL VARIABLES LIKE 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.00 sec)
---
roel@roel-ubuntu-vm:/tmp$ ls \#*
ls: cannot access #*: No such file or directory
---
mysql> CREATE TEMPORARY TABLE TESTDISK (ID INT) ENGINE=MYISAM;
Query OK, 0 rows affected (0.00 sec)
---
roel@roel-ubuntu-vm:/tmp$ ls -l \#sql*
-rw-rw---- 1 roel roel 8556 2009-11-28 15:36 #sqlc4c_1_0.frm
-rw-rw---- 1 roel roel    0 2009-11-28 15:36 #sqlc4c_1_0.MYD
-rw-rw---- 1 roel roel 1024 2009-11-28 15:36 #sqlc4c_1_0.MYI

[28 Nov 2009 4:51] Roel Van de Paar

Realized that the documentation means 'when a transaction has begun, and CREATE TEMPORARY TABLE is issued, it does not commit'. Interesting to note though is that the files get created immediately:

roel@roel-ubuntu-vm:/tmp$ ls -l \#sql*
ls: cannot access #sql*: No such file or directory
---
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE TESTDISK (ID INT) ENGINE=MYISAM;
Query OK, 0 rows affected (0.00 sec)
---
roel@roel-ubuntu-vm:/tmp$ ls -l \#sql*
-rw-rw---- 1 roel roel 8556 2009-11-28 15:45 #sqlc4c_1_1.frm
-rw-rw---- 1 roel roel    0 2009-11-28 15:45 #sqlc4c_1_1.MYD
-rw-rw---- 1 roel roel 1024 2009-11-28 15:45 #sqlc4c_1_1.MYI