MySQL Bugs: #5137: Temporary tables with innodb as default table type using innodb_file_per

Bug #5137	Temporary tables with innodb as default table type using innodb_file_per_table
Submitted:	21 Aug 2004 6:15	Modified:	18 Oct 2004 16:37
Reporter:	Carlos Proal Aguilar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.1.3beta/4.1.4	OS:	Solaris (Solaris 9/Windows)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I have innodb as my default table type but when i create a temporary table i lost the connection with mysql.
The log shows:
040817 11:00:24  InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File name ./tmp/#sql18ac_1e_0.ibd
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.
-e 
Number of processes running now: 0
040817 11:00:25  mysqld restarted
040817 11:00:25  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
040817 11:00:27  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 15 49286162.
InnoDB: Doing recovery: scanned up to log sequence number 15 49286162
InnoDB: Last MySQL binlog file position 0 6742305, file name ./catarina-bin.000017
040817 11:00:27  InnoDB: Flushing modified pages from the buffer pool...
040817 11:00:27  InnoDB: Started; log sequence number 15 49286162
/centia01/final/database/mysql/bin/mysqld: ready for connections.
Version: '4.1.3-beta-max-log'  socket: '/tmp/mysql.sock'  port: 3306

It seems that innodb create the temp file with a coded name and then it cant find it or something like that.

How to repeat:
Use innodb as default table type
Use innodb_file_per_table
> create temporary table test(id int);

Thank you for the bug report. I was able for to repeat on Windows with
a server 4.1.3 and latest 4.1.4.

Version: '4.1.4-beta-debug-log'  socket: ''  port: 3306  Source distribution
040821  3:14:19  InnoDB: Operating system error number 3 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: File name .\temp\#sqlf48_1_0.ibd
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

Hi!

Thank you for finding this important bug. I was able to repeat this on Linux, too.

Workaround: in Linux create a directory called

tmp

under the datadir of MySQL, or in Windows create a directory called temp under the datadir. Then InnoDB will place the .ibd file of a temporary table into that directory. Or use the MyISAM table type for temporary tables.

I am looking at fixing the bug. MyISAM creates a temporary table in the temporary file directory of the OS. Putting the .ibd file there is one possible solution.

But I do not like the fact that the OS can remove files from the temp file directory at its own will. Then the internal data directory of InnoDB would get out of sync with what .ibd files really exist. Of course, MySQL always creates the .frm file for the temp table in the temp file directory, and the problem exists anyway.

Another solution is that InnoDB automatically creates a database called 'tmp' under the datadir of MySQL and puts the .ibd file there. That would be a cleaner solution from the viewpoint of consistency.

I have to think more about this.

Thank you,

Heikki

Fix pushed to 4.1.6. InnoDB now creates the .ibd file for the table to the temp dir of mysqld. In Unix this is usually /tmp and in Windows /temp.

Thank you,

Heikki