Lost Connection Issues

Lost Connection Issues

Postby 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
FunnyName123
 
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Postby 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.
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Postby 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!
FunnyName123
 
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Postby gerham » Mon 05 Jun 2017 11:18

How long does the query take Using an alternative native connection mysql tool?
gerham
 
Posts: 12
Joined: Thu 22 Sep 2016 23:58

Re: Lost Connection Issues

Postby 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
FunnyName123
 
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Postby 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?
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Postby FunnyName123 » Thu 08 Jun 2017 14:17

Tried that, still get the same error.
FunnyName123
 
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Postby 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';
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Postby 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
FunnyName123
 
Posts: 11
Joined: Thu 01 Jun 2017 12:54

Re: Lost Connection Issues

Postby 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?
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44

Re: Lost Connection Issues

Postby 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

Postby 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.
FunnyName123
 
Posts: 11
Joined: Thu 01 Jun 2017 12:54


Return to SSIS Data Flow Components