MySQL LOAD_FILE() loads null values

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

Halberd

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

Renettarenew

@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. – Renettarenew

Did you look at some of the other answers below? MySQL has changed since 2004. – Renettarenew

Answer 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

Sanorasans

You saved a lot of time! – Gingersnap

Answer 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.

Communist

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

Halberd

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. – Towill

Answer 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

Dodecasyllable

Answer 6

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

Pachston

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

Franke

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

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

Phyllome

Answer 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.

Preposition