title: Script hangs after query that takes long time to execute
body: <p>I have problems with a script in PowerShell that hangs after executing MySQL SELECT query, that takes in cases more than 10 minutes to execute.</p>
  
  
  
  <pre class="lang-bsh prettyprint-override"><code>$query = "SELECT S.DESCRIPTION, date_format(N.TIMESTAMP, '%H'), count(*)
      FROM NOTIFICATIONS N, SENDING_TYPE S
      WHERE N.ID_SENDING_TYPE = S.ID_SENDING_TYPE
      AND N.TIMESTAMP &gt; date_format ('"+$fecha+"', '%Y%m%d')
      GROUP BY S.DESCRIPTION, date_format(N.TIMESTAMP, '%H')"
  </code></pre>
  
  <p>Example - <code>$fecha="20170630"</code></p>
  
  <p>I execute the query with my function (here is where PowerShell hangs)</p>
  
  <pre class="lang-bsh prettyprint-override"><code>$resultado = Get-QueryMysql -Servidor $MySQLHost -Port $port -UserName $MySQLAdminUserName -Password $MySQLAdminPassword -DataBase $MySQLDatabase -Query $query
  </code></pre>
  
  <p>Here's the function <code>Get-QueryMysql</code>:</p>
  
  <pre class="lang-bsh prettyprint-override"><code>function Get-QueryMysql {
      [CmdletBinding()]
      [Alias()]
      [OutputType([int])]
      Param(
          # Servidor Mysql
          [Parameter(Mandatory=$true)]
          [String]$Servidor,
  
          # Puerto Oracle
          [Parameter(Mandatory=$true)]
          [String]$Port,
  
          # ServiceName
          [Parameter(Mandatory=$true)]
          [String]$DataBase,
  
          # Usuario
          [Parameter(Mandatory=$true)]
          [String]$UserName,
  
          # Password
          [Parameter(Mandatory=$true)]
          [String]$Password,
  
          # Query
          [Parameter(Mandatory=$true)]
          [String]$Query,
  
          # If Allow zero datetime
          [Parameter(Mandatory=$false)]
          [switch]$AllowZeroDateTime,
  
          # Reintentos
          # Query
          [Parameter(Mandatory=$false)]
          [int]$Reintentos=10,
  
          [switch]$AntiguoMysql
      )
  
      Begin {
          # Load the ODP assembly
          if ($AntiguoMysql.IsPresent) {
              Add-Type -Path C:\TRABAJO\REPORTES\modules\MySQL\mysql.data.dll -IgnoreWarnings
          } else {
              $null = [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
          }
      }
  
      Process {
          # Cadena de conexion
          $ConnectionString = "server=" + $Servidor + ";port=$port;uid=" + $UserName + ";pwd=" + $Password + ";database=" + $DataBase
  
          if ($AllowZeroDateTime.IsPresent) {
              $ConnectionString = "server=" + $Servidor + ";port=$port;uid=" + $UserName + ";pwd=" + $Password + ";database="+$DataBase+";Allow Zero Datetime=True"
          }
  
          $cont = 0
          for ($i=0; $i-lt $Reintentos; $i++) {
              $MysqlConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
              $MysqlConnection.ConnectionString = $ConnectionString
              $MysqlConnection.Open()
  
              $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $MysqlConnection)
              $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
              $DataSet = New-Object System.Data.DataSet
              $RecordCount = $dataAdapter.Fill($DataSet, "data")
  
              if ($DataSet) {
                  break;
              }
          }
          if (!$dataSet) {
              Write-Host "ERROR: Conectando a la BBDD"
              return $null
          }
  
          # Datos obtenidos
          return $DataSet.Tables[0]
      }
  
      End {
          # Cerrando Conexiones
          if ($MysqlConnection) {
              $MysqlConnection.Close()
              $MysqlConnection.Dispose()
          }
      }
  }
  </code></pre>
  
  <p>The query is a SELECT that filters by timestamp field. I tried to change the structure of the table, but my requirement in a production environment was denied. So I need to do that my script works, I only have problems with queries that takes a long time.</p>
  
  <p>I try using script-block command but it doesn't work.</p>
  
  <p>When process is executing the cpu is in 0.5% and 20 mb of ram (Doesn't have increment).</p>
  
  <p>When I execute the query from DBMS (Navicat) it takes 10 minutes, in somes cases more than that. I checked the MySQL database with <code>SHOW PROCESSLIST</code> and the query executed by the script finished succesfully after minutes, but the PowerShell script hangs.</p>
  
  <hr>
  
  <p>The script hangs after execute the following line:</p>
  
  <pre class="lang-bsh prettyprint-override"><code>$RecordCount = $dataAdapter.Fill($dataSet, "data")
  </code></pre>
  
  <hr>
  
  <p>After load another connector:</p>
  
  <pre class="lang-bsh prettyprint-override"><code>[void][system.reflection.Assembly]::LoadFrom("C:\modules\MySQL\6.9.8\mysql.data.dll")
  </code></pre>
  
  <p>the scripts doesn't hang, but now it happens an error exception:</p>
  
  <pre class="lang-bsh prettyprint-override"><code>Exception "Fill" arguments "2": "Fatal error encountered during command execution."
  +             $RecordCount = $dataAdapter.Fill($dataSet, "data")
  +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : MySqlException
  </code></pre>
  
  <p>Seeing the timeout variable in database, is setted to 8 hours:</p>
  
  <pre class="lang-bsh prettyprint-override"><code>interactive_timeout=28800
  wait_timeout=28800
  </code></pre>
  
  <p>The <code>SHOW PROCESSLIST</code> command shows that the connection is in <code>Sending Data</code> state.</p>
  
  <p>The <strong>Error Exception</strong> only occurs when the query take over 1 minute or more.. </p>