'MySQL LOAD_FILE() loads null values

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

mysql

Solution 1:^[1]

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

Solution 2:^[2]

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

Solution 3:^[3]

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.

Solution 4:^[4]

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

Solution 5:^[5]

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

Solution 6:^[6]

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

Solution 7:^[7]

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/

Solution 8:^[8]

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 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=""

Solution 9:^[9]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2
Solution 3	Acubo
Solution 4	arete
Solution 5	r0m4n
Solution 6	Tushar
Solution 7
Solution 8	MEDZ
Solution 9	Skocdopole

'MySQL LOAD_FILE() loads null values

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]