MySQL Bugs: #73672: mysql can't read UTF16 encoded sql file

Bug #73672	mysql can't read UTF16 encoded sql file
Submitted:	21 Aug 2014 13:26	Modified:	22 Aug 2014 11:29
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.6.19	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	utf16

Description:
A dump made on windows in powershell with 'mysqldump.exe -A > dump.sql' has a UTF-16 encoding.

Trying to restore that with 'source dump.sql' in mysql or in cmd.exe with 'mysql.exe < dump.sql' fails.

I can imagine that there are more utilities which write UTF16 encoded sql files on Windows.

====================================================================
D:\mysql>.\bin\mysql.exe -u root -p < ps.sql
Enter password: ********
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless opti
on --binary-mode is enabled and mysql is run in non-interactive mode. Set --bina
ry-mode to 1 if ASCII '\0' is expected. Query: ' ■-'.
====================================================================

Related: Bug #73481

How to repeat:
create a dump in powershell
source with 'source dump.sql'

Suggested fix:
Detect UTF16 and handle correctly (on all platforms, not just on windows)

Somewhat related: Bug #68805

Hello Daniël,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

// I tried with 5.6.19 client with 5.6.21 server

// Backup from PowerShell prompt

PS D:\ushastry\mysql-advanced-5.6.19-winx64\bin> .\mysqldump -h x.x.x.x -uushastry --databases test > dump.sql

// restore from normal cmd prompt

D:\ushastry\mysql-advanced-5.6.19-winx64\bin>mysql.exe -u ushastry -h x.x.x.x < dump.sql
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: ' ¦-'.

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

Added to http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html:

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

mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.1.4, “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:

mysqldump [options] --result-file=dump.sql