powershell中MySQL超时

Question

我在 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

我正在根据此站点上的步骤连接到数据库:Querying MySQL Databases with 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
        }
    }
}

Answer 1

将 default command timeout=60; 添加到 Powershell 脚本中的连接字符串。

您可能还想设置 MySqlCommand 的 CommandTimeout 属性对象。