MySQL Bugs: #38003: Cannot create temporary table using InnoDB engine with innodb_file_per

Bug #38003	Cannot create temporary table using InnoDB engine with innodb_file_per_table
Submitted:	9 Jul 2008 22:21	Modified:	10 Aug 2008 18:06
Reporter:	Chetan Sarva	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.51a-3ubuntu5.1-log	OS:	Linux (Ubuntu 8.04 (Hardy))
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb, temporary table

Description:
Creating a temporary table of type InnoDB fails when innodb_file_per_table is enabled. 

tmpdir is located on a physical partition, not tmpfs, however I believe it is related to the following change/bug from the 5.0.51 release notes:

Creating a temporary table with InnoDB when using the one-file-per-table setting, and when the host filesystem for temporary tables was tmpfs, would cause an assertion within mysqld. This was due to the use of O_DIRECT when opening the temporary table file. (Bug#26662)

It was previously working in 5.0.38. 

How to repeat:
Edit my.cnf:
innodb_file_per_table = 1

then -

mysql> CREATE TEMPORARY TABLE _t ( `asdf` VARCHAR(255) ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table '/var/tmp/#sql1869_7_1.frm' (errno: -1)

Thank you for the report.

I can not repeat described behavior. Please specify accurate version of MySQL package you use (file name). Also please check error log if there is any error.

mysql> select version();
+------------------------+
| version()              |
+------------------------+
| 5.0.51a-3ubuntu5.1-log |
+------------------------+

Package used: 

mysql-server-5.0_5.0.51a-3ubuntu5.1_i386.deb 
md5sum: c6257e88d36a38e4c6832a9340bc7784

There are no other errors in the error log.

Thank you for the feedback.

We don't provide Ubuntu binaries. Where did you download this package?

I am using the official Ubuntu package from Ubuntu 8.04 Hardy Heron. 

http://packages.ubuntu.com/hardy/mysql-server-5.0

Thank you for the link.

Could you please try in your environment MySQL binaries also?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I've just run into exactly the same problem using the same ubuntu package:

CREATE TEMPORARY TABLE a (i INT) ENGINE = INNODB;

Do you have a solution other than upgrading?

This just mysteriously started working for me. The only thing I can think of that I did that could have affected it was that I restarted apparmor. The MySQL profile in that inherits the ability to write to /tmp anyway, so I can't see that it should have been a problem, but whatever, it's working now!