Problem with LOAD_FILE

[richpri]

I am having a problem getting LOAD_FILE to work on my local MYSQL server.
I researched this problem guided by this manual entry.

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. 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.

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

I am indeed getting a return of NULL as can be seen here:

mysql> select LOAD_FILE('/home/rich/NetBeansProjects/BOARD18/data/testBox.json');
+--------------------------------------------------------------------+
| LOAD_FILE('/home/rich/NetBeansProjects/BOARD18/data/testBox.json') |
+--------------------------------------------------------------------+
| NULL |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

The file permissions look as follows:

rich@rich2:~$ ls -l NetBeansProjects/BOARD18/data/
total 8
-rw-rw-r-- 1 rich  mysql 1452 Sep 22 07:41 testBox.json
-rw-rw-r-- 1 rich  mysql  378 Sep 22 14:15 testSession.json
rich@rich2:~$

My MYSQL version is:

mysql> select @@version;
+-------------------------+
| @@version |
+-------------------------+
| 5.5.24-0ubuntu0.12.04.1 |
+-------------------------+
1 row in set (0.00 sec)

I checked the variables mentioned above and they seem ok:

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
| 16777216 |
+----------------------+
1 row in set (0.02 sec)

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.01 sec)

And the FILE privilege has been granted:

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

I am at a loss as to what else to check. Any suggestions?

 

[DonQuichote]

This is an Ubuntu issue. For security reasons, AppArmour does not allow the database server to access just any files. So you will have to dive into AppArmour to enable this on Ubuntu.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[richpri]

Thanks for pointing me in the correct direction. I edited the local apparmor security profile for
mysql in the /etc/apparmor.d/local directory. I added one line as shown below.

rich@rich2:/etc/apparmor.d/local$ more usr.sbin.mysqld
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/home/rich/NetBeansProjects/** r,
rich@rich2:/etc/apparmor.d/local$

and now LOAD_FILE works fine:

mysql> select LOAD_FILE('/home/rich/NetBeansProjects/BOARD18/data/test.text');
+-----------------------------------------------------------------+
| LOAD_FILE('/home/rich/NetBeansProjects/BOARD18/data/test.text') |
+-----------------------------------------------------------------+
| This is a test file.                                            |
+-----------------------------------------------------------------+
1 row in set (0.02 sec)

MYSQL should now be able to access all files and directories under the /home/rich/NetBeansProjects directory.