Linkedserver connection to MySQL database with ODBC driver connection issue

Question

I have setup SQL Server 2016 linkedserver to connect to MySQL 8 instance and created stored procedure that insert data from SQL Server database to MySQL database using open query, it works fine when I called the procedure, it inserts the records to MySQL. However, when I create multiple procedures that insert the data to MySQL and call all the procedures at once it fails.

I'm using windows server 2016 VM and Google Cloud Managed MySQL Instance, and ODBC unicode driver 8.4

Example: When I call a procedure separately like below it works.

EXEC insert_data_db1 @p = 1;

I have another procedure with multiple sub procedures like this, when I called this procedure with multiple sub procs, it failed with the following error message.

EXEC insert_data_db1 @p = 1;
EXEC insert_data_db2 @p = 2;
EXEC insert_data_db3 @p = 3;

Each of the procedure have multiple insert statements like these;

INSERT INTO OPENQUERY([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

Errror;

OLE DB provider "MSDASQL" for linked server "LinkedServer" returned message "[MySQL][ODBC 8(w) Driver]Can't connect to MySQL server on 'ip:port' (10060)".

Need some help, what might be the issue?

Answer 1

So you are saying it works when you only run the stored proc once, but fails when you run multiple instances of it parallelly at once.

Are you doing this in Management Studio?

Might be a long shot, but Linked Server connections are just single threaded, so if one thread is already taken, maybe the rest is refused?

You could try breaking it up to steps so that SSMS executes them individually in sequence:

EXEC insert_data_db1 @p = 1;
GO
EXEC insert_data_db2 @p = 2;
GO
EXEC insert_data_db3 @p = 3;