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.

Hundley

It looks like the query is missing a close parenthesis. – Tarragon

Oh yes, but in my query parenthesis exist but doesn't work – Hundley

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?

Nectarine

I made 2 errors. File is not on mysqlserver and my user hasn"t file privilege. I make change ang give you, i hope, a good news! – Hundley

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

Covariance

Worked for me. Thanks! (but why did it work? my data.blob had read permissions for my group and others: -rw-rw-r--) – Popper

I think the database user also needs the "file privilege" in MySQL to be set. – Covariance

| Warning | 1301 | Result of load_file() was larger than max_allowed_packet (104 8576) - truncated | – Epistle

Answer 3

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

Mcdonnell

Answer 4

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.

Phoneme

Just to confirm, you are saying that the files being loaded into the database must be on the same storage drive as where MySql is running? For example, the files I want to add are in an external hard drive, and MySql-workbench is running on Windows which runs on a separate drive... that will not work? – Narcoanalysis

@Narcoanalysis no if the machine running sql has access and can map the drive it'll work fine. – Phoneme

Answer 5

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!

Glenoid

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.

Electrograph

Also check your permissions via: "SELECT user, file_priv FROM mysql.user;" – Electrograph