Insert a BLOB value into MySQL database

Question

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

Are you running your database server (mysqld) in elevated permissions? Needs to be run as administrator (from an administrator command prompt, or administrator account if a service) in modern Windows versions to access files in the Administrator folder. — Gary Schreiner
– Gary Schreiner, Commented May 10, 2014 at 0:17
Windows has UAC (user access control). It doesn't allow certain things to happen without elevated privileges, even when you're logged in as administrator. You need to run mysqld in either an administrator command prompt (right click on the command prompt icon and choose run as administrator) or as an administrator service. — Gary Schreiner
– Gary Schreiner, Commented May 10, 2014 at 0:33

zloctb · Accepted Answer · 2015-06-11 09:18:12Z

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)

Gary Schreiner · Accepted Answer · 2014-05-10 00:58:24Z

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.

Aaron · Accepted Answer · 2022-06-29 14:06:23Z

0

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

Using MysqlWorkbench I enter a record into my blob table without applying the insert.
I right-click the blob field, open value editor then upload an image there.
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

edited Jun 29, 2022 at 14:06

answered Jun 29, 2022 at 14:06

Aaron

674 bronze badges

Collectives™ on Stack Overflow

Insert a BLOB value into MySQL database

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related