Tempo limite do MySQL no PowerShell
Eu tenho uma configuração do MySQL DB no meu laptop Windows. Estou usando um script do PowerShell para conectar / consultar / inserir o banco de dados. Eu tenho uma consulta que estou tentando executar, posso executá-la no MySQL Workbench. Atualmente, leva 31.032 seg para executar e retorna 3 linhas.
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 Script
$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')')"
}
Quando executo o script do PowerShell, ele atinge o tempo limite. Então, eu pensei em mudar as opções de tempo limite. Eu corri pela primeira vez
SET GLOBAL connect_timeout=31536000;
SET GLOBAL wait_timeout=2147483;
SET GLOBAL interactive_timeout=31536000;
Os números são os máximos permitidos na documentação do MySQL. Isso não deu certo! Então eu editei o arquivo my.ini e adicionei
[mysqld]
connect_timeout=31536000
wait_timeout=2147483
interactive_timeout=31536000
Eu reiniciei o serviço MySQL. Ainda é o mesmo problema!
Quando o script se conecta ao banco de dados, ele exibe
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 :
O ConnectionTimeout sempre exibia 15 antes da edição do tempo limite e após cada tentativa.
O que estou perdendo aqui pessoal? Obrigado antecipadamente pela ajuda.
EDITAR# 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
Estou me conectando ao banco de dados com base nas etapas deste site:Consultando bancos de dados MySQL com o PowerShell
EDITAR
Na parte inferior é a função modificada. Coloquei um comentário "Adicionado" acima de cada nova linha.
Nova linha de conexão
Connect-MySqlServer -Credential $dbcred -ComputerName localhost -Database sudoku -CommandTimeOut 600 -ConnectionTimeOut 25
Nova saída de conexão
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 :
Função Modificada
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
}
}
}