5

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.

jww
  • 97,681
  • 90
  • 411
  • 885
user2761933
  • 179
  • 1
  • 4
  • 15
  • Are you trying to load in an image? – Kermit Sep 10 '13 at 14:16
  • Yes, I am loading a jpg. – user2761933 Sep 10 '13 at 14:28
  • Besides the fact that you should *not* be storing images in a MySQL database, what are the reasons for doing this? – Kermit Sep 10 '13 at 14:31
  • I am creating an app that will allow a user to view and download specific images. I need a way to display the images without letting the user actually download them unless they are authorized. My current prototype app has SQL holding the file locations, and then the app uses those locations to display the images, but in the final version the client will not have the images located on their computer. How else would I allow the user to view the images if not by storing them on SQL? (PS, I'm pretty new to SQL, why is storing images on the server bad practice?) – user2761933 Sep 10 '13 at 14:43
  • You store the image on the file system. Please read [this](http://stackoverflow.com/a/6472268/679449) and [this](http://stackoverflow.com/questions/527801/php-to-store-images-in-mysql-or-not) – Kermit Sep 10 '13 at 14:45
  • Alright, I think I understand. So instead of storing my file IN SQL, I would store them on the same server as MySQL and then just place the file locations in SQL? Then theoretically the user could query SQL for the file location, then download it from the server? Sorry if I'm not making sense, I'm just trying to figure out how the typical configuration works. – user2761933 Sep 10 '13 at 15:32
  • You don't store the image on the *same* system as MySQL. You store a *path* to the image, then return that path to the image. Typically, the path of the image is rendered by a web browser or embedded in an application such as PHP to prevent hot linking. – Kermit Sep 10 '13 at 15:34
  • what's the output of ls -l /Users/pricedb/Desktop/FolderName/imageName? – VMai Apr 10 '14 at 16:07

5 Answers5

1

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');
lastboy
  • 566
  • 4
  • 12
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!

Metafaniel
  • 29,318
  • 8
  • 40
  • 67
0

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.

nobjta_9x_tq
  • 1,205
  • 14
  • 16
0

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

Agustí Sánchez
  • 10,455
  • 2
  • 34
  • 25
0
  1. show variables like 'secure_file_priv';
  2. Load file in this directory.
  3. select load_file('directory_from_1');
Sergio
  • 11