Mysql Unable to create / write error # 13 to file

Question

I created a Django view that returns some data after reading from MySql DB. When I try to find about 6,000 rows from the database, everything works fine, and the view returns an HttpResponse as expected. However, when I try to extract 7000+ lines I get the following error

(1, "Can't create/write to file '/home/nipun/mysql_temp/MYzplJok' (Errcode: 13)") 

I used to think that the error may be due to the fact that the space is exhausted for / temp, so I changed the tempdir setting in my.cnf
I also ensured that the new tmpdir / home / nipun / mysql_temp and its parent directories could be overwritten by me by changing ownership. Although this is not a Django problem, here is a point of view

 def query_json(request): from django.utils import simplejson objects=Publisher.objects.filter(location='ROOM_01',sensor_name='CPU_TEMPERATURE').order_by('-id')[0:9000] json = simplejson.dumps( [{"reading": float(o.reading), "timestamp": str(o.timestamp) } for o in objects] ) return HttpResponse(json,mimetype="application/json") 

So in the filter change 9000 - 6000 works fine.
For more error information, see the Django stack trace.

 errorclass <class '_mysql_exceptions.InternalError'> errorvalue InternalError(1, "Can't create/write to file '/home/nipun/mysql_temp/MYuotga9' (Errcode: 13)") error (<class '_mysql_exceptions.InternalError'>, InternalError(1, "Can't create/write to file '/home/nipun/mysql_temp/MYuotga9' (Errcode: 13)")) 

EDIT
According to the comment, I tried this in my MySQL prompt

 mysql> CREATE TEMPORARY TABLE t (i int); ERROR 1005 (HY000): Can't create table 't' (errno: 13) 

So basically this is a question about how to allow MySQL to write to a temporary directory

Answer 1

This is probably a permission issue for the temporary folder used by Mysql.

You can find the folder used by Mysql for temporary operations in the configuration file in /etc/mysql/my.cnf , as such:
tmpdir = /tmp
As you can see, the default temporary folder is "/ tmp".

Privileges must be:
drwxrwxrwt 9 root root 4096 oct. 24 15:39 tmp/ d
so anyone, including the mysql user, can use it. If it is not installed correctly, the following command will do the trick:
chmod 0777 /tmp

Hope this helps!

Answer 2

You have configured mysql tmpdir to point to a directory on which the server does not have write permission.

For small file arrays, MySQL uses a buffer in memory, but for larger files, it uses files on disk (in tmpdir).

In any case, this is a sysadmin error (and not a programming error) setting MySQL tmpdir to point to an unplayable directory.