How can I adjust the timeout for a MySQL query run by PowerShell to allow more than 30 seconds to run?

Question

I have a MySQL SELECT query that takes a few minutes when run in SQLyog (the GUI I use). When I try to run the exact same query via PowerShell (using SimplySQL) it always fails at 30 seconds. I'm not sure which timeout variable is limiting me, or if there are even other settings in my PS environment that have a 30 second timeout. I have set these timeout values ​​in the my.cnf file of the Linux MySQL server and restarted MySQL. But without success:

net_read_timeout=600
mysqlx_read_timeout=600
mysqlx_connect_timeout=600

I've searched on Google for MySQL and timeout values, but haven't found an answer. So my two questions are: what other variable called 30 second timeout I can override, or is there some other way to find out the difference between how the query is running in my interactive tool vs. how it is running in the PS session difference? (Note: I cannot speed up the query to always complete in under 30 seconds)

When I run my PS I just need:

Open-MySQLConnection -ConnectionString ...
Invoke-SqlQuery -Query ...
Invoke-SqlQuery -Query "SHOW VARIABLES LIKE '%timeout%'"

(The last line shows that I have successfully overridden any MySQL variable named "timeout".)

Answer 1

PowerShell uses the .Net connection classes behind the scenes to accomplish this, and these objects require a connection string. The connection string is usually allowed to contain a timeout for the connection. The exact timeout setting depends on the specific driver you use, and unfortunately it's not well documented on Connectionstrings.com, but here's an example:

Connect Timeout=600

Otherwise, you could probably set a timeout property on the connection object itself... but we'd need to look at the PowerShell connection code to know exactly what that looks like.

It's also possible that the PowerShell code is handing it over to the ODBC data source, where the connection string is just something like DSN=MyDataSource. In this case, you need to look at the configuration of the data source.