Can't create/write to file '#sql_1fc7_3.MYD' (Errcode: 17)

Question

Can't create/write to file '#sql_1fc7_3.MYD' (Errcode: 17)

how can i fix that?

Answer 1

Error 17 is "file exists".

See if anything else has #sql_1fc7_3.MYD open:

lsof | grep '#sql_1fc7_3.MYD'

If it doesn't, then move it. I'm not sure what led to this problem, but one guess is that MySQL crashed at some point and as a result didn't clean up a temp table's file named #sql_1fc7_3.MYD.

Answer 2

Python 2.6 (r26:66714, Nov  3 2009, 17:33:38) 
[GCC 4.4.1 20090725 (Red Hat 4.4.1-2)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import errno
>>> errno.errorcode[17]
'EEXIST'
>>> 

Are you trying to create a table post-restore or something like that? What commands are you trying to execute that cause that error?

Answer 3

A few possibilities:

1) the file already exists from a previously attempted repair/alter table that failed or is still in progress (show processlist)

2) your system is set to write temporary tables to /tmp which is not chmod 1777 (or /var/tmp)

3) your mysql database directory is not owned by the process that is running mysql (or mysqld doesn't have privileges to write to that database directory.

Answer 4

I have seen this error several times. I'm not sure how this happens, but in my case it's caused by the /tmp/ directory on the database server suddenly becoming only writable by the root user (as suggested by the previous answerer). I have no clue why this keeps happening, but I set it back via "chmod 777 /tmp/" and the error immediately goes away.