MySQL: LOAD_FILE returns null

I’m running a web application in a remote Ubuntu machine (using DigitalOcean, which I recommend). I’ve installed MySQL, and everything seems to work OK.

But, at some point I wanted to upload some images inside a blob column of a table, so I needed to use the LOAD_FILE function. But the problem was that it always returns null.

I tried a lot of things:

  • grant FILE permissions to the database user
  • give execution permissions to the folder who contains the images
  • change the owner of the images to mysql
  • etc..

but nothing seems to work..

Finally, I found the solution, and is related with AppArmor.

First approach

Type the following command to see the list of profiles currently loaded:

$ cat /sys/kernel/security/apparmor/profiles

Sample output:

/usr/sbin/tcpdump (enforce)
/usr/sbin/mysqld (enforce)
/usr/lib/connman/scripts/dhclient-script (enforce)
/usr/lib/NetworkManager/nm-dhcp-client.action (enforce)
/sbin/dhclient (enforce)

So, we should disable the mysql profile:

sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

Now, LOAD_FILE should be working.

To re-enable apparmor protection for mysql again:

sudo rm /etc/apparmor.d/disable/usr.sbin.mysqld
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

Second approach

Edit the local apparmor security profile for mysql inside /etc/apparmor.d/local directory, and add the directory who contains the images:

nano /etc/apparmor.d/local/usr.sbin.mysqld
melorriaga@ubuntu:/etc/apparmor.d/local$ more usr.sbin.mysqld
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/folder/who/contains/images/** r

Now, LOAD_FILE should be working.

MySQL: LOAD_FILE returns null

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s