Can't get load_file() working in mysql

Question

I've been struggling for 2 nights now to get load_file() to work, but the results are NULL. I run version "5.6.19-0ubuntu0.14.04.1".

Example:

mysql> show variables like '%secure%';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| secure_auth      | ON          |
| secure_file_priv | /root/load/ |
+------------------+-------------+
mysql> show variables like 'max_allowed%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+

mysql> desc xmlDocs;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| fileName    | varchar(30) | NO   | PRI | NULL    |       |
| server      | varchar(20) | NO   |     | NULL    |       |
| doc_content | blob        | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

mysql> insert into xmlDocs values ('test','test',load_file('/root/load/test.xml'));
ERROR 1048 (23000): Column 'doc_content' cannot be null

File permissions:

drwxrwxr-x 5 mysql mysql  4096 Nov 24 08:18 .
drwx------ 6 root  root   4096 Nov 24 08:33 ..
drwxr--r-- 5 root  root   4096 Nov 22 16:24 EU1
drwxr--r-- 5 root  root   4096 Nov 22 16:26 server
-rwxrwxrwx 1 mysql mysql 83440 Nov 24 08:18 test.xml
drwxr--r-- 5 root  root   4096 Nov 22 16:24 US1

Checked:

• MySql has execute and even owns the dir
• Mysql ows the file
• DB user = root
• Filesize < max_allowed_packets
• secure-file-priv is set
• I have no apparmor running

Without setting secure-file-priv it can read non important data like /etc/passwd effortlessly :P. Also from "/" i can import, but nowhere else. When setting secure-file-priv, I can get it to work from "/" only!

Same file, no secure-file-priv set:

mysql> insert into xmlDocs values ('test','test',load_file("/root/load/test.xml"));
ERROR 1048 (23000): Column 'doc_content' cannot be null
mysql> insert into xmlDocs values ('test','test',load_file("/etc/test.xml"));
Query OK, 1 row affected (0.00 sec)

Any ideas?

Answer 1

Typical ... been looking for 2 days and 5 minutes after posting it here i figured it out.

The file to be loaded, not just the folder it's located in must have execute ... EVERY folder in it's path up to "/" must have execute!