У меня на ноутбуке Windows установлена ​​БД MySQL. Я использую скрипт powershell для подключения / запроса / ввода в БД. У меня есть запрос, который я пытаюсь выполнить, я могу запустить его в MySQL Workbench, в настоящее время он занимает 31.032 секунды и возвращает 3 строки.

SELECT puz1.sudoku9x9_id, puz1.difficulty, puz2.sudoku9x9_id, puz2.difficulty 
    FROM sudoku9x9 as puz1 
    INNER JOIN sudoku9x9 as puz2 
        WHERE  
            puz1.clue_9 = puz2.clue_1 AND puz1.region_9 = puz2.region_1 AND 
            puz1.difficulty/puz2.difficulty BETWEEN .84 AND 1.19 AND 
            NOT EXISTS (SELECT 1 FROM samurai2x AS a 
            WHERE 
                a.puz1_id = puz1.sudoku9x9_id AND a.puz2_id = puz2.sudoku9x9_id)

Скрипт Powershell

$samurai2x = "SELECT puz1.sudoku9x9_id, puz1.difficulty, puz2.sudoku9x9_id, puz2.difficulty FROM sudoku9x9 as puz1 INNER JOIN sudoku9x9 as puz2 WHERE puz1.clue_9 = puz2.clue_1 AND puz1.region_9 = puz2.region_1 AND puz1.difficulty/puz2.difficulty BETWEEN .84 AND 1.19 AND NOT EXISTS (SELECT 1 FROM samurai2x AS a WHERE a.puz1_id = puz1.sudoku9x9_id AND a.puz2_id = puz2.sudoku9x9_id) LIMIT 1"

Invoke-MySqlQuery -Query $samurai2x | ForEach {
    $diff = ([INT]$_.'difficulty' + [INT]$_.'difficulty1') / 2
    Invoke-MySqlQuery -Query "INSERT INTO samurai2x(difficulty, puz1_id, puz2_id) VALUES ('$diff', '$($_.'sudoku9x9_id')', '$($_.'sudoku9x9_id1')')"
}

Когда я запускаю скрипт powershell, он истекает. Поэтому я посмотрел на изменение параметров времени ожидания. Я первый раз побежал

SET GLOBAL connect_timeout=31536000;
SET GLOBAL wait_timeout=2147483;
SET GLOBAL interactive_timeout=31536000;

Числа максимально допустимы из документации MySQL. Это не разрезать это! Поэтому я отредактировал файл my.ini и добавил

[mysqld]

connect_timeout=31536000
wait_timeout=2147483
interactive_timeout=31536000

Я перезапустил службу MySQL. Все та же проблема!

Когда скрипт подключается к БД, он отображает

ServerThread      : 26
DataSource        : localhost
ConnectionTimeout : 15
Database          : sudoku
UseCompression    : False
State             : Open
ServerVersion     : 5.7.17-log
ConnectionString  : server=localhost;port=3306;user id=root;database=sudoku
IsPasswordExpired : False
Site              : 
Container         :

ConnectionTimeout всегда отображал 15 до редактирования времени ожидания и после каждой попытки.

Что мне здесь не хватает, ребята? Заранее спасибо за помощь.

РЕДАКТИРОВАТЬ

