Easy lost connection in uploading a few MB binary data

Justmade · Post by **Justmade** » Tue 10 Jan 2012 15:35

I see that when using TMyQuery to upload some binary data through internet which is a few MB (say 4 MB), it is very easy to get connection lost error message. Sometime I have to try 10+ times to success.

The Package Size in the Server is already 16/32MB and it can success some times so it is not that issue.

Instead, if I use SQLYog to upload that, it almost 100% success. I think it is some time out settings in the client size but I am not sure which and where to set. Could you please give some hints for us regarding this? Thanks.

AndreyZ · Post by **AndreyZ** » Wed 11 Jan 2012 09:39

Hello,

If you already have the max_allowed_packet MySQL variable set to 16/32M, please try increasing the TMyConnection.ConnectionTimeout property value, for example, set it to 300 seconds. You can find more information about cases when MySQL breaks connections at http://dev.mysql.com/doc/refman/5.1/en/ ... ction.html

Justmade · Post by **Justmade** » Thu 12 Jan 2012 01:22

Thank you for your advice. I am sure the max_allowed_packet is already 16M+

If run locally, the large blob update completely problem free. However when over internet, SQLYog update keep slow but smooth update while my application mostly lost connection and sometime success.

I add the ConnectionTimeout to 300 and that time all upload is success.

However, I cannot affort ConnectionTimeout to stay at 300 as it is possible that the server is really offline. So I switch it back to 15 and only raise it to 300 when updating tables with big blob. However connection time out happen again.

Do I need to Disconnect the connection before changing the ConnectionTimeout? In that case, all my opened query will be closed and cause much problem.

So what should I do?

I am using Latest MyDAC, Delphi 2010, Win 7 32bit

Thanks in advance for your help.

AndreyZ · Post by **AndreyZ** » Thu 12 Jan 2012 11:29

If you set the ConnectionTimeout property after connection is established, you have to reconnect. To avoid this problem, you can have two opened connections: the first with ConnectionTimeout set to 15, the second with ConnectionTimeout set to 300. After this, you can choose the correct connection for each particular query. Please note that you can pick ConnectionTimeout smaller than 300 seconds that allows updating tables with BLOB fields without occuring the "Lost connection to MySQL server" error. It depends on your network speed.