Trouble saving large images in MySQL

Question

I have a lot of images which are more than 1 mb in size which I need to save in MySQL. Sometimes it can update the 1 mb size images but sometimes even 500 kb images cannot be saved.

I already changed max_allowed_packet to 32M in the my.ini file in MySQL Server 5.0.

This is my code for saving an image. Have I done something wrong here?

Try
                Dim FileSize As UInt32
                Dim mstream As New System.IO.MemoryStream()
                pic_box_save.Image.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg)
                Dim arrImage() As Byte = mstream.GetBuffer()
                FileSize = mstream.Length
                mstream.Close()
                ' MsgBox(FileSize)

                MyConnection()
                Sql = "update AREA set AREA_NAME=@Aname, AREA_IMG=@image_data where AREA_NO='" & cboAreaNo.Text & "'"
                Dim cmd As New MySqlCommand(Sql, Con)
                cmd.Parameters.AddWithValue("@Aname", txtAreaname.Text)
                cmd.Parameters.AddWithValue("@image_data", arrImage)
                cmd.ExecuteNonQuery()
                MsgBox("Data successfully updated!!", vbInformation, "Updating")
                Con.Close()
            Catch ex As Exception
                MsgBox("Data not Saved!!!" + ex.Message, vbCritical, "System message")
            End Try

Post the exception details if you are getting any exception. — Nilesh Thakkar, Jul 26 '15 at 03:08
Storing images in a database like this is an anti-pattern. Is there reason this must be stored in the database, itself? Common database patterns are to store the image data in a filesystem (typically one that has replication, backup, etc.) and to simply record a reference (e.g. path) to it in the database. — Michael Aaron Safyan, Jul 26 '15 at 03:08
i'm getting an error when trying to save an image with large size. but sometimes, image with same size can be saved. "Packets larger than max_allowed_packet are not allowed" — user4945412, Jul 26 '15 at 03:18
the images i'm talking about are maps that will be displayed to users. i need to save those images in the database so if there will be changes in maps, the admin can change it without the need to edit the code — user4945412, Jul 26 '15 at 03:20
among several things wrong there, dont use [`GetBuffer`](https://msdn.microsoft.com/en-us/library/system.io.memorystream.getbuffer.aspx) (see the remarks). The buffer will always be larger than the actual image size - sometimes double. Use `.ToArray()`, but an image stored in the filesystem will be easier for someone to edit than one stored as bytes in a DB. You also need to dispose of the DB Objects you are creating or you will run out of resources. See [this for example](http://stackoverflow.com/q/31522891/1070452) — Ňɏssa Pøngjǣrdenlarp, Jul 26 '15 at 03:29
oh i see. storing only the path of the image is the best way to do. thank you everyone. but i need to sync my localhost database in my online database too. anyways, in "You also need to dispose of the DB Objects" do i just need to add Using before mstream As New System.IO.MemoryStream() and End using @Plutonix ? i'm not sure on what i'm doing. i'm still researching. — user4945412, Jul 26 '15 at 04:00

score 0 · Answer 1 · edited Jul 26 '15 at 14:25

0

I just need to add

SET GLOBAL max_allowed_packet=32*1024*1024

edited Jul 26 '15 at 14:25

Artjom B.

61,146
24
125
222

answered Jul 26 '15 at 04:13

user4945412

35
10

1

If an answer was given in the comments, you probably should ask the commenter to post an answer first and if they don't post it, post the answer yourself as community wiki. – Artjom B. Jul 26 '15 at 14:26

Trouble saving large images in MySQL

1 Answers1