Lost Connection Issues
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Lost Connection Issues
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
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
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Lost Connection Issues
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.
Does this help? If not, please send the full error message.
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Re: Lost Connection Issues
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!
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!
Re: Lost Connection Issues
How long does the query take Using an alternative native connection mysql tool?
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Re: Lost Connection Issues
Regardless of connection type, still get the same error message
No problem writing to it, just problems with reading and lookup's
No problem writing to it, just problems with reading and lookup's
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Lost Connection Issues
Try increasing values of the following server variables: net_read_timeout, net_write_timeout and max_allowed_packet.
Is the issue reproduced after this?
Is the issue reproduced after this?
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Re: Lost Connection Issues
Tried that, still get the same error.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Lost Connection Issues
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';
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';
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Re: Lost Connection Issues
String Parameters:
Connection Timeout - 6000
Default Command Timeout - 6000
Sever Variables:
net_read_timeout - 6000
net_write_timeout - 72000
max_allowed_packet - 33554432
Connection Timeout - 6000
Default Command Timeout - 6000
Sever Variables:
net_read_timeout - 6000
net_write_timeout - 72000
max_allowed_packet - 33554432
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Lost Connection Issues
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?
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Re: Lost Connection Issues
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.
Thanks for your help.
-
- Posts: 11
- Joined: Thu 01 Jun 2017 12:54
Re: Lost Connection Issues
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.
-
- Posts: 2
- Joined: Wed 22 Aug 2018 12:20
Re: Lost Connection Issues
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
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
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Lost Connection Issues
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:
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';
-
- Posts: 2
- Joined: Wed 22 Aug 2018 12:20
Re: Lost Connection Issues
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
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