Question 181019 in Mysql Question posted in
The official MySQL documentation can be found here.

Which timeout needs to be increased so MySQL query via PowerShell can run longer than 30 seconds?

I have a MySQL SELECT query that takes a few minutes to run when I run it in SQLyog (the GUI I use). When I try to run the identical query via PowerShell (using SimplySQL), it fails, always at 30 seconds.
I am not sure which timeout variable is limiting me, or even if there is something else that has a 30-second override in my PS environment.
I have put these overrides in the my.cnf file on the Linux MySQL server and restarted MySQL. No joy:

net_read_timeout=600
mysqlx_read_timeout=600
mysqlx_connect_timeout=600

I have Googled MySQL and timeout values, all to no avail.
So my two questions are: what is the name of some other 30-second timeout variable I could override, or is there another way to figure out the difference between how the query is running in my interactive tool vs. how it’s running in a PS session?
(Note: I’m not going to be able to speed the query up to always complete in 30 seconds)

When I run my PS, I simply:

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

(that last line shows me that I have successfully overridden any MySQL variables with "timeout" in their names.)

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I tried altering the SQL Query line, but it turns out the parameter I needed is on the Open-MySQLConnection line. I needed to add -ConnectionTimeout 28800 and all's right with the world.

    https://www.powershellgallery.com/packages/SimplySql/1.9.1/Content/Providers%5CMySql%5Cconfig.ps1


  2. 0

    PowerShell uses the .Net connection classes for this behind the scenes, and these objects expect a connection string. One of the items usually allowed in the connection string is a timeout for the connection. This will look different depending on which specific driver you’re using, and unfortunately is not well-documented at Connectionstrings.com, but here is one example:

    Connect Timeout=600

    Otherwise, you may be able to set a timeout property for the connection object itself… but we’d need to see the PowerShell code for the connection to know what the looks like.

    It’s also possible the PowerShell code punts on this, and instead refers to an ODBC datasource, where the connection string is just something like DSN=MyDataSource. In that case, you’ll need to look at the data source configuration.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search