Powershell Export-CSV from MySQL database reader fails mid-export

Question

I'm a bit new to PowerShell, and I've got a new requirement to get Data out of a MySQL database and into an Oracle one. The strategy I chose was to output to a CSV and then import the CSV into Oracle.

I wanted to get a progress bar for the export from MySQL into CSV, so I used the data reader to achieve this. It works, and begins to export, but somewhere during the export (around record 5,000 of 4.5mil -- not consistent) it will throw an error:

Exception calling "Read" with "0" argument(s): "Fatal error encountered during data read." Exception calling "Close" with "0" argument(s): "Timeout in IO operation" Method invocation failed because [System.Management.Automation.PSObject] does not contain a method named 'op_Addition'. Exception calling "ExecuteReader" with "0" argument(s): "The CommandText property has not been properly initialized."

Applicable code block is below. I'm not sure what I'm doing wrong here, and would appreciate any feedback possible. I've been pulling my hair out on this for days.

Notes: $tableObj is a custom object with a few string fields to hold table name and SQL values. Not showing those SQL queries here, but they work.

Write-Host "[INFO]: Gathering data from MySQL select statement..."
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection
$conn.ConnectionString = $MySQLConnectionString
$conn.Open()

#
# Get Count of records in table
#
$countCmd = New-Object MySql.Data.MySqlClient.MySqlCommand($tableObj.SqlCount, $conn)
$recordCount = 0
try{
  $recordCount = $countCmd.ExecuteScalar()
} Catch { 
  Write-Host "[ERROR]: (" $tableObj.Table ") Error getting Count."
  Write-Host "---" $_.Exception.Message
  Exit
} 
$recordCountString = $recordCount.ToString('N0')
Write-Host "[INFO]: Count for table '" $tableObj.Table "' is " $recordCountString

#
# Compose the command
#
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($tableObj.SqlExportInit, $conn)

#
# Write to CSV using DataReader
#
Write-Host "[INFO]: Data gathered into memory. Writing data to CSV file '" $tableObj.OutFile "'"
$counter = 0 # Tracks items selected
$reader=$cmd.ExecuteReader()
$dataRows = @()
# Read all rows into a hash table
while ($reader.Read())
{
    $counter++
    $percent = ($counter/$recordCount)*100
    $percentString = [math]::Round($percent,3)
    $counterString = $counter.ToString('N0')
    Write-Progress -Activity '[INFO]: CSV Export In Progress' -Status "$percentString% Complete" -CurrentOperation "($($counterString) of $($recordCountString))" -PercentComplete $percent 
    $row = @{}
    for ($i = 0; $i -lt $reader.FieldCount; $i++)
    {
        $row[$reader.GetName($i)] = $reader.GetValue($i)
    }
    # Convert hashtable into an array of PSObjects
    $dataRows += New-Object psobject -Property $row            
}
$conn.Close()
$dataRows | Export-Csv $tableObj.OutFile -NoTypeInformation

EDIT: Didn't work, but I also added this line to my connection string: defaultcommandtimeout=600;connectiontimeout=25 per MySQL timeout in powershell

Have you tried to look at this? stackoverflow.com/questions/2546115/… — CodeNagi
– CodeNagi, Commented Jul 27, 2018 at 18:14
Just to give another approach, I'd suggest to use the database's native export capacities instead of letting Powershell build the CSV. That's likely to be faster, too. — Tomalak
– Tomalak, Commented Jul 27, 2018 at 18:39
@Tomalak I had thought of that, too, but I've got restrictions. One of the requirements is that this has to have a progress indicator (though I can argue out of that). I also won't have access to the MySQL server or it's host system to do the native export. I need to do what I can with a connection string and limited credentials if at all possible. — Thomas
– Thomas, Commented Jul 27, 2018 at 19:48

HAL9256 · Accepted Answer · 2018-07-27 18:32:58Z

0

Using @Carl Ardiente's thinking, the query is timing out, and you have to set the timeout to something insane to fully execute. You simply have to set the timeout value for your session before you start getting data.

Write-Host "[INFO]: Gathering data from MySQL select statement..."
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection
$conn.ConnectionString = $MySQLConnectionString
$conn.Open()

# Set timeout on MySql

$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("set net_write_timeout=99999; set net_read_timeout=99999", $conn) 
$cmd.ExecuteNonQuery()

#
# Get Count of records in table
#
...Etc....

answered Jul 27, 2018 at 18:32

HAL9256

13.7k1 gold badge39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Over a year ago

Sadly no dice. It looked like it proceeded farther than usual, but still fails with the same error message. I moved my $conn.Open() before the command creation (instead of after as it was), but that did not help either. I'm thinking this is a special setting for the reader, as I'm not using .ExecuteNonQuery()

Thomas · Accepted Answer · 2018-08-07 18:22:10Z

0

Not that I've found the solution, but none of the connection string changes worked. Manually setting the timeout didn't seem to help either. It seemed to be caused from too many rows returned, so I broke up the function to run in batches, and append to a CSV as it goes. This gets rid of the IO / timeout error.

answered Aug 7, 2018 at 18:22

Thomas

3591 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

Powershell Export-CSV from MySQL database reader fails mid-export

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related