MariaDB Insert BLOB Image

Question

What I want to do?

• I want to insert a picture into a MariaDB database using the command line, using the LOAD_FILE fuction.

What's wrong?

• I always get a NULL return.
• I don't want a solution like: This is bad style and I haven't seen this so far - try to store the full path! I want to store this picture in this database and not the path.

System

• mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1

• ArchLinux 4.7.2-1-ARCH

• A picture called Test.jpg (817KB) under /home/user/Blob/Test.jpg, /tmp/Test.jpg and even under /var/lib(mysql/images/Test.jpg

• The picture belongs to the user and group mysql and has every permission I could imagine

  -rwxrwxrwx  1 mysql mysql 836508 20. Feb 2016  Test.jpg
  

• I tested several users i.e. mysql and root

Database

I have created a database called Blobtest with a table called Test with a Blob and a Longblob variable.

CREATE TABLE Test (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,longblobimg LONGBLOB NOT NULL, blobimg BLOB NOT NULL, PRIMARY KEY(id));

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| longblobimg | longblob         | NO   |     | NULL    |                |
| blobimg     | blob             | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

Insert Statement

(in this case with the /home/user/directory)

INSERT INTO Test VALUES (1, LOAD_FILE('/home/user/Blob/Test.jpg'), LOAD_FILE('/home/user/Blob/Test.jpg'));

Approach to solving this problem

I followed the instructions at this link MySQL LOAD_FILE() loads null values

• I have execute permission on the parent directory
• The FILE privilege is explicily granted. (GRANT FILE on . TO mysql@localhost)
• I have flushed privileges
• I have logged out and logged back in
• I have tested several directories, belonging to mysql or user via chmod and chown command
• SHOW VARIABLES LIKE 'max_allowed_packet'; is set to 16 MB or 16777216, picture is 817KB big
• select HEX(LOAD_FILE('/home/user/Blob/Test.jpg')); returns NULL

Solutions?

I don't know if this is a bug in MariaDB or if I'm the only one who has this problem. To point this out again: I want to store the picture within this database. I don't want to store the path. This is an experiment, I have to store the picture in the database.

It would be awesome if somebody can help me with this problem!

Answer 1

LOAD_FILE() only works if the file is located on the database server. If the server is on the same system as the file, then there should be no problems loading it. If so, I'd recommend opening a bug report about inadequate error reporting.

Other ways to binary load data into the server

Usually connectors provide methods for loading binary data into the database. For example the MariaDB C connector provides mysql_stmt_send_long_data.

Another option is to convert the data into a non-binary encoding (e.g. base64 or hex) on the client side and decode it on the server (base64 with FROM_BASE64 and hex with UNHEX).