using Invoke-SqlScalar to execute a store procedure in MySQL not completed/notify completion until timeout of command

[Strada-EricFr]

Hi,
I use
Invoke-SqlScalar to call a store procedure in MySQL
The store procedure do a lot of update and takes for example 3700 seconds.

I have trace the process of the store procedure, see the last command (logged in a table that the Store procedure is completed)

The issue:

• i suse the followig to call store procedure :
  $result = Invoke-SqlScalar -Query $query -ConnectionName $this.Connection.ConnectionName -CommandTimeout 4800 -ErrorAction SilentlyContinue -ErrorVariable SqlError -WarningAction SilentlyContinue

so after 3700 the strore procedure complte on MySQL side, but the run of the Invode-SqlScalar is completed/finish when reach the time of of 4800

Why is the invoke-scalar not notify about the completion/end of the Store Procedure ?

regards

[mithrandyr]

@ericfran -- I'm having troubles reproducing, can you provide some sample code that demonstrates the issue.
I'm using PowerShell 7.4.7 and SimplySql 2.1.0.96. I tried this:

Open-MySqlConnection -Server $server -Credential $cred
#create a store procedure
$proc = @"
DROP PROCEDURE IF EXISTS testdelay;
CREATE PROCEDURE testdelay(d int)
BEGIN
  SELECT SLEEP(d);
END;
"@

Write-Host "Creating procedure..."
Invoke-SqlUpdate -Query $proc | Out-Null

Write-Host "Call proc with 5 seconds, timeout 15 seconds... (if 0, completed.  if 1, interrupted):"
Invoke-SqlScalar -Query "call testdelay(5)" -CommandTimeout 15

Write-Host "Repeat call, response is measurement of query execution:"
Measure-Command {Invoke-SqlScalar -Query "call testdelay(5)" -CommandTimeout 15}

Write-Host "Call proc with 15 seconds, timeout 5 seconds... (if 0, completed.  if 1, interrupted):"
Invoke-SqlScalar -Query "call testdelay(15)" -CommandTimeout 5

Write-Host "Repeat call, response is measurement of query execution:"
Measure-Command {Invoke-SqlScalar -Query "call testdelay(15)" -CommandTimeout 5}

Close-SqlConnection

[Strada-EricFr]

I thanks for fast reply.
I found the root cause of the problem:

• the store procedure runs avg 1 hour
• I set first command Timeout to less than 1 hour ---> and so I had in ErrorVariable the timeout message. : OK
• So , I increase the the timeout to 2 hours ---> the store procedure complete on MySQL but I still had a timeout after 2 hours; seems the call to process the store procedure never receive a notification of the completion from MySQL Server.
• I try to set in the SQL use in the Invoke-SqlScalar some : set session read and write timeout but add still the same issue
• so based on some other experience to access database I remember the keep alive.
• after review the doc of the : Open-MySqlConnection i did different try to set parameters (based on doc : https://dev.mysql.com/doc/connector-net/en/connector-net-8-0-connection-options.html ) to provide this:
  $AdditionalParameters = @{
  "default command timeout" = 14400
  "Connection Timeout" = 30
  "AllowUserVariables" = "True"
  "UseCompression" = "True"
  "Pooling" = "True"
  "MinimumPoolSize" = 5
  "Maximumpoolsize" = 20
  "ConnectionLifeTime" = 300
  "Keepalive" = 10
  #"UseAffectedRows" = "True" # When true, the connection reports changed rows instead of found rows.
  #"Ignore Prepare" = "True"
  #"Protocol" = "socket"
  #"ProcedureCacheSize" = 0
  #"CacheServerProperties" = "True"
  #"UseUsageAdvisor" = "True"
  #"CheckParameters" = "False"
  }

to be used so :
Open-MySqlConnection -Server $this.Server -Database $this.Database.ToLower() -Credential $this.Credential -ConnectionName $this.connectionName -ErrorAction SilentlyContinue -ErrorVariable SqlError -WarningAction SilentlyContinue -Additional $AdditionalParameters

Result:

• it works, my store prcoedure complete after 1:05 hour and I receive the notification when it completes.
• the Keepalive made the trick .... (other was only for testing purpose)
  Some are not supported like :UseUsageAdvisor, ...

Note:

• maybe provide an example of parameter in the doc of the Open-MySqlConnection could be "nice" ;-)

thanks.
Regards,
Eric

[mithrandyr]

@ericfran -- so the key is to set "KeepAlive" -- maybe that should be set by default, are you aware of any issues that might happen with doing that?