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!!
Subscribe to:
Post Comments (Atom)
It also works if you "sudo chown mysql:mysql filename".
ReplyDeleteThanks very much Mohd for taking the time to post this. I spent considerable effort trying to google a solution until I decided to see if this was an Ubuntu issue, as it turned out to be. I had to edit the /etc/password to add "/var/lib/mysql" as the home directory for mysql (otherwise it had "/nonexistent"), also, at least for me (Ubuntu 13.04 w/MySQL 5.5.32), doing the chown mysql:mysql the previous commenter gave wouldn't work.
ReplyDeleteIt works also on MariaDB, the problem seem related on the ownership of the main folder, if user mysql has the full access (rw) than it allows the read permission.
ReplyDeletedoes not work
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteIt does not work... LOAD_FILE() return NULL
ReplyDeletemysql> insert into image (Id,Name,Image_Data, Image_File_Name) values(1,"doll",
LOAD_FILE('/var/lib/mysql/all_image/chain.jpeg'),"chain");
Query OK, 1 row affected (0.47 sec)
mysql> select * from image;
+------+------+------------+-----------------+
| Id | Name | Image_Data | Image_File_Name |
+------+------+------------+-----------------+
| 1 | doll | NULL | chain |
+------+------+------------+-----------------+