LOAD_FILE issue with binary data

[jazzman1]

Hey folks,
does anybody have an idea why the LOAD_FILE() doesn't work for me?

I'm trying to store a binary data(picture) in the table named - picture.

 

 

+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| ID    | int(11)  | NO   | PRI | NULL    | auto_increment |
| IMAGE | longblob | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
 

 

As you can see the type of IMAGE field is set to "LONGBLOB"

According docs, the uploaded image is located on the same file server where the MySQL is deployed and the user named "lxc" has the FILE privilege to this DB table.
The max_allowed_packet bytes is set to 500M inside mysql conf file and there is no issue with secure_file_priv.

The command that I'm running into linux terminal is simple:

[lxc@lxc1 pdo]$ echo "INSERT INTO picture VALUES(NULL,LOAD_FILE ('images/Tree_2.jpg'))" | /usr/bin/mysql test -h ::1 -u lxc -ppassword

The file is there and the user "lxc" is the owner of that file with r/w priv.

 

 

[lxc@lxc1 pdo]$ ls -l images/
total 1240
drwxrwxrwx. 2 lxc lxc   4096 Dec 15 20:52 content
-rw-rw-r--. 1 lxc lxc 491562 Jan  7 13:03 dmc_0003.jpg
-rw-rw-r--. 1 lxc lxc 770042 Jan  7 13:03 Tree_2.jpg

 

So, when I run the mysql command line, LOAD_FILE() function returns NULL, but the ID is stored correctly.

There is no such kind of issue when I'm uploading a binary data with php using file_get_contents() (without LOAD_FILE()), what I usualy do.

<?php


$username = 'lxc';

$password = 'password';
  
$img = file_get_contents('images/Tree_2.jpg');

$sql = "INSERT INTO picture VALUES(NULL, ':binary')";
  
//$sql = "select * from picture where ID=13";
  
$dbh = new PDO('mysql:dbname=test;host=::1;charset=utf8', $username, $password);

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 
$stmt = $dbh->prepare($sql);
  
$stmt->bindParam(':binary', $img, PDO::PARAM_STR);
  
$stmt->execute();

$stmt = null;

PS:

Server version: 5.5.35 MySQL
CentOS 6.4/GNU/Linux machine

Edited January 14, 2014 by jazzman1

[requinix]

When you execute a SQL command all you're doing is sending it to the MySQL server. The client isn't parsing the query looking for LOAD_FILE()s so that it can translate the file path you gave.

 

Try an absolute path.

[jazzman1]

Very good The path was the problem.