Set CommandTimeout to resolve Mysql Has Gone Away

lucag · Post by **lucag** » Fri 05 Aug 2011 10:23

Hi,
this is my evniroment :

Delphi 2009
DbExpress Driver for Mysql 4.40

I'm connecting to db with TSqlConnection, and SimpleDataset.

In database i had a blob file of about 13 Mb.

I create connection at runtime :

Code: Select all

      ConnectionName := 'Devart MySQL Direct';
      DriverName     := 'DevartMySQLDirect';
      LibraryName    := 'dbexpmda40.dll';
      VendorLib      := 'not used';
      GetDriverFunc  := 'getSQLDriverMySQLDirect';
      LoginPrompt    := False;
      l_Conn.Params.Add('HostName='+ l_Host);
      l_Conn.Params.Add('DataBase=autoupdate');
      l_Conn.Params.Add('User_Name=' + l_User);
      l_Conn.Params.Add('Password=' + l_Password);
      l_Conn.Params.Add('CommandTimeOut=3000');
      l_Conn.Params.Add('Fetchall=False');

Then i create a query runtime and save the file blob.

All work fine in local, because query is exec in about 4 seconds, but when i'm in remote i had a Mysql Has Gone Away exactly 60 seconds after the execution of query.

I had try to read elsewhere, commandtimeout, connectiontimeout etc but i can't solve this issue.

Can you help me ?
Thank's
Luca G.
Archimedia Solutions srl

AndreyZ · Post by **AndreyZ** » Fri 05 Aug 2011 12:30

Hello,

The connection lost problem with large blob fields is caused by MySQL server. The point is that the server can break connection if the BLOB field value is larger than max_allowed_packet. In this case you should increase max_allowed_packet on the server. You should add the following string (for example) to the my.ini file:

Code: Select all

max_allowed_packet = 20M

You can read more about this problem here:
http://dev.mysql.com/doc/refman/5.1/en/ ... ction.html
http://dev.mysql.com/doc/refman/5.1/en/ ... wed_packet

lucag · Post by **lucag** » Mon 08 Aug 2011 07:15

Hi,
i know very well mysql , i'm managing about 20 server mysql under linux and windows.

I know naturally about max_allowed_packet and he set correctly.

Infact if you read my post in local all work fine. If there is a problem about the size of blob he really don't work also in local when a query take 3-4 seconds to execute.

The real problem is the timout, after 60 seconds my query close connection.

AndreyZ · Post by **AndreyZ** » Tue 09 Aug 2011 08:32

Please try setting the ConnectionTimeout option in the following way:

Code: Select all

l_Conn.Params.Values['ConnectionTimeout'] := '300';

, and check if the problem persists.

lucag · Post by **lucag** » Tue 09 Aug 2011 09:17

First, thank's for your reply.

i added

l_Conn.Params.Values['ConnectionTimeout'] := '300';

but nothing change. After exact 60 second the mysql has gone away.

AndreyZ · Post by **AndreyZ** » Wed 10 Aug 2011 09:23

It seems that this problem is connected with your MySQL server and its settings, and not connected with MyDAC. Please take a look at this article: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html . It may help you to find a cause of such MySQL server behaviour.

lucag · Post by **lucag** » Wed 10 Aug 2011 14:25

Thank's Again.

I'm checking however only to precise a thing :

and not connected with MyDAC

I'm not using mydac but DbExpress Driver for Mysql.

AndreyZ · Post by **AndreyZ** » Wed 10 Aug 2011 14:42

I'm sorry, it was my mistake. I meant dbExpress driver for MySQL.