V2 breaks parallel processing

[bigmike613]

After upgrading to V2 I get the error below when running parallel sql updates. this worked fine in v1.9.1. Note that for parallel processing, a new connection is created, sqlupdate ran and then connection closed for each item.

Invoke-SqlUpdate: Line | 17 | invoke-Sqlupdate -connectionname inside -Query $query | ~~~~~~~~~~~~~ | This MySqlConnection is already in use. See https://fl.vu/mysql-conn-reuse

[mithrandyr]

@bigmike613 -- can you provide a sample script that reproduces your problem? Thanks.

Also, the reason this may have worked in v1.9.1 is because that version used a different MySql package and an older version at that. If you need to run multiple statements in parallel -- create a unique connection for each path.

#assume you are running 4 statements in parallel, then open 4 connections
Open-MySqlConnection -ConnectionName A
Open-MySqlConnection -ConnectionName B
Open-MySqlConnection -ConnectionName C
Open-MySqlConnection -ConnectionName D

[bigmike613]

here you go. I edited this so you should be able to run this directly after changing the user/pass and server.

$devices=@("8.8.8.8","5.5.5.5","1.1.1.1")
$devices | % -parallel {
$device=$_
import-module simplysql
$username = "mike"
$PT_PASS="pass"
$server="mysql"
$password = ConvertTo-SecureString $PT_PASS -AsPlainText -Force
$psCred = New-Object System.Management.Automation.PSCredential -ArgumentList ($username, $password)
$result = Test-Connection -ComputerName $device -quiet -Count 2 -timeout 1
if ($result)
{
$query = "insert into results (devicename,status) values ('" + $device + "',true);"
open-mysqlconnection -connectionname inside -server $server -cred $psCred -sslmode required -database ALERTING
invoke-Sqlupdate -connectionname inside -Query $query
close-sqlconnection -connectionname inside
}else{
$result2 = Test-Connection -ComputerName $device -quiet -Count 2 -timeout 1
if ($result2){
$query = "insert into results (devicename,status) values ('" + $device + "',true);"
}else{
# both pings failed
$query = "insert into results (devicename,status) values ('" + $device + "',false);"
}
open-mysqlconnection -connectionname inside -server $server -cred $psCred -sslmode required -database ALERTING
invoke-Sqlupdate -connectionname inside -Query $query
close-sqlconnection -connectionname inside
}
}

[mithrandyr]

@bigmike613 -- a couple of suggestions.

• you only need to import the module once (so do it outside your foreach statement)
• you are using the same connection name "inside" in the process block of the foreach statement... so each iteration is using the same connection (especially since the connectionName is global to the powershell session.)

  
  #demo creating unique connection names inside the foreach
  1..5 | ForEach-Object -Parallel {$cn = "test$_"; write-host $cn}

demo creating connections

1..5 | ForEach-Object -Parallel { $cn = "test$_" Open-SQLiteConnection -ConnectionName $cn }

Show-SqlConnection -All

However, based on your example -- I would generate the results in the foreach statement and return them, then open a single connection and process the results
```powershell
$results = @("8.8.8.8","5.5.5.5","1.1.1.1") |
  ForEach-Object -Parallel {
    [PSCustomObject]@{
      device = $_
      result = (Test-Connection -ComputerName $_ -Count 2 -Quiet -Timeout 1)
    }
  }

#Open-MySQLConnection #used SQLite in memory database below, but you can adapt by opening MySqlConnection instead.
Open-SQLiteConnection 
Invoke-SqlUpdate -Query "CREATE TABLE results (devicename text, status text)"
$results | Invoke-SqlUpdate -Query "INSERT INTO Results (devicename, status) VALUES (@device, @result)"
Invoke-SqlQuery -Query "SELECT * FROM results"
Close-SqlConnection

[bigmike613]

I find it frustrating that I have to re-write my application because of a change in your module. I attempted to do this and found that the SQL query creates a System.Data.DataRow which makes it difficult to manipulate. This will require more than a simple change to my application. I think I will have to stick with v 1.9.1 until I can invest some more time coming up with a solution. Thanks for your suggestions.

also, it seems that passing a pscustomobject to the invoke-sqlupdate does not work.

$devices | invoke-Sqlupdate -query "INSERT INTO Results (devicename, status) VALUES (@devicename, @result)"
Invoke-SqlUpdate: The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.

[mithrandyr]

@bigmike613, This is the unfortunate consequences of major changes. The v2 was a major change to support cross platform powershell and PS7. It was also an opportunity to update the underlying providers used. MySql.Data was used for the MySql connections prior to v2 and MySqlConnector is used in v2. This change was made for performance reasons and supportability reasons.

As for changing your application -- it seems a small change to not resuse the same connection inside the foreach statement. Technically, you could use the value in your devices array as the connection name and that would guarantee a unique connection, thereby avoiding the problem.

As for the last issue, some SQL providers (and I believe MySql is this way), treat parameter names as case-sensitive. In order for passing objects to Invoke-Sql* to work, the actual property name on the object has to match exactly (including case) to the parameter name in your query (FYI this is also true with regards to passing values to the parameters by hashtable using the -parameters parameter.)

As for the System.Data.DataRow -- that has always been returned by the module, however if you want to receive PSObjects instead, you can use the -Stream parameter on Invoke-SqlQuery and PSObjects will be returned instead of DataRow objects.

[bigmike613]

Great, I think the -stream will get me where I need to be. thanks again!