asked on

Mysql can't write to new tmpdir

I'm running a large query. (https://www.experts-exchange.com/questions/25747438/How-to-select-max-min-first-last-by-group-Aka-Can-my-program-become-a-short-query.html?anchorAnswerId=30091847#a30091847) which fails because I run out of disc space.

/mnt has lots of freespace.
I tried creating a new tmpdir

# mkdir /mnt/mysqltmp
# chown mysql /mnt/mysqltmp
# chmod 777 /mnt/mysqltmp
# ls -l /mnt/mysqltmp
drwx------ 2 root root 16384 2009-10-15 18:16 lost+found
drwxrwxrwx 2 mysql root 4096 2010-04-08 08:21 mysqltmp

Yet when I now run queries, I get:
ERROR 1 (HY000): Can't create/write to file '/mnt/mysqltmp/#sql_1628_0.MYI' (Errcode: 13)
# perror 13
OS error code 13: Permission denied

Why do I get permission denied?

ASKER CERTIFIED SOLUTION

cjl7

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arminel

Hi,

Your /mnt directory must have read and execute permissions for the application to get to /mnt/mysqltmp/ directory.

It's like this: you can't get to level 2 if you don't pass through level 1 first :)

letharion

ASKER

>Your /mnt directory must have read and execute permissions for the application to get to /mnt/mysqltmp/ directory.
Good point, I hadn't thought about this, but /mnt is also 777, so that's not it.

>Just mount your mysqltemp in /var/lib/mysql instead and you should be smiling.
Yes!
mount --bind /mnt/mysqltmp /var/lib/mysql/mysqltmp
fixed it. I am smiling ^^
Ty