How to use LOAD_FILE to load a file into a MySQL blob?

Question

I tried to load a file into a MySQL blob (on a Mac).

My query is

INSERT INTO MyTable VALUES('7', LOAD_FILE('Dev:MonDoc.odt'))

No error appears but the file is not loaded into the blob.

Answer 1

I just wanted to add one more caveat that I found in my testing:

when using select load_file('/path/to/theFile.txt'); The file that you are loading HAS to be on the machine the sql instance is running on.

This bit me in the butt for a long time because I use MySQL workbench to load files all the time into our various sql instances and when using commands like LOAD DATA LOCAL INFILE 'C:/path/to/theFile.csv' INTO TABLE those would easily grab the file off of my local hard drive and process it into the tables regardless of where the actual sql instance was running. However the load_file command doesn't seem to behave at least for me in the same way (Maybe there exists a local_load_file() command I don't know about). MySQL seems to only allow it to look for files from the local system where the sql instance is running.

So if you're like me and you can't figure out why load_file is always returning NULL have no fear...upload the files to the sql server instance and then use that path from your Query browser and all will be well.

Answer 2

After ensure other conditions, my solution is change a global variable named secure-file-priv. Its default value is NULL, which means mysqld can't read/wirite files. I changed its value by add secure-file-priv= in /etc/my.cnf behind [mysqld], then restart mysql service. Then, load_file() worked!

Answer 3

The manual states the following:

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. 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.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

As of MySQL 5.0.19, the character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;

From this, I see more than one thing that could be wrong in your case...

• are you passing the full path?
• are privileges set correctly?
• what does the function return? NULL?
• have you tried it with the query given in the manual?

Answer 4

double escape the slahes in the full path if you're in windows.

Answer 5

I had the same problem with Linux ...

select load_file('/tmp/data.blob');
+-----------------------------+
| load_file('/tmp/data.blob') |
+-----------------------------+
| NULL                        |
+-----------------------------+

Eventually i could load the file successfully after user and group ownership were changed to 'mysql':

sudo chown mysql:mysql /tmp/data.blob

Answer 6

Thanks.

The user that is running mysql, needs to OWN the file. My mistake was, I thought it just needed to be able to READ or EXECUTE the file.