Lost Connection to MySQL server during query

[g-mie]

I am using MySQL server 3.51 database. I am insert/update image into database as follow;

Private Sub insertupdate_image()
    Dim MyStream As ADODB.Stream
    Set MyStream = New ADODB.Stream
    MyStream.Type = adTypeBinary
    MyStream.Open
    MyStream.Position = 0
    
    Dim sTemp As String
    Dim MyData As Variant
       
    sTemp = "C:\SPAI_Temp\HMDaftar" & intHMDaftarID & ".jpg"
    If pPicture = 0 Then Exit Sub 
    VB.SavePicture pPicture, sTemp
    MyStream.LoadFromFile sTemp
 
    Dim Rs As ADODB.Recordset
    Set Rs = New ADODB.Recordset
    query = "Select Gambar,HMDaftarID From t_HMGambar where HMDaftarID=" & intHMDaftarID
    Rs.Open query, db, , adLockPessimistic
    
    With Rs
        If .RecordCount > 0 Then
            .Fields!Gambar = MyStream.Read
            .Fields!HMDaftarID = intHMDaftarID
            .Update
        Else
            .AddNew
            .Fields!Gambar = MyStream.Read
            .Fields!HMDaftarID = intHMDaftarID
            .Update
        End If
        .Close
    End With
 
    MyStream.Close
    Set MyStream = Nothing    
End Sub

It is work well if I insert or update image that size below then 30Kb.

BUT, it is giving "Lost Connection to MySQL server during query" error if I insert or update image that the size above 30Kb.
What should I do?

Thanks

[randem]

Set your connection timeout to 600

[g-mie]

randem...

I'm very new with MySQL (3.51). How to set Connection Timeout to 600?

Thanks

[randem]

You set it on you ADODB connection.

[LinXG]

Hi g-mie;

MySQL is way out of my league, but just to satisfy my own curiosity about MySQL could you tell me what data type you are using to store your pictures. It seems odd that MySQL would crash like that.

[g-mie]

LinXG...
Field Name = MyPic
Field Type = LongBlob
Length/Set = ""
Default = ""

[g-mie]

randem...
Please tell me more detail....
TQ

[brucevde]

Try setting the CommandTimeout property to a higher value not the ConnectionTimeout property.

db.CommandTimeout = 60 'or whatever you need. default is 30 seconds.

[g-mie]

brucevde...
Same error still come out.
Any others idea?

[g-mie]

anybody?

[randem]

You store pictures in a field that supports long data types (binary information) such as IMAGE.

[g-mie]

I use LongBlob field type. I think it is suitable field type. Maybe other setting need to be set..!?
Any idea?

[randem]

Should work. All you need is a simple test to be sure

If (Not dbTable!Data.Attributes And FieldAttributeEnum.adFldLong) Then
 
... 'Field is ok for long binary data
 
End If

[randem]

Can you post your project... or at least your connection code?

[randem]

Also why are you attempting to use streaming data instead of the regular recordset method?

[g-mie]

Can you post your project... or at least your connection code?

This is my connection code

Public Sub conn2db()
Dim con As String
mServer = "localhost"
con = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=" & mServer & ";" _
            & "DATABASE=Kejora;" _
            & "UID=root;" _
            & "PWD=pass;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
 
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.Open con
 
End Sub

[g-mie]

Also why are you attempting to use streaming data instead of the regular recordset method?

If I not mistaken, streaming can 'hold' big size of data.

[randem]

Use:

Public Sub conn2db()
Dim con As String
mServer = "localhost"
con = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=" & mServer & ";" _
            & "DATABASE=Kejora;" _
            & "UID=root;" _
            & "PWD=pass;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841
 
Set db = New ADODB.Connection
db.CursorLocation = adUseClient
db.ConnectionTimeout=600
 
db.Open con
 
End Sub

[g-mie]

db.ConnectionTimeout=600

Same error come out.

If .RecordCount > 0 Then
            .Fields!Gambar = MyStream.Read
            .Fields!HMDaftarID = intHMDaftarID
            .Update                     '<==the error highlighted here 
        Else
            .AddNew
            .Fields!Gambar = MyStream.Read
            .Fields!HMDaftarID = intHMDaftarID
            .Update
        End If

[randem]

I am not sure you can update a Database field like that. You would need to use the AppendChunk method.

[g-mie]

BUT, it is working for picture with size below then 30KB.
AND, it is working if I use MS-Access database. I can insert picture with size more then 2MB

[randem]

Is your access database on your local computer and mySQL over a network or internet?

[randem]

Here is something I goggled:

While logged into MySQL, we should modify the max_allowed_packet system variable. This variable determines how large of a packet (i.e. a single row) can be sent to the MySQL server. By default, the server will only accept a maximum size of 1 meg from our client application. If you do not intend to exceed 1 meg, this should be fine. If you do intend to exceed 1 meg in your file transfers, this number has to be increased. I set my max_allowed_packet value to 15M, in MySQL 3.x, this limit is 16M, in 4.x, the size is limited only to your system memory, up to a theoretical 2G maximum. I personally find 15M to be more than enough, especially since my users connect remotely through DSL modems at best, and a 15 meg transfer tends to take upwards of 5 minutes as it is. If you do need to change this value, you can either set it in the my.cnf file (add a line that reads max_allowed_packet=15M), or use the SET max_allowed_packet=15M; syntax from within MySQL.

[g-mie]

Thanks randem...
It's work now. The come from max_allowed_packet.
TQ