LOAD_FILE returns NULL

Question

I am trying to insert an image into my MySQL server. I have done some research and it looks like the best way to do that is through LOAD_FILE(). However, LOAD_FILE() always returns null. I know there are 4 conditions for LOAD_FILE():

1. The file must be located on the server host
2. You must specify the full path name to the file, and you must have the FILE privilege.
3. The file must be readable by all and its size less than max_allowed_packet bytes.
4. 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.

I am currently using:

select LOAD_FILE('/Users/pricedb/Desktop/FolderName/imageName');

and it returns NULL

I have confirmed that all privileges are granted to the user. What does it mean that the file must be located on the server host? The server is being run off my local computer and and file is located there, so does that mean I am good on that?

Any advice would be greatly appreciated, I do not know why it's not returning a value.

Answer 1

Using MySQL Workbench 8.0:

1. In the Result Grid panel where you can see the rows and columns, right-click the cell that will hold the BLOB value (currently shown as NULL).
2. In the context menu that appears, select the first item: Open Value in Editor.
3. In the lower-left corner, there are two buttons. Click on: Load...
4. Browse to your image file.
5. The Binary tab is filled with the image's binary data.
6. Click: Apply. You'll return to the Result Grid panel again. Notice NULL has been replaced by BLOB.
7. Right-click BLOB and select Open Value in Editor once more.
8. You'll find a new tab at the top: Image. There you can preview the image you just added.

Greetings!

Answer 2

I had the same issue.

Fond out that the file to be loaded, needs to be in the folder location where mysql/mariadb has privileges to read it. It can be configured, BUT the data folder is already has access right.

In my case I copied my file to data folder: C:\Program Files\MariaDB 10.3\data And than I just called it with full path:

select load_file('C:\\Program Files\\MariaDB 10.3\\data\\test.txt');

Answer 3

"FILE" is an administrative privilege. When you say that ALL privileges have been granted, does that include global administrative privileges too?

Here's the syntax http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-global-privileges, but I found "MySQL Workbench" GUI more helpful.

Check out OS file permissions, too.

Answer 4

1. show variables like 'secure_file_priv';
2. Load file in this directory.
3. select load_file('directory_from_1');

Answer 5

Here is my command on windows 7:

select load_file("C:/Program Files (x86)/MySQL/MySQL Workbench CE 6.0.7/images/hieuImages/a.jpg");

And it worked! You maybe try to copy your images into that directory and select again in mysql.