How to set maximum value of a blob type in mysql [Resolved]

Posted by Jerome under VB.NET on 4/23/2012 | Points: 10 | Views : 22788 | Status : [Member] | Replies : 24
I want to attach files to a blob field in mysql,but the problem is I can't attach images more than 1 mb in size.How could I solve my problem?Need Help!


Thanks




Responses

Posted by: Bugwee on: 4/24/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
the max allowed packet variable in your sql server exceeds.

try to run :

set max_allowed_packet = 500M


now restart the MySQl service and you are done

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 4/24/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
you can also change directly the packet size via Mysql Administrator... locate and change the max_allowed_packet, under Startup Variables

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
Uploading images into a MySQL database table is a bad idea

For more details, refer this link..

http://www.hockinson.com/programmer-web-designer-denver-co-usa.php?s=47

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
if you want to store big binary data in a MySQL database you need to use another blob type as MEDIUMBLOB or LONGBLOB

Refer this article, its sure to helps to u...

http://enricogi.blogspot.in/2008/12/blob-type-in-mysql.html

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/23/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Singaravel M,

I already used LongBlob dataype but it gets error when I try to save big binary data.Do you have other idea instead?



Jerome
Thanks

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
can i know error details..?

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/23/2012 [Member] Starter | Points: 25

Up
0
Down
data loo long for coulum pic,which my field name...



Cheers,
Jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
It all depends on the column type used for the picture column

.TINYBLOB: maximum length of 255 bytes
.BLOB: maximum length of 65,535 bytes
.MEDIUMBLOB: maximum length of 16,777,215 bytes
.LONGBLOB: maximum length of 4,294,967,295 bytes

compare with ur image size and use correct data type...

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
This error throws only when input size is exceed the declaration size..

So once again check it..

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/23/2012 [Member] Starter | Points: 25

Up
0
Down
What if image size exceeds long blob max length..

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
yes

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
what is ur image size...?

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/23/2012 [Member] Starter | Points: 25

Up
0
Down
image size is 5mb

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/23/2012 [Member] Silver | Points: 25

Up
0
Down
okay..

i can't get any idea..

Refer the following example with ur code..

EXAMPLE for,
How to store pictures in Mysql database

http://forums.mysql.com/read.php?20,17671,27914

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
where did I put this code?


Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
run it on the mysql query browser or in mysql workbench...



Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
what do you by 500M?megabyte?

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/24/2012 [Member] Silver | Points: 25

Up
0
Down
yes 500 MB

Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
Yes right. M = megabyte.

by default the max allowed packet size = 1M. if you want to insert files greater than 1M then you will got a max_allowed_packet error.

If you want to insert 5mb files or lesser then set the max allowed packet to 5M.

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
I could not find max_allowed_packet under startup variable.I also run the code in mysql query browser and it gets error."Incorrect argument type to variable 'max_allowed_packet' "

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bugwee on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
Did you find the Startup variable or Startup parameter on the mysql Admistrator? i think, if you click on that there are tabs displayed like General parameters, my isam parameters, innodb parameters, etc.... find the the packet size in any of the tabs... i think you can see there like default value = 1M.

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/24/2012 [Member] Silver | Points: 25

Up
0
Down
edit your my.cnf file and add

max_allowed_packet=500M 


then restart



Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jerome on: 4/24/2012 [Member] Starter | Points: 25

Up
0
Down
where would I found my.cnf file?

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sakthi.Singaravel on: 4/24/2012 [Member] Silver | Points: 25

Up
0
Down
By default found at:

/etc/my.cnf

or

/etc/mysql/my.cnf


Regards,
Singaravel M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response