Wednesday, 30 July 2014

MySQL: How to insert blob in table

In order to insert a blob (image, txt, etc) in table FOO in MySQL you should execute the LOAD_FILE [1] statement:

mysql> insert into FOO(data) values(LOAD_FILE('/path/to/file.txt'));

But in my case NULLs were inserted in column data.

Troubleshooting [2]:
mysql> select load_file('/path/to/file.txt'); -- returns NULL

Solution: file.txt should be owned by mysql user/group (although its permissions are -rw-rw-r--; i.e. granted read access to others)

$ sudo chown mysql:mysql /path/to/file.txt


[1] From the manual:


    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.

[2] Thanks StackOverflow

No comments:

Post a Comment