MySQL LOAD_FILE returning NULL

php mysql mysql-loadfile

15,166

Solution 1

Looks like some versions of MySQL on Linux distributions have a bug with the LOAD_FILE function. Here is the thread. At the very end of the thread looks like there might be a workaround.

EDIT:

Since you're on a shared host, looking at the functionality you want could you just read the file instead? Using the file() should read the file into an array format.

Solution 2

A really ugly workaround can be found here:

http://angkatbahu.blogspot.com.es/2011/03/mysql-loadfile-function-in-ubuntu-it_12.html

If you put the files you want to load in /var/lib/mysql/all_images directory, it works!

Tested in ubuntu 12.10 (and no, chomd'ing files to mysql user, didn't work)

Solution 3

TO see what privileges you have, use show grants.

I have documented some additional commands you can use to check if you meet the conditions here:

http://pastebin.com/Dvsdxh9Y

Addendum I would make to the documentation. Make sure:

there is execute permission on the parent directory
The FILE privilege must is explicily granted. (GRANT FILE on . TO user@localhost)
You have flushed privileges
You have logged out and logged back in

Example of permission on parent dir:


mysql> \! ls -ld `dirname /home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg`
drwxrwxr--. 2 jlam jlam 4096 May 12 14:22 /home/jlam/code/projectName/doc/filesForTesting/images

mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/image

Test01.jpg'));
+-------------------------------------------------------------------------------------------------------------+
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg')) |
+-------------------------------------------------------------------------------------------------------------+
| NULL                                                                                                        |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> \! chmod o+x /home/jlam/code/projectName/doc/filesForTesting/images
mysql> \! ls -ld `dirname /home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg`
drwxrwxr-x. 2 jlam jlam 4096 May 12 14:22 /home/jlam/code/projectName/doc/filesForTesting/images
mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))                                                                                                                                
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| FFD8FFE1001845786966000049492A00080000000000000000000000FFEC00114475636B7900010004000000500000FFE10407687474703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F003C3F787061636B657420626567696E3D22EFBBBF222069643D2257354D304D7043656869487
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Example of user privileges:


16:38:09 (getImages) ~/code/projectName/doc/filesForTesting/images$ mysql -u eventCal -p eventCal
Enter password: 

mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for eventCal@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'eventCal'@'localhost' IDENTIFIED BY PASSWORD '*xxxxx' |
| GRANT ALL PRIVILEGES ON `tmp`.* TO 'eventCal'@'localhost'                                                       |
| GRANT ALL PRIVILEGES ON `eventCalTesting`.* TO 'eventCal'@'localhost'                                           |
| GRANT ALL PRIVILEGES ON `eventCal`.* TO 'eventCal'@'localhost'                                                  |
| GRANT ALL PRIVILEGES ON `eventCal_categoryMigration`.* TO 'eventCal'@'localhost'                                |
+-----------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+-------------------------------------------------------------------------------------------------------------+
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg')) |
+-------------------------------------------------------------------------------------------------------------+
| NULL                                                                                                        |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In other root session:


mysql> grant file ON *.*  to eventCal@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Back in user session, I still can't load the file


mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+-------------------------------------------------------------------------------------------------------------+
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg')) |
+-------------------------------------------------------------------------------------------------------------+
| NULL                                                                                                        |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

.....But if I log out and back in:


mysql> exit
Bye

16:40:14 (getImages) ~/code/projectName/doc/filesForTesting/images$ mysql -u eventCal -p eventCal
Enter password: 

mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))                                                                                                                                
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| FFD8FFE1001845786966000049492A00080000000000000000000000FFEC00114475636B7900010004000000500000FFE10407687474703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F003C3F787061636B657420626567696E3D22EFBBBF222069643D2257354D304D7043656869487
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

15,166

Author by

Simon

Updated on June 21, 2022

Comments

Simon about 2 years
I'm doing this SELECT LOAD_FILE("/home/user/domains/example.com/public_html/robots.txt") AS tmp FROM tmpTable but it returns NULL. How can I check if this is because I haven't got the FILE privilege or if it's something else? MySQL won't give an error. (I'm using PHP)

Anyone that has experience with LOAD_FILE, tell me about that function:)
```
<?php
$result = mysql_query('SELECT LOAD_FILE("/home/user/domains/example.com/public_html/robots.txt") AS tmp FROM tmpTable') or die(mysql_error());
while($row = mysql_fetch_assoc($result))
{
var_dump( $row['tmp'] );
}
```
Randall Cook over 11 years

While the workaround is ugly, I found that you don't have to put your images in /var/lib/mysql/all_images. I used /var/lib/mysql/foo_images and it worked fine. Thanks to this, I had my database schema installer (a Debian package) create /var/lib/mysql/foo_images, fill it with image files, then in my postinst I run my SQL full of LOAD_FILEs and then delete /var/lib/mysql/foo_images. The end result is perfectly clean with minimal chance of messing up the MySQL installation, and the intermediate stages weren't too bad either. I am satisfied.
ivo Welch over 8 years

in addition, on ubuntu linux and other places, apparmor may interfere.
ivo Welch over 8 years

the reason why the other directories failed was the apparmor block
Arto Bendiken over 8 years

This tip worked for me on Ubuntu 14.04 LTS as well. Namely, put your file under the /var/lib/mysql/ directory, and then use relative filenames, e.g., LOAD_FILE('foobar.txt') will load /var/lib/mysql/foobar.txt.