MySQL LOAD_FILE returns NULL
I'd the same problem with Fedora Linux while trying add images to a blob field.
I solved coping the files to the directory /var/lib/mysql/images
and giving the command:
chown -R mysql:mysql /var/lib/mysql/images
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/
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 ..
make sure the file parent directory have execute permission. So if
somefile.txt
sits undermyuser
directory; you must have execute have permission onmyuser
directory.You must have the FILE privilege.
means the FILE privilege must be granted explicitly usingGRANT FILE on . TO user@localhost
Flush the privilege
Logout and Login back and check whether it's working or not.
See this post MySQL LOAD_FILE() loads null values
So I been banging my head against this issue with finally found to to resolve it:
mysql> select LOAD_FILE('/var/www/upload/test.zip');
+---------------------------------------+
| LOAD_FILE('/var/www/upload/test.zip') |
+---------------------------------------+
| NULL |
+---------------------------------------+
1 row in set (0.00 sec)
The problem is the secure_file_priv is not set. To see where it is pointed to do the following query:
Perform query:
select @@GLOBAL.secure_file_priv;
To point to the area that you are looking to pull file What I did was to add the following to the end mysqld.conf
secure_file_priv = /var/www/upload.
Restarted mysql and test with
mysql> select LOAD_FILE('/var/www/upload/test.zip');
+--------------------------------------------------------------------------------------