CODEWITHSUNDEEP
mysqlsqlmysql-workbenchmariadb
Davit Hermansyah
Davit Hermansyah

Reputation: 55

When I insert a image into MySQL table the result is NULL

What's wrong with this code, I want to insert an image to table, but when I was executed this code the result of image field is NULL.

I try with MySQL Workbench executing:

CREATE TABLE image(keyh int, img blob);
INSERT INTO image VALUES(1, load_file('d:\Picture\cppLogo.png'));

Upvotes: 1

Views: 4303

Answers (5)

Vidyut Singhai
Vidyut Singhai

Reputation: 1

Store Directly Without folder name for example-

create table myimg(id int, image mediumblob);

insert into myimg values(101, load_file("E://xyz.png"));

Upvotes: 0

AQuirky
AQuirky

Reputation: 5236

On Windows the fundamental problem is that MySql, by default, runs as a Windows service under the Network account which means that there are only a few file locations the server can access. Thus for load_file to work, the file must be placed in a folder on the server which can be read by the service. There seems to be no documentation on this. In my investigation the only folder that works with load_file is C:\ProgramData\MySQL\MySQL Server 8.0\Uploads

Run a query to test the load...

select load_file('C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\1.txt') ;

Note on windows you have to use either double \ or / to separate the path elements. This will return NULL on failure, otherwise the contents of the file.

Assume now a table named db.image with columns source and image. Source is character and image is blob. The command to load a.jpg into the table would be

insert into db.image (source,image) values ('a.jpg',load_file('c:/programdata/mysql/mysql server 8.0/uploads/a.jpg'));

Upvotes: 0

Ravi.Dudi
Ravi.Dudi

Reputation: 1364

Open your MySql Command Line Client and login with root user and type

mysql> SHOW VARIABLES LIKE "secure_file_priv";

this will show you the secure path used by MySql to access the files. something like

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

you can either paste files inside this folder or change the "secure_file_priv" variable value to "empty string" so that it can read file from anywhere.

Upvotes: 0

Davit Hermansyah
Davit Hermansyah

Reputation: 55

i was found the correct syntax is following this:

C:/wamp/binsql5.5.20/data/56VRLRFE.jpg'

not this

C:\wamp\binsql5.5.20\data\56VRLRFE.jpg'

thanks guys for all your Answer :D

Upvotes: 1

Daniel W.
Daniel W.

Reputation: 32290

To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_load-file

What can you do?

Check which user mysql is running with, and make sure the file is readable by that user. Make sure the security settings allow the file to be read and it is not of greater size than max_allowed_packet.

See SHOW VARIABLES LIKE 'max_allowed_packet'.

For me, it looks like the file is on your localhost and you try to upload it. This is not possible using LOAD_FILE(). The file must be already on the server.

The issue can also be caused by your windows directory seperator character \ (like RiggsFolly said), which is used for escaping instead, switch to unix style / then:

LOAD_FILE('D:/Picture/cppLogo.png')

Or your Image is of greater filesize than a BLOB field can hold, like Balazs Vago said.

Upvotes: 1

Related Questions