Mysql Can't create/write to file error#13

Question

I had created a Django view which is returning some data after reading from MySql DB.When i try and fetch around 6000 rows from the Database,everything works fine and the view returns HttpResponse as is expected.However as i try to fetch 7000+ rows i get the following error

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

Earlier i thought that the error could be due to space getting exhausted for /temp,so i changed the tempdir setting in my.cnf
I also ensured that new tmpdir /home/nipun/mysql_temp and it's parent directories are writable by me by changing the ownership. Although this is not a Django problem,here is the 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 changing 9000 to 6000 works fine.
Some more information about the error is provided in 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
As per a comment, i tried this on my MySQL prompt

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

So it essentially is now an issue of how to allow MySQL to write to temporary directory

Answer 1

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

You can find the folder used by Mysql for temp operations in the config file in /etc/mysql/my.cnf as such :

tmpdir          = /tmp

As you can see, the temp folder in use by default is "/tmp".

Privileges should be :

drwxrwxrwt   9 root root  4096 oct.  24 15:39 tmp/

So anybody, including the "mysql" user, can use it. If not correctly set, the following command would do the trick :

chmod 0777 /tmp

Hope this helps !

Answer 2

If after giving necessary write permissions to the new tmpdir you're still having the same error - and mysql therefore can't start - you might have AppArmor enabled. So do :

sudo vim /etc/apparmor.d/local/usr.sbin.mysqld

Of course you can use any text editor other than vim eg nano. Note that usr.sbin.mysqld at the end corresponds to what you get by doing which mysql or which mysqld (with / replaced by .). When the file is open, you'll see some comments at the top like :

# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.

Then put after those comments :

/path/to/new/tmp/dir/ r,
/path/to/new/tmp/dir/** rwk,

And then do : sudo service apparmor reload. Now try starting mysql : sudo service mysql start and you should be good to go.