8

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

Community
  • 1
  • 1
arete
  • 1,903
  • 4
  • 17
  • 23

9 Answers9

11

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

serge3ling
  • 111
  • 2
  • 5
8

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
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Julien Lamarche
  • 931
  • 1
  • 12
  • 29
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.

Acubo
  • 120
  • 6
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/*
arete
  • 1,903
  • 4
  • 17
  • 23
  • 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. – user208145 Oct 17 '16 at 02:49
2

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

r0m4n
  • 3,474
  • 3
  • 34
  • 43
0

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

Tushar
  • 85,780
  • 21
  • 159
  • 179
Sawssen BENNACEUR
  • 1
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/

Arturo Seijas
  • 194
  • 1
  • 8
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 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=""
MEDZ
  • 2,227
  • 2
  • 14
  • 18
Frank Salinas
  • 1
0

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.

Dan Macak
  • 16,109
  • 3
  • 26
  • 43