Conditions for LOAD_FILE to work

From http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_load-file:

"To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size 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."

### Condition 1 #################################################
"the file must be located on the server host, "

I'm running on same host:

mysql> \! hostname && ls /home/jlam/testImage.jpg
dev1
/home/jlam/testImage.jpg

15:05:34 ~$ hostname; ls $PWD/testImage.jpg
dev1
/home/jlam/testImage.jpg


### Condition 2 #################################################
* you must specify the full path name to the file,

(see example below)


### Condition 3 #################################################

* and you must have the FILE privilege.

mysql> show grants;
+-------------------------------------------------------+
| Grants for test@localhost                             |
+-------------------------------------------------------+
| GRANT FILE ON *.* TO 'test'@'localhost'               |
| GRANT ALL PRIVILEGES ON `tmp`.* TO 'test'@'localhost' |
+-------------------------------------------------------+
2 rows in set (0.00 sec)


### Condition 4 #################################################

* The file must be readable by all and

mysql> \! ls -l /home/jlam/testImage.jpg
-rw-rw-r--. 1 jlam jlam 440418 May 12 15:01 /home/jlam/testImage.jpg



### Condition 5 #################################################

* its size less than max_allowed_packet bytes.

mysql> show variables like '%max_allow%'
    -> ;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 16777216   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.00 sec)

mysql> \! ls -l /home/jlam/testImage.jpg
-rw-rw-r--. 1 jlam jlam 440418 May 12 15:01 /home/jlam/testImage.jpg

mysql> select 440418 < 16777216;
+-------------------+
| 440418 < 16777216 |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)



### Condition 6 #################################################

* 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.

mysql> show variables like '%secure%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_auth      | OFF   |
| secure_file_priv |       |
+------------------+-------+
2 rows in set (0.00 sec)



### Some addendums I would make #################################

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:

<pre>

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

</pre>


Example of user privileges:

<pre>

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 '*xxx' |
| 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)

</pre>


In other root session:

<pre>

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)

</pre>

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


<pre>

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)

</pre>


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

<pre>

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

</pre>