MySQL LOAD_FILE returning NULL

Question

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'] );
}

have you granted the user with the permissions? GRANT FILE ON . TO me; — Phill Pafford
– Phill Pafford, Commented Jan 5, 2011 at 18:19
Also I've heard the LOAD_FILE is a Windows only function, mot Linux: forums.mysql.com/read.php?20,218333 — Phill Pafford
– Phill Pafford, Commented Jan 5, 2011 at 18:21

Daniel Pecos · Accepted Answer · 2012-12-29 12:20:50Z

5

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)

answered Dec 29, 2012 at 12:20

Daniel Pecos

5815 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Randall Cook Over a year ago

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 a year ago

the reason why the other directories failed was the apparmor block

Arto Bendiken Over a year ago

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.

Phill Pafford · Accepted Answer · 2011-01-05 18:26:46Z

4

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.

answered Jan 5, 2011 at 18:26

Phill Pafford

85.5k92 gold badges268 silver badges385 bronze badges

Comments

Julien Lamarche · Accepted Answer · 2014-05-12 20:52:51Z

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
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

in addition, on ubuntu linux and other places, apparmor may interfere.

Collectives™ on Stack Overflow

MySQL LOAD_FILE returning NULL

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related