Linkedserver connection to MySQL database with ODBC driver failed

Question

Linkedserver connection to MySQL database with ODBC driver failed

binrip 0

Hi
I am attempting to use a SQL Server 2014 linked server to connect to a MySQL 8 instance. My single openquery insert statement works fine, but when I try to run multiple insert statements to insert data to multiple tables, I receive a generic error message. Sometimes it works without error during off-peak hours. The error message I receive is:

OLE DB provider "MSDASQL" for linked server "MYSQL-LINKED-SERVER" returned message "[MySQL][ODBC 8.4(w) Driver]Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060".

This is the insert statement I am using:

INSERT INTO OPENQUERY([MYSQL-LINKED-SERVER],'SELECT  id, name, state_date, created_at, updated_at, created_byFROM Db1.table1') SELECT  id, name, state_date, created_at, updated_at, created_by FROM source a

I have already tried modifying some settings on the Windows server hosting the queries, and I have enabled certain flags on the MySQL side. Can someone please help me pinpoint what could be causing the issue?

binrip 0 Reputation points

2024-09-17T06:03:39.8666667+00:00

I have Adjust the windows server MaxUserPort and TcpTimedWaitDelay settings and enable following flags in mysql as well.
max_allowed_packet: 67108864

wait_timeout: 86400

connect_timeout: 28000

net_read_timeout: 3600

interactive_timeout: 28773

net_write_timeout: 3600

bulk_insert_buffer_size: 4294967295
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-09-17T21:08:29.1666667+00:00

That is the sort of operation the requires you to sacrifice two goats at full moon, and then a horse at new moon. Then you might get away with it.

That is, there are so many moving parts here, and so much you don't have insight in. As Olaf notes, this seems to be a message from the MySQL OBDC driver. I would look at different way to run this operation, for instance with SSIS, or just a plain script or program.

I do not that there seems to be a syntax error in your code above. There needs to be a space before FROM. But I guess that is only a typo in the post, and not the actual code.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-09-19T09:14:56.93+00:00
Hi @binrip,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Regarding your question:

The error message you're receiving suggests that the connection to the MySQL server is being lost during the execution of multiple insert statements.

Here are some troubleshooting steps that might help you.

Test the network connection between the Windows server and the MySQL server using tools like ping or telnet.

Check the MySQL server's configuration to ensure it can handle multiple concurrent connections and increase the max_connections setting if necessary.

Check for any firewall rules or security settings that might be causing the connection to drop intermittently.

Monitor the SQL Server's performance using tools like Performance Monitor to ensure it has sufficient resources (CPU, memory) to handle the workload.

Ensure that the ODBC driver is up-to-date and compatible with the MySQL version you're using.

Consider batching multiple insert statements together in a single query to reduce overhead and improve performance.

I hope this information helps. Please do let us know if you have any further queries .
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-09-20T10:59:42.5333333+00:00

Hi @binrip,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-09-23T18:12:05.8733333+00:00

Hi @binrip,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

binrip 0 Reputation points

2024-09-17T06:03:39.8666667+00:00

I have Adjust the windows server MaxUserPort and TcpTimedWaitDelay settings and enable following flags in mysql as well.
max_allowed_packet: 67108864

wait_timeout: 86400

connect_timeout: 28000

net_read_timeout: 3600

interactive_timeout: 28773

net_write_timeout: 3600

bulk_insert_buffer_size: 4294967295
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2024-09-17T21:08:29.1666667+00:00

That is the sort of operation the requires you to sacrifice two goats at full moon, and then a horse at new moon. Then you might get away with it.

That is, there are so many moving parts here, and so much you don't have insight in. As Olaf notes, this seems to be a message from the MySQL OBDC driver. I would look at different way to run this operation, for instance with SSIS, or just a plain script or program.

I do not that there seems to be a syntax error in your code above. There needs to be a space before FROM. But I guess that is only a typo in the post, and not the actual code.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-09-19T09:14:56.93+00:00

Hi @binrip,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Regarding your question:

The error message you're receiving suggests that the connection to the MySQL server is being lost during the execution of multiple insert statements.

Here are some troubleshooting steps that might help you.

Test the network connection between the Windows server and the MySQL server using tools like ping or telnet.

Check the MySQL server's configuration to ensure it can handle multiple concurrent connections and increase the max_connections setting if necessary.

Check for any firewall rules or security settings that might be causing the connection to drop intermittently.

Monitor the SQL Server's performance using tools like Performance Monitor to ensure it has sufficient resources (CPU, memory) to handle the workload.

Ensure that the ODBC driver is up-to-date and compatible with the MySQL version you're using.

Consider batching multiple insert statements together in a single query to reduce overhead and improve performance.

I hope this information helps. Please do let us know if you have any further queries .
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-09-20T10:59:42.5333333+00:00

Hi @binrip,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mahesh Kurva 5,025 Reputation points Microsoft External Staff Moderator

2024-09-23T18:12:05.8733333+00:00

Hi @binrip,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Olaf Helper 47,436

Can't connect to MySQL server on 'ip:port' (10060)".

Are you sure about the used IP port 10060? Default is 3306.

Can you connect your MySQL Server other tools like Wockbench?

Can you run a simple SELECT statement against the linked server?

binrip 0 Reputation points

2024-09-17T07:51:22.76+00:00
Yes, I can connect to MySQL with Workbench also and can run openquery select and insert statements without any issue. However, when I run multiple openquery insert statements, I'm getting this error. Here is the actual error message I got

OLE DB provider "MSDASQL" for linked server "MYSQL-LINKED-SERVER" returned message "[MySQL][ODBC 8.4(w) Driver]Lost connection to MySQL server at 'waiting for initial communication packet', system error: 10060".
Olaf Helper 47,436 Reputation points

2024-09-17T08:42:00.33+00:00

That's a very different error message then before.

And the error is raised by MySQL, not by MS SQL Server.
binrip 0 Reputation points

2024-09-18T05:35:25.6166667+00:00

Hi,
I don't see any error messages from mysql log? Do you have any suggestion on how to troubleshoot this issue.

Share via

Linkedserver connection to MySQL database with ODBC driver failed

1 answer

Your answer