MySQL CREATE TEMPORARY TABLE Error

Question

I'm running EasyPHP 5.4.6 with MySQL 5.5.27-log.

When I try to create a temporary table using the following:

CREATE TEMPORARY TABLE temp_table (x int);

I get the following error:

ERROR 1005 (HY000): Can't create table 'temp_table' (errno: 22)

However, it works if I remove the "TEMPORARY".

The syntax for the temporary table was copied verbatim from an example page. I tried googling the error numbers, but that yielded nothing I could use.

Can anyone think of what may be going on?

Which OS are you using? Can you show your mysql configuration file? — Jonathan Amend
– Jonathan Amend, Commented Nov 29, 2012 at 22:11
I am not sure about this: mysql> CREATE TEMPORARY TABLE temp_table (count int); Query OK, 0 rows affected (0.11 sec) mysql> — user1092803
– user1092803, Commented Nov 29, 2012 at 22:13
That's what I was afraid of. It looks like a bug. I really don't want to install a different stack :( — RonaldBarzell
– RonaldBarzell, Commented Nov 29, 2012 at 22:14
I'm looking for it. It's not in my installation folder. The O/S is WinXP. — RonaldBarzell
– RonaldBarzell, Commented Nov 29, 2012 at 22:17

Jonathan Amend · Accepted Answer · 2012-11-29 22:29:48Z

1

errno: 22 indicates that MySQL is trying to access a file with an invalid path (fopen -> INVAL). MySQL is not likely to generate an invalid path, so check that your temp path is set correctly. If you have a custom set tmpdir in your MySQL configuration, make sure it's a valid path. If not, check the current value using SHOW VARIABLES LIKE '%tmp%'; If something is incorrect or missing, check that your system's TEMP environment variable is set properly. For more info, check http://dev.mysql.com/doc/refman/5.5/en/temporary-files.html.

answered Nov 29, 2012 at 22:29

Jonathan Amend

12.8k3 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RonaldBarzell Over a year ago

That did the trick. The my.ini did not have a tmpdir variable, so I just added one and set it to a path. Now everything works. Thanks!

Jonathan Amend Over a year ago

Glad I could help. I would still double-check why your system's TEMP path is not set properly though; this might cause you further headaches in the future.

RonaldBarzell Over a year ago

Once I get the chance, I'll look into it.

user1092803 · Accepted Answer · 2012-11-29 22:15:43Z

0

Make sure the user you are using has the create_tmp_table_priv privelege granted (table user of the mysql database)

answered Nov 29, 2012 at 22:15

user1092803

3,3072 gold badges37 silver badges36 bronze badges

1 Comment

RonaldBarzell Over a year ago

The user does. The user is root :). But I also manually checked the privilege table just in case.

Collectives™ on Stack Overflow

MySQL CREATE TEMPORARY TABLE Error

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related