LOAD_FILE returns NULL

问题:

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.

回答1:

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.

回答2:

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

回答3:

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');