Mysql – LOAD_FILE() works on SELECT, but returns NULL on insert

bloblinuxmariadbMySQL

I'm having troubles using the LOAD_FILE() function in my environment.

I'm trying to add a file to a BLOB field but whenever I try to insert or update this field with LOAD_FILE(), this field is being set as NULL.

I already checked all common problems which includes system variables, file size and max packet Size and permission problems (Both DB and Linux permissions), even some workaround that included moving the file to a Mysql Owned directory, the problem persists.

Do note that when using LOAD_FILE in a statement similar to

SELECT LOAD_FILE('/foo/bar.jpg');

does work and the file raw data gets displayed, either via CLI or using a DBA tool like DBeaver.

Dump of the table:

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string` varchar(3) NOT NULL,
  `blob` blob NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Each file size I want to insert is around 8kb in size.

I'd appreciate any help with this issue.

Best Answer

LOAD_FILE, only reads the contents from the file and returns the contents from itself as a string, you try to add an image into field and using this function isn't gonna be possible. Besides you should have FILE privilege to you could do this and allocating the file into /var/lib/mysql-files. Being this a bad practice, the best is add the route relative or absolute from this file.

Related Question