MySQL Bugs: #73481: Restore from SQL-file needs powershell tips

Bug #73481	Restore from SQL-file needs powershell tips
Submitted:	6 Aug 2014 8:40	Modified:	5 Jul 2016 18:36
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Microsoft Windows
Assigned to:	Philip Olson	CPU Architecture:	Any

Description:
Page: http://dev.mysql.com/doc/refman/5.6/en/reloading-sql-format-dumps.html

Using this won't work in powershell:
mysql.exe < backup.sql

This should work:
Get-Content backup.sql | mysql.exe

However it will probably fail for larger backups:
http://stackoverflow.com/questions/4788935/powershell-pipe-file-contents-into-application-...

How to repeat:
Try to restore a .sql backup file in powershell.

It might also be a better solution to create a powershell compatible program for processing SQL files. This is because stdin in powershell works differently than cmd.exe/bash/csh.

Like this:
mysqlsource.exe -f backup.sql

Or this:
mysql.exe --source backup.sql

This should already work, but it's not as userfriendly/easy.
mysql.exe -e "source d:\data\backup.sql"
I don't know if this should work, as the \ might need to be changed to / or \\.

Hello Daniël,

Thank you for the report.

Thanks,
Umesh

//
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\ushastry> cd D:\ushastry\mysql-advanced-5.6.19-winx64\bin
PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> mysql -h x.x.x.x -uushastry -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
..
..

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q
Bye
PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> mysql -h x.x.x.x -uushastry -p < dump.sql
The '<' operator is reserved for future use.
At line:1 char:39
+ mysql -h x.x.x.x -uushastry -p < <<<<  dump.sql
    + CategoryInfo          : ParserError: (<:OperatorToken) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin>

PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> get-content dump.sql | mysql -h x.x.x.x -uushastry -p
Enter password:
PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin>

I never was able to make any MySQL command line client work in Powershell at all.

It was nice if it worked as Powershell has scripting options etc. etc. not unlike bash in Linux for instance.

But I think that MySQL/Sun/Oracle never considered Powershell. In my understanding it would also require some kind of wrapper written in a .NET language. IMO it would be more useful to have this than the current utilities for Windows ('MySQL for Excel', 'MySQL notifier' etc.).

-- Peter
-- not a MySQL/Oracle person

A mysqldump created with 'mysqldump.exe -A > backup_powershell.sql'
will have a different file encoding than a dump made with the same command in cmd.exe.

The dump made in cmd.exe can be restrored with 'mysql> source dump.sql', but the one made with powershell can't. The work-around is to use 'Get-Content dump.sql | mysql.exe'

With powershell: UTF16LE
With cmd.exe   : UTF8

Instead of using '> dump.sql' in powershell this can be done to create a UTF8 encoded file:
mysqldump.exe -A | Out-File dump.sql -Encoding UTF8

PowerShell usage goes beyond the scope of the MySQL manual, so this will not
be documented. Thankfully its usage, in several ways, is covered on the
Internet.

Thank said, thank you for the request and sorry it cannot be accommodated.

PowerShell is mentioned in the manual already:
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

"""
Note

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:

shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.1.5, “Connection Character Sets and Collations”), so the dump file will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql
"""

I think this is sufficient for most cases, so I won't reopen this bug.

Thank you for following up on this, soon I'll update the docs accordingly. Reopening this bug in the meantime.

The following note was added:

Note:
For Windows PowerShell users: Because the "<" character is
reserved for future use in PowerShell, an alternative approach
is required, such as using quotes 
  cmd.exe /c "mysql < dump.sql"

Additional suggestions and feedback is welcome, and thank you for reporting this bug (and sorry it took so long to handle).