MySQL LOAD_FILE() loads null values [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed last month.

Improve this question

I'm attempting to load a directory of xml files into a database table as blobs. Each row would contain a corresponding blob file. When loading files via a python script into the table, values are inserted as null. This is also the case when running an equivalent command in the mysql command line.

At one point I was able to insert values after changing some folder permissions to mysql, but due to needed scripting privileges I had to modify ownership of the directory /var/lib/mysql/foo, thus values are being reinserted as null effectively breaking the script I wrote. I cannot remember the necessary change of directory permissions to do so.

The following are discussions of the topic:

http://bugs.mysql.com/bug.php?id=38403

along with

MySQL LOAD_FILE returning NULL

serge3ling · Accepted Answer · 2018-07-14 16:34:34Z

12

I copied my file to the location where MySQL has access to.
To know the location I used:

select @@secure_file_priv;

and it gave me /var/lib/mysql-files/.

Nothing else worked: neither turning off apparmor, nor changing ownership and permissions, nor merely granting the file privilege. So I rolled back most of that but the right directory still works. In my case.

My source is Raymond Nijland, here: https://dba.stackexchange.com/questions/190380/load-file-producing-null

edited Jul 14, 2018 at 16:34

answered Jul 12, 2018 at 8:42

serge3ling

1212 silver badges5 bronze badges

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

1 Comment

Pramod Sharma Over a year ago

You saved a lot of time!

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

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 '*xxxx' |
| 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
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

@Acubo 's answer below offers an additional clue. My answer was written back in 2014, it's possible the platform's default configuration has changed since.
Did you look at some of the other answers below? MySQL has changed since 2004.

Acubo · Accepted Answer · 2017-06-06 10:22:53Z

4

I have lost some time with this problem. In my case, the problem had to do with secure_file_priv variable defined in "my.ini".

From MySQL documentation:

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

So, there are two ways:

1)remove this option 2) use the default folder for uploading files ("C:\ProgramData\MySQL\MySQL Server 5.7\Uploads"). I also had to use double slash in Windows.

answered Jun 6, 2017 at 10:22

Acubo

1502 silver badges6 bronze badges

Comments

r0m4n · Accepted Answer · 2020-02-05 05:00:45Z

3

I had tried all the other answers here. After quite a bit of trial and error discovered that my issue was that secure-file-priv was not set to anything at all. When I set this value to a directory path in my.conf and ensured the permissions were set correctly, files could finally be accessed

add to my.conf:

secure_file_priv="/test/"

I think this answer is specific to mysql running on mac

answered Feb 5, 2020 at 5:00

r0m4n

3,5023 gold badges36 silver badges43 bronze badges

Comments

arete · Accepted Answer · 2013-08-14 20:29:17Z

2

In order for load file to work make sure that all permissions are granted for the MySQL owner and group.

