MySQL LOAD_FILE returns NULL

Question

I want to get SQL LOAD_FILE function to work and have read every single question/answer + documentation about this, but here is what's been happening.

When I want to LOAD_FILE from my home directory:

mysql> SELECT LOAD_FILE('/home/myuser/somefile.txt');
+----------------------------+
| LOAD_FILE('/home/myuser/somefile.txt') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

So after getting this, I thought maybe the problem is that MySQL cannot access my home directory. And I tried running this, which worked fine:

SELECT LOAD_FILE('/etc/mysql/my.cnf');

Then SELECT LOAD_FILE('/etc/passwd'); worked fine as well.

So I said gotcha, it's a file/folder read/ownership permission problem. So, I moved my file into /etc/mysql/ but then it still didn't work. I've tried chown mysql:mysql somefile.txt but still, I had no luck:

mysql> SELECT LOAD_FILE('/etc/mysql/somefile.txt');
+----------------------------+
| LOAD_FILE('/etc/mysql/somefile.txt') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

P.S. 1. All files are readable by all user groups, so no need to chmod. But I've even tried chmod 777 if you'd like to ask. 2. I checked, secure-file-priv variable is not set up in MySQL, so no, LOAD_FILE is not restricted to any path.

Any ideas what might be the problem here?

Answer 1

Per Documentation

To use this function, the file must be located on the server host.

You must specify the full path name to the file.

You must have the FILE privilege.

The file must be readable by all

File size should be less than max_allowed_packet bytes.

If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

So check all the above mentioned condition satisfies.

EDIT:

Not sure whether you understood properly ..

1. make sure the file parent directory have execute permission. So if somefile.txt sits under myuser directory; you must have execute have permission on myuser directory.

2. You must have the FILE privilege. means the FILE privilege must be granted explicitly using GRANT FILE on . TO user@localhost

3. Flush the privilege

4. Logout and Login back and check whether it's working or not.

See this post MySQL LOAD_FILE() loads null values

Answer 2

I found out that it has to do with AppArmor. I disabled AppArmor for MySQL and it worked. For people having the same problem, please read here: http://www.cyberciti.biz/faq/ubuntu-linux-howto-disable-apparmor-commands/