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

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 2

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