Search code examples
powershellmysql-cli

I want the exit code as well as stderr output in powershell

I have code such as this in my linux machine, which I want to port over for windows using powershell:

mysql -udbuser -pPassword -hlocalhost mydb < my_sql_file.sql >> mylog.log 2>&1
if [! $? ]
then
  echo "Mysql invocation returned a failure" >> mylog.log
  exit 1
fi

In my powershell script, I have something like:

get-content "my_sql_file.sql" | mysql -udbuser -pPassword -hlocalhost mydb 3>&1 2>&1 >> mylog.log 
if (!$($?)) {
  write-output "Mysql invocation returned a failure" >> mylog.log
  exit 1
}

What I expect: In case of SQL runtime error the stderr message should be captured in the logs The mysql return code in case of success should pass by the if condition and execution should continue

What is happening: In case of mysql success it is still entering into the if

I have tried the below but encountering same or other problems:

  1. use | out-file mylog.log
  2. use | add-content mylog.log
  3. create a logger class with a log method, then use | % ${$Logger.log($_)}

Regardless of what I do, I think I am ending up losing the exit code of mysql Please show me how to write the code so that I can capture the mysql error dump as well as its return code so that my code knows when to abort its operation and use the error dump to report to user

Solution

    • For simplicity, use *>> to redirect all streams.

    • For robustness, check the automatic $LASTEXITCODE variable for the exit code of the most recently executed external program; the abstract, Boolean automatic $? variable can yield false negatives in PowerShell versions up to v7.1

    Get-Content "my_sql_file.sql" |
  mysql -udbuser -pPassword -hlocalhost mydb *>> mylog.log

if ($LASTEXITCODE -ne 0) {
  "Mysql invocation returned a failure" >> mylog.log
  exit 1
}

    Character-encoding notes:

    • In PowerShell, > is an effective alias of the Out-File cmdlet (and >> effectively calls Out-File -Append), whose default output character encoding in Windows PowerShell is "Unicode" (UTF-16LE) and in PowerShell (Core) 7+ BOM-less UTF8. To control the character encoding, call Out-File or, for text input, Set-Content / Add-Content with the -Encoding parameter. Note that you may also have to ensure that an external program's output is first properly decoded, which happens based on the encoding stored in [Console]::OutputEncoding. Note that you can't avoid these decoding and re-encoding steps as of PowerShell v7.2.4.

    • In Windows PowerShell, -Encoding utf8 invariably creates a UTF-8 file with a BOM; to avoid that, you'll have to call a .NET API directly, e.g., $output = some.exe ...; [System.IO.File]::WriteAllLines("$PWD/out.txt", $output)

    For instance, use the following variant of the commands above to create an ASCII file:

    Get-Content "my_sql_file.sql" |
  mysql -udbuser -pPassword -hlocalhost mydb *>&1 |
    Add-Content -Encoding Ascii mylog.log

if ($LASTEXITCODE -ne 0) {
  "Mysql invocation returned a failure" |
    Add-Content -Encoding Ascii mylog.log
  exit 1
}

    Note: Unlike Out-File -Append, Add-Content, when adding to an existing file, tries to match that file's existing encoding.