chown mysql:mysql /var/lib/mysql/foo/*
chmod go+rw /var/lib/mysql/foo/*

answered Aug 14, 2013 at 20:29

arete

1,9494 gold badges17 silver badges25 bronze badges

1 Comment

user208145 Over a year ago

This was the last crucial step which finally allowed me to load the files I needed in the database. show VARIABLES indicated the secure_file_priv variable was set properly, but I still couldn't load the files.

Daniel Macak · Accepted Answer · 2022-04-02 17:18:25Z

I was running mysql 8.0.26 in a docker container and I checked all the conditions mentioned in the docs:

File privilege on my mysql user
secure_file_priv not set to any non-empty dir. I actually had it set to NULL
I didn't exceed max_allowed_packet with the file I tried to load
The file was located on the host
I specified full, correct path to the file

Plus I extra added

All the parent folders had at least 755 mod
The immediate parent folder belonged to user running mysql process

...and I still was getting NULL from select load_file('...');

I even tried to set the secure_file_priv explicitly to various locations in /home/..., while making sure all conditions are fulfilled, and still nothing.

Finally, setting the secure_file_priv to /var/lib/mysql-files worked. I created that folder myself to see if /var might be the right place, and even though root owns that folder, it still works.

Tushar · Accepted Answer · 2016-12-07 11:29:23Z

0

I copied the image in /var/lib/mysql/images ( after creating images folder) and it worked!

edited Dec 7, 2016 at 11:29

Tushar

87.4k21 gold badges164 silver badges182 bronze badges

answered Dec 7, 2016 at 11:27

Sawssen BENNACEUR

1

Comments

Arturo Seijas · Accepted Answer · 2018-05-16 08:39:36Z

0

It could be due to the SO security constraits. In fact, I was able to solve the problem by disabling the MySQL profile in AppArmour

The procedure I followed was to check whether there was a profile loaded for mysql

sudo aa-status

and then disabling it

sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

https://www.cyberciti.biz/faq/ubuntu-linux-howto-disable-apparmor-commands/

edited May 16, 2018 at 8:39

answered May 15, 2018 at 12:54

Arturo Seijas

3022 silver badges8 bronze badges

1 Comment

Ugnius Malūkas Over a year ago

Please provide what he needs to do here, not just a link.

thundernhut · Accepted Answer · 2024-09-16 00:16:09Z

0

How can you all set null this variable ? When you set it in mySQL:

set secure_file_priv="";

Then it replies: 07:14:01 set secure_file_priv="" Error Code: 1238. Variable 'secure_file_priv' is a read only variable 0.000 sec

answered Sep 16, 2024 at 0:16

thundernhut

113 bronze badges

1 Comment

Nico Haase Over a year ago

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review

mimidatabase · Accepted Answer · 2025-07-27 06:57:14Z

0

Acubo solved my issue with the Null Blob inserts. In the MySql my.ini file, the secure-file-priv was set to a specific folder that I do not use, that is why it ignored my path in the LOAD_FILE. Acubo said to make sure the secure-file-priv setting is set to empty. It will then use the hardcoded path in the LOAD_FILE statement.

my.ini

Secure File Priv. Modified by Frank Salinas 12/11/2019 secure-file-priv must be set to an empty string (different from NULL) in order to query directories other than the hard coded directory

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
secure-file-priv=""

edited Jul 27 at 6:57

mimidatabase

1331 silver badge5 bronze badges

answered Dec 16, 2019 at 17:07

Frank Salinas

1

Comments

Idris Shining-Times · Accepted Answer · 2025-10-25 13:49:24Z

I know I'm late, but to keep my fellow Windows users updated, and thanks to r0m4n's answer, check (assuming your image files are not too large, and are readable):

Your MySQL user has been granted FILE permission. root@localhost is always granted FILE permission with others by default.
Do SHOW VARIABLES LIKE 'secure_file_priv'; (when you're logged in MySQL), this will show the value assigned to that variable that is present in my.ini
If the variable is set to NULL, it means that variable is not declared in the mentioned file. So, you have to set the variable to the directory where you will upload your images.
To do so, first open Command Prompt in Administrator mode. Then type the command mysqld --verbose --help | findstr "basedir". This finds the base directory where tools of MySQL are present, including my.ini. Go to that base directory
Open my.ini in Notepad, and add secure_file_priv=C:\\path\\to\\folder under [mysqld]. For example, in my case I did secure_file_priv=C:\\mysql-files.
Hit save, then in the same (Administrator) Command Prompt, do net stop mysql then net start mysql.
Now you can do SHOW VARIABLES LIKE 'secure_file_priv' and it will not be NULL now.
You can then store your image(s) under the path you provided, and cross-check through SELECT LENGTH(LOAD_FILE(...)) to see if everything's working or not

Collectives™ on Stack Overflow

MySQL LOAD_FILE() loads null values [closed]

11 Answers 11

1 Comment

2 Comments

Comments

Comments

1 Comment

Comments

Comments

1 Comment

1 Comment

Comments

Comments

Hot Network Questions