CODEWITHSUNDEEP
mysqlblob
Simo03
Simo03

Reputation: 273

Insert a BLOB value into MySQL database

I have a MYSQL database with a column named img of BLOB type.

When I insert a value into that column like this :

LOAD_FILE('C:/Documents and Settings/All Users/Documents/My Pictures/Sample Pictures/Sunset.jpg')

it works !

But like this :

LOAD_FILE('C:/Documents and Settings/Administrator/My Documents/My Pictures/picture.jpg')

it doesn't work and it tells me that the column img cannot be null !

And in both cases the file exists, and I'm connecting to the database as the root user (all privileges), so I don't understand why I'm getting this error.

Thanks in advance

Upvotes: 0

Views: 25674

Answers (3)

Aaron
Aaron

Reputation: 77

This may be a little late but how I insert blob files when I just need them as sample data is like this:

  1. Using MysqlWorkbench I enter a record into my blob table without applying the insert.
  2. I right-click the blob field, open value editor then upload an image there.
  3. Now before applying the insert I copy the row and paste it repeatedly (a procedure loop can be used as well)

I do this same thing when updating blob fields as well except in the WHERE clause I would use WHERE primaryKey >= this value this would update all the samples

Upvotes: 0

zloctb
zloctb

Reputation: 11176

Maybe problem with max_allowed_packet

1.jpg is a small picture and 2.jpg is a big picture 

mysql> DESCRIBE  blob_files;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| file  | blob    | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO blob_files(file) VALUE(LOAD_FILE('D:/2.jpg'));
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------
------------------+
| Level   | Code | Message
                  |
+---------+------+--------------------------------------------------------------
------------------+
| Warning | 1301 | Result of load_file() was larger than max_allowed_packet (104
8576) - truncated |
+---------+------+--------------------------------------------------------------
------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO blob_files(file) VALUE(LOAD_FILE('D:/1.jpg'));
Query OK, 1 row affected (0.05 sec)

Upvotes: 1

Gary Schreiner
Gary Schreiner

Reputation: 902

I'm going to post this as an answer, and I'll modify it as needed. In the comments I mentioned mysqld needs to run as administrator. Upon consideration, I realized this is actually not a good idea, since windows UAC is in place for a reason. A better option is to add the necessary permission to the folder.

Go to your My Documents folder under administrator, right click on My Pictures, go to security, and add "LOCAL SERVICE" to your permissions with the read attribute. Then your MySQL server should be able to read from that folder.

If you want to verify LOCAL SERVICE is the proper account, go to start -> run and type services.msc and press enter. Find MySQL, right click and hit properties, go to the Log On tab and see which account it runs as. This is the one you should add to the security tab of the folder with the read permissions.

Upvotes: 0

Related Questions