MySQL LOAD_FILE returns NULL

Question

I want to get SQL LOAD_FILE function to work and have read every single question/answer + documentation about this, but here is what's been happening.

When I want to LOAD_FILE from my home directory:

mysql> SELECT LOAD_FILE('/home/myuser/somefile.txt');
+----------------------------+
| LOAD_FILE('/home/myuser/somefile.txt') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

So after getting this, I thought maybe the problem is that MySQL cannot access my home directory. And I tried running this, which worked fine:

SELECT LOAD_FILE('/etc/mysql/my.cnf');

Then SELECT LOAD_FILE('/etc/passwd'); worked fine as well.

So I said gotcha, it's a file/folder read/ownership permission problem. So, I moved my file into /etc/mysql/ but then it still didn't work. I've tried chown mysql:mysql somefile.txt but still, I had no luck:

mysql> SELECT LOAD_FILE('/etc/mysql/somefile.txt');
+----------------------------+
| LOAD_FILE('/etc/mysql/somefile.txt') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

P.S. 1. All files are readable by all user groups, so no need to chmod. But I've even tried chmod 777 if you'd like to ask. 2. I checked, secure-file-priv variable is not set up in MySQL, so no, LOAD_FILE is not restricted to any path.

Any ideas what might be the problem here?

@Rahul Yes, it does. I put a sentence or two in it. Less than 2 KB in total. — Emir
– Emir, Commented Jun 27, 2014 at 19:09

Emir · Accepted Answer · 2014-06-27 23:06:27Z

9

I found out that it has to do with AppArmor. I disabled AppArmor for MySQL and it worked. For people having the same problem, please read here: http://www.cyberciti.biz/faq/ubuntu-linux-howto-disable-apparmor-commands/

answered Jun 27, 2014 at 23:06

Emir

7722 gold badges8 silver badges22 bronze badges

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

2 Comments

John Over a year ago

Alternate steps to disable apparmor that worked for me on Linux Mint 18 - this stops apparmor service from running ever again, but doesn't delete apparmor package from the servers: # /etc/init.d/apparmor stop # /etc/init.d/apparmor teardown # update-rc.d -f apparmor remove # service apache2 restart # service mysql restart

Payel Senapati Over a year ago

If placed in original folder of select @@GLOBAL.secure_file_priv; (in my case it was - /var/lib/mysql-files) instead of changing it, then apparmor disabling is not needed

Community · Accepted Answer · 2017-05-23 11:46:04Z

5

Per Documentation

To use this function, the file must be located on the server host.

You must specify the full path name to the file.

You must have the FILE privilege.

The file must be readable by all

File size should be 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.

So check all the above mentioned condition satisfies.

EDIT:

Not sure whether you understood properly ..

make sure the file parent directory have execute permission. So if somefile.txt sits under myuser directory; you must have execute have permission on myuser directory.
You must have the FILE privilege. means the FILE privilege must be granted explicitly using GRANT FILE on . TO user@localhost
Flush the privilege
Logout and Login back and check whether it's working or not.

See this post MySQL LOAD_FILE() loads null values

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Jun 27, 2014 at 19:14

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

6 Comments

Emir Over a year ago

yes, I've read the documentation and checked all above conditions in advance. I'm pretty sure the file cannot be read (related to the last condition), but I don't know why. It just cannot read the file for some reason.

Rahul Over a year ago

That's great. What is the value of SHOW VARIABLES LIKE 'max_allowed_packet'?

Emir Over a year ago

Rahul Over a year ago

@Emir, that fine then. Check edited answer and varify those points

Emir Over a year ago

Answer for your EDIT: 1. Yes, the folder has execute permission. I've even tried putting this file under /etc/mysql/ which MySQL has all the access by default. 2. I'm logged into MySQL as root, so yes I've checked and have FILE privilege (and all other privileges).

|

Donald Pitchford · Accepted Answer · 2019-07-29 19:14:02Z

4

So I been banging my head against this issue with finally found to to resolve it:

mysql> select LOAD_FILE('/var/www/upload/test.zip');
+---------------------------------------+
| LOAD_FILE('/var/www/upload/test.zip') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set (0.00 sec)

The problem is the secure_file_priv is not set. To see where it is pointed to do the following query:

Perform query:

select @@GLOBAL.secure_file_priv;

To point to the area that you are looking to pull file What I did was to add the following to the end mysqld.conf

secure_file_priv =  /var/www/upload.

Restarted mysql and test with

mysql> select LOAD_FILE('/var/www/upload/test.zip');
+--------------------------------------------------------------------------------------

answered Jul 29, 2019 at 19:14

Donald Pitchford

412 bronze badges

2 Comments

JerseyDevel Over a year ago

This was it for me. Only files in this location could be loaded into the database. Thanks.

Payel Senapati Over a year ago

You have to add secure_file_priv = /var/www/upload exactly under [mysqld] line in the file /etc/mysql/mysql.conf.d/mysqld.cnf and not at the "end", this incomplete information made me waste a few hours, hope I helped somebody Also the answer provided by @Emir has to be followed at the same time, that is turnoff "apparmor" for "mysql"

exg · Accepted Answer · 2015-12-08 00:36:05Z

2

I'd the same problem with Fedora Linux while trying add images to a blob field.

I solved coping the files to the directory /var/lib/mysql/images and giving the command:

chown -R mysql:mysql /var/lib/mysql/images

answered Dec 8, 2015 at 0:36

exg

1336 bronze badges

Comments

user3741598 · Accepted Answer · 2014-06-27 20:01:15Z

0

Load data infile file_name... is the proper command for MySQL. And since you didn't give an example of the data in your file I can't show you know to exactly use it. There are variables to handle delimiters and end of line characteristics. Good luck.

answered Jun 27, 2014 at 20:01

user3741598

2951 silver badge12 bronze badges

Collectives™ on Stack Overflow

MySQL LOAD_FILE returns NULL

5 Answers 5

2 Comments

6 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

6 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related