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!!