Intermittent SSL Connection Error to MySQL in Copy Data task using Self Hosted IR

Question

Source = MySQL hosted on a on-premises Windows server accessed via a Self Hosted IR on the same server.
Connection to MySQL via an ODBC connection string stored in KeyVault similar to Server=xx.x.x.4;Port=3306;Database=mydatabase;Uid=root;Pwd=************;

Destination = Azure SQL Database using an Azure IR

Pipeline:
1 Stored procedure
Drops all tables in the destination database

2 Copy data
Selects all required table names from MySQL using SQL and stores these in a MySqlTables table in the destination database.
SELECT table_name AS 'Tables_in_mydatabase' FROM information_schema.tables WHERE table_schema = 'myschema' AND TABLE_TYPE <> 'VIEW';

3 Lookup
Queries the MySqlTables table to get the list of all table names that will be imported

4 ForEach
Does the following for each table name returned from the MySqlTables table:

4-1 Set variable
Sets a variable called TableName with the current table name.

4-2 Copy data
Copies the table and data specified by the TableName variable from the MySQL source and creates a new table in the destination database.
A pre-copy script does a check to ensure that the destination table has been deleted.

5 Execute Pipeline
Runs a series of stored procedures to do an initial transformation of the import data in the destination database.

I am getting an SSL connection error being returned for random tables in step 4-2 Copy Data.
It is normally one or two table copies that fail. All other table copies succeed. (Currently 37 tables in total.)

Summary of error message:

"Cannot enable SSL for the connection when connecting to a server that has not enabled SSL."

This does not make sense to me.
My understanding is that the MySQL connection will either be SSL enabled or it wont be. (I'm a SQL Server guy, I have very little experience with MySQL.)
Also, I cannot see any way of specifying the use of SSL in the Data Factory elements, so I assume that I would need to use SSL_ENFORCE and SSLMODE in the ODBC connection.
I am not using any SSL settings in the ODBC connection.

If the connection was using SSL to connect to the tables that successfully transferred to the Azure SQL Database, why does it suddenly fail for random tables?
Alternatively, if the connection is not using SSL with the successfully transferred tables, why does it suddenly decide to use SSL for the tables that fail?

Full error message:

"ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][MySQL] (1130) Cannot enable SSL for the connection when connecting to a server that has not enabled SSL.
ERROR [HY000] [Microsoft][MySQL] (1130) Cannot enable SSL for the connection when connecting to a server that has not enabled SSL.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][MySQL] (1130) Cannot enable SSL for the connection when connecting to a server that has not enabled SSL.
ERROR [HY000] [Microsoft][MySQL] (1130) Cannot enable SSL for the connection when connecting to a server that has not enabled SSL.,Source=,'"

This pipeline ran flawlessly when the MySQL database is hosted on a Linux VM on either my desktop or in Azure. The Self Hosted IR for these occasions were hosted on my desktop or on a Windows VM in Azure, respectively. It also ran flawlessly the first couple of times it ran against the source declared at the beginning of this question.

Copy Data task Source Settings:

Copy Data Task Sink Settings:

Answer 1

SSL is on the SHIR itself here's a troubeshoot for SSL issues

https://learn.microsoft.com/en-us/azure/data-factory/self-hosted-integration-runtime-troubleshoot-guide?tabs=data-factory#couldnt-establish-a-trust-relationship-for-the-ssltls-secure-channel

cant say I have had that specific error before though so it could be something slightly different to the linked

is there more detail about the linked service and dataset that youve got set up behind the copy activity to assist

Answer 2

Thanks @AaronHughes I looked at the troubleshooting guide for the Self Hosted Integration Runtime (SHIR), but I don’t think it really applies in this case. All the SSL details on there are concerned with the SHIR’s connection to Data Factory. I don’t have an issue with this connection. If this was the problem, none of the tables from the MySQL source database would be transferred.

I am having intermittent errors with random tables in the same source database.

To clarify what I mean:

• Monday’s run of the pipeline - Table X and Y will transfer without any issues. Table Z will fail with the SSL connection error.
• Wednesday’s run of the pipeline - Table Y and Z will transfer without any issues. Table X will fail with the SSL connection error

The troubleshooting guide did point me towards the logs that are available in the Data Factory web app.
I have compared a successful run for a table against a failed run for the same table. In short, the failure happens when the copy data task starts to query data from the source table. By this time, the auto create table operation has completed. This required reading the schema from the source database. E.g. the MySQL source database has been connected to and the schema has been read.

So, the copy data task can create a connection to read the schema, but then fails when it tries to create a connection to read the data. This seems like a bug.

I tried to add a wait task to the ForEach loop to see if that helped, but there was no improvement.

If anyone from Microsoft is interested and has access to report logs, I have submitted two.

• A successful run of a table from Monday: cf73a2cb-ac31-4ee1-afb3-6b5873e4a991
• A failed run of the same table from Wednesday: 9aa4943b-9bfc-4091-8da8-eebffff07af3