Angkat Bahu: March 2011

Saturday, March 12, 2011

MySQL LOAD_FILE() in Ubuntu, It Works!

Recently, I want to create a php site that will display images retrieved from MySQL database. I always prefer to work with CLI mode other than using query browser for create databases, tables, updating etc.

LOAD_FILE can be used to store images into database through MySQL command line (client). I'm using MySQL version 5.1.41 on my Ubuntu 10.04. Here are the simple steps:

Step 1: Initially we will create a database and tables

mysql> create database testdb;

Query OK, 1 row affected (0.00 sec)

mysql> use testdb;

Database changed

mysql> create table myimages

 -> (

 -> id integer unsigned not null auto_increment,

 -> name varchar(20),

 -> type varchar(10),

 -> picture mediumblob not null,

 -> primary key (id),

 -> unique (name)

 -> );

Query OK, 0 rows affected (0.09 sec)

mysql> desc myimages;

Step 2: Upload an image
At first, I've tried to upload an image from a directory located at '/home/user1/Pictures/ubuntu_logo.jpg'.

mysql> insert into myimages (name, type, picture) values ('xyz', 'jpeg', LOAD_FILE('/home/user1/Pictures/ubuntu_logo.jpg'));

ERROR 1048 (23000): Column 'picture' cannot be null

This error occurred because the result of LOAD_FILE('/home/user1/Pictures/ubuntu_logo.jpg') has return the NULL value probably due to privileges and permission of the /home/user1 directory which is belongs to user1.
The same problem also has been brought up in mysql forum.
After checked on /etc/passwd, it shows that the mysql's home directory is located at /var/lib/mysql/, where permission is fully given for mysql user.
Simply login as root user and copy the image to /var/lib/mysql or you may created your own directory such as /var/lib/mysql/all_images

$ sudo su

$ mkdir /var/lib/mysql/all_images

$ cp /home/user1/Pictures/ubuntu_logo.jpg /var/lib/mysql/all_images

Now, try to run the same insert command again with different directory.

mysql> insert into myimages (name, type, picture) values ('bz', 'jpeg', LOAD_FILE('/var/lib/mysql/all_images/ubuntu_logo.jpg'));

Query OK, 1 row affected (0.00 sec)

Yeah, it works!!