# Set MySQL connection info
$username = "root"
$password = cat D:\Sudoku\mysecurestring.txt | convertto-securestring
$dbcred = new-object -typename System.Management.Automation.PSCredential `
         -argumentlist $username, $password

# Connect to MySQL server
Connect-MySqlServer -Credential $dbcred -ComputerName localhost -Database sudoku

Я подключаюсь к БД на основе шагов на этом сайте:Запросы к базам данных MySQL с помощью PowerShell

РЕДАКТИРОВАТЬ

Внизу находится измененная функция. Я помещаю комментарий «Добавлено» над каждой новой строкой.

Новая соединительная линия

Connect-MySqlServer -Credential $dbcred -ComputerName localhost -Database sudoku -CommandTimeOut 600 -ConnectionTimeOut 25

Новый выход подключения

ServerThread      : 23
DataSource        : localhost
ConnectionTimeout : 25
Database          : sudoku
UseCompression    : False
State             : Open
ServerVersion     : 5.7.17-log
ConnectionString  : server=localhost;port=3306;user id=root;database=sudoku;defaultcommandtimeout=600;connectiontimeout=25
IsPasswordExpired : False
Site              : 
Container         : 

Модифицированная функция

function Connect-MySqlServer
{
    <#
    .SYNOPSIS
        Connect to a MySQL Server
    .DESCRIPTION
        This function will establish a connection to a local or remote instance of 
        a MySQL Server. By default it will connect to the local instance on the 
        default port.
    .PARAMETER ComputerName
        The name of the remote computer to connect to, otherwise default to localhost
    .PARAMETER Port
        By default this is 3306, otherwise specify the correct value
    .PARAMETER Credential
        Typically this may be your root credentials, or to work in a specific 
        database the credentials with appropriate rights to do work in that database.
    .PARAMETER Database
        An optional parameter that will connect you to a specific database
    .PARAMETER TimeOut
        By default timeout is set to 15 seconds
    .EXAMPLE
        Connect-MySqlServer -Credential (Get-Credential)

        cmdlet Get-Credential at command pipeline position 1
        Supply values for the following parameters:
        Credential


        ServerThread      : 2
        DataSource        : localhost
        ConnectionTimeout : 15
        Database          :
        UseCompression    : False
        State             : Open
        ServerVersion     : 5.6.22-log
        ConnectionString  : server=localhost;port=3306;User Id=root
        IsPasswordExpired : False
        Site              :
        Container         :

        Description
        -----------
        Connect to the local mysql instance as root. This example uses the 
        Get-Credential cmdlet to prompt for username and password.
    .EXAMPLE
        Connect-MySqlServer -ComputerName db.company.com -Credential (Get-Credential)

        cmdlet Get-Credential at command pipeline position 1
        Supply values for the following parameters:
        Credential


        ServerThread      : 2
        DataSource        : db.company.com
        ConnectionTimeout : 15
        Database          :
        UseCompression    : False
        State             : Open
        ServerVersion     : 5.6.22-log
        ConnectionString  : server=db.company.com;port=3306;User Id=root
        IsPasswordExpired : False
        Site              :
        Container         :

        Description
        -----------
        Connect to a remote mysql instance as root. This example uses the 
        Get-Credential cmdlet to prompt for username and password.
    .NOTES
        FunctionName : Connect-MySqlServer
        Created by   : jspatton
        Date Coded   : 02/11/2015 09:19:10
    .LINK
        https://github.com/jeffpatton1971/mod-posh/wiki/MySQL#Connect-MySqlServer
    #>
    [OutputType('MySql.Data.MySqlClient.MySqlConnection')]
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory)]
        [ValidateNotNullOrEmpty()]
        [pscredential]$Credential,

        [Parameter()]
        [ValidateScript({ Test-Connection -ComputerName $_ -Quiet -Count 1 })]
        [ValidateNotNullOrEmpty()]
        [string]$ComputerName = $env:COMPUTERNAME,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [int]$Port = 3306,

        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [string]$Database,

        # Added
        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [int]$CommandTimeOut = 15,

        # Added
        [Parameter()]
        [ValidateNotNullOrEmpty()]
        [int]$ConnectionTimeOut = 20
    )
    begin
    {
        $ErrorActionPreference = 'Stop'

        if ($PSBoundParameters.ContainsKey('Database')) {
            $connectionString = 'server={0};port={1};uid={2};pwd={3};database={4};' -f $ComputerName,$Port,$Credential.UserName, $Credential.GetNetworkCredential().Password,$Database
        }
        else
        {
            $connectionString = 'server={0};port={1};uid={2};pwd={3};' -f $ComputerName, $Port, $Credential.UserName, $Credential.GetNetworkCredential().Password
        }
        # Added
        $connectionString = $connectionString + "default command timeout=$CommandTimeOut; Connection Timeout=$ConnectionTimeOut;"
    }
    process
    {
        try
        {
            [MySql.Data.MySqlClient.MySqlConnection]$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
            $conn.Open()
            $Global:MySQLConnection = $,conn
            if ($PSBoundParameters.ContainsKey('Database')) {
                $null =  New-Object MySql.Data.MySqlClient.MySqlCommand("USE $Database", $conn)
            }
            $conn
        }
        catch
        {
            Write-Error -Message $_.Exception.Message
        }
    }
}