Tiempo de espera de MySQL en powershell
Tengo una configuración de MySQL DB en mi computadora portátil con Windows. Estoy usando un script de PowerShell para conectar / consultar / ingresar a la base de datos. Tengo una consulta que estoy tratando de ejecutar, puedo ejecutarla en MySQL Workbench, actualmente tarda 31.032 segundos en ejecutarse y devuelve 3 filas.
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)
Script de 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')')"
}
Cuando ejecuto el script de PowerShell, se agota el tiempo de espera. Así que busqué cambiar las opciones de tiempo de espera. Primero corrí
SET GLOBAL connect_timeout=31536000;
SET GLOBAL wait_timeout=2147483;
SET GLOBAL interactive_timeout=31536000;
Los números son el máximo permitido de la documentación de MySQL. Eso no lo cortó! Así que edité el archivo my.ini y agregué
[mysqld]
connect_timeout=31536000
wait_timeout=2147483
interactive_timeout=31536000
Reinicié el servicio MySQL. Sigue siendo el mismo problema!
Cuando el script se conecta a la base de datos, muestra
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 siempre ha mostrado 15 antes de editar el tiempo de espera y después de cada intento.
¿Qué me estoy perdiendo aquí chicos? Gracias de antemano por la ayuda.
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
Me estoy conectando a la base de datos según los pasos de este sitio:Consultar bases de datos MySQL con PowerShell
EDITAR
En la parte inferior está la función modificada. Pongo un comentario "Agregado" sobre cada nueva línea.
Nueva línea de conexión
Connect-MySqlServer -Credential $dbcred -ComputerName localhost -Database sudoku -CommandTimeOut 600 -ConnectionTimeOut 25
Nueva salida de conexión
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 :
Función 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
}
}
}