Lost Connection Issues

Discussion of issues, suggestions and bugs of Devart SSIS Data Flow Components, our product line for building SSIS-based ETL solutions, performing data access to popular cloud applications and databases.
FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Lost Connection Issues

Post by FunnyName123 » Thu 01 Jun 2017 13:00

I was just wondering if anyone else was having timeout and connection lost issues with the MySql source and MySql lookups.

I've already updated the timeouts on the connections in SSIS and I have also updated the timeouts at a server level.

It's not like I'm retrieving a lot of data, the source normally brings back 500,000 records and the lookup looks at a table with 30,000 records.

Any solutions?

Thanks

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Post by Pinturiccio » Fri 02 Jun 2017 15:37

Try increasing a value of your Default Command Timeout property in Devart MySql Connection Manager. To find this property you need to edit your connection and switch to the Advanced tab.

Does this help? If not, please send the full error message.

FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Post by FunnyName123 » Mon 05 Jun 2017 09:18

I've already changed the timeout properties on the connections.

The error message I get is:
[Devart MySql Lookup] Error: Lost connection to MySql.

I can only think that the lookup tries to cache the whole table, is there anyway to do a partial cache instead of a full cache?

Thanks!

gerham
Posts: 12
Joined: Thu 22 Sep 2016 23:58

Re: Lost Connection Issues

Post by gerham » Mon 05 Jun 2017 11:18

How long does the query take Using an alternative native connection mysql tool?

FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Post by FunnyName123 » Mon 05 Jun 2017 13:38

Regardless of connection type, still get the same error message :cry:

No problem writing to it, just problems with reading and lookup's

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Post by Pinturiccio » Wed 07 Jun 2017 14:30

Try increasing values of the following server variables: net_read_timeout, net_write_timeout and max_allowed_packet.
Is the issue reproduced after this?

FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Post by FunnyName123 » Thu 08 Jun 2017 14:17

Tried that, still get the same error.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Post by Pinturiccio » Mon 12 Jun 2017 10:12

Please send us the values of the following connection string parameters:
1. Connection Timeout
2. Default Command Timeout

And the values of the following server variables:
1) net_read_timeout
2) net_write_timeout
3) max_allowed_packet

You can get the latter values via an SQL query. For example, you can retrieve the value of the max_allowed_packet variable with the following query:
SHOW VARIABLES LIKE 'max_allowed_packet';

FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Post by FunnyName123 » Mon 12 Jun 2017 10:46

String Parameters:
Connection Timeout - 6000
Default Command Timeout - 6000

Sever Variables:
net_read_timeout - 6000
net_write_timeout - 72000
max_allowed_packet - 33554432

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Post by Pinturiccio » Wed 14 Jun 2017 11:47

Please try testing the Devart MySql Lookup component with different values of the BatchSize property. The default value is 5000. Try lower values: 10, 100, 500. Does the lookup component work successfully with any of these values?

FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Post by FunnyName123 » Mon 19 Jun 2017 14:11

I've updated some of the smaller existing lookup components to 150 and they now seem to be working fine, i'll need to test it on some of the larger lookup components to see if they also make a difference too.

Thanks for your help.

FunnyName123
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Post by FunnyName123 » Thu 22 Jun 2017 07:51

I've made the changes to the larger lookups and although it works, it's painfully slow. So i've changed it to a merge join which takes half the time.

domenico.chiarito
Posts: 2
Joined: Wed 22 Aug 2018 12:20

Re: Lost Connection Issues

Post by domenico.chiarito » Tue 28 Aug 2018 09:07

I m getting the same error but on the Source mysql component
I tried to set "Connection Timeout" and "Default Command Timeout" to 150 but does not help
note, I cannot change configuration on server side,
please advice. thanks

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Post by Pinturiccio » Wed 29 Aug 2018 12:55

This topic is about Devart MySqlLookup component and 150 is a value of the BatchSize property.

As for MySqlSource component, try increasing Connection Timeout and Default Command Timeout, for example, to 600. Is the issue fixed after this?

Please also send us the values of the following server variables:
1) net_read_timeout
2) net_write_timeout
3) max_allowed_packet

You can get the latter values via an SQL query. For example, you can retrieve the value of the max_allowed_packet variable with the following query:

Code: Select all

SHOW VARIABLES LIKE 'max_allowed_packet';

domenico.chiarito
Posts: 2
Joined: Wed 22 Aug 2018 12:20

Re: Lost Connection Issues

Post by domenico.chiarito » Tue 04 Sep 2018 07:18

I have already set 5000 to both params, but error still occurs, is it too big?
keep alive param can help?

server variables:
max_allowed_packet, 4194304
net_write_timeout, 60
net_read_timeout 30

I hope is possible to solve the issue changing client connection params since I cannot change production settings. thanks

Post Reply