Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Importing a mysqldump file fails at line 1 with strange characters

ErrorERROR 1064 (42000) at line 1 when importing a .sql dump

Short answer

If the dump was created in Windows PowerShell with the > operator, the file is UTF-16, and MySQL cannot use UTF-16 as a client character set. Make the dump again with mysqldump --result-file so it is written by mysqldump itself. If you cannot dump again, convert the file to UTF-8 first.

  • Applies to MySQL 8.4, mysqldump 8.4, Windows PowerShell 5.1, PowerShell 7.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look at the first bytes of the file. A normal dump starts with readable text. A UTF-16 file has a null byte between every letter, so the hex view shows 00 after each character.

    Format-Hex dump.sql | Select-Object -First 4
  2. 2Match what you see, then jump to its fix

    1. 1Error at line 1 on a dump you made yourself in PowerShell with mysqldump ... > dump.sqlThe dump was written with > in Windows PowerShellFix
    2. 2The same error, and you cannot run mysqldump againThe server is gone and only the UTF-16 file is leftFix
    3. 3ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of ...You tried to force UTF-16 as the client character setFix

What does the error look like?

  • ERROR 1064 (42000) at line 1 when importing a .sql dump
  • ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'ucs2'

Cause 1 of 3

The dump was written with > in Windows PowerShell

1Check

In Windows PowerShell the redirection operators > and >> create UTF-16LE files. The MySQL manual says such a dump cannot be loaded correctly, because UTF-16 is not allowed as a connection character set.

Format-Hex dump.sql | Select-Object -First 4

2Fix

Dump again and let mysqldump create the file, which avoids the redirection. The manual says this writes ASCII-compatible output and also stops Windows from converting newlines.

mysqldump -u root -p --result-file=dump.sql mydb

3Verify

The first line of the new file is plain text, and the import runs.

Get-Content dump.sql -TotalCount 3
mysql -u root -p mydb < dump.sql

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The server is gone and only the UTF-16 file is left

1Check

PowerShell can read the file as UTF-16LE with the name unicode, and PowerShell 7 can write it back as UTF-8 without a BOM. Windows PowerShell 5.1 would add a BOM with its utf8 setting, so do the conversion in PowerShell 7 (pwsh).

Get-Content dump.sql -Encoding unicode -TotalCount 3

2Fix

Convert the file to UTF-8 without BOM in PowerShell 7. The conversion reads the whole file, so allow time and memory for a large dump.

Get-Content dump.sql -Encoding unicode | Set-Content dump-utf8.sql -Encoding utf8NoBOM

3Verify

Import the converted file.

mysql -u root -p mydb < dump-utf8.sql

Confirmed by learn.microsoft.com

Did this fix it?

Cause 3 of 3

You tried to force UTF-16 as the client character set

1Check

MySQL refuses ucs2, utf16, utf16le and utf32 as the client character set, in --default-character-set and in SET NAMES. A command that tries to match the file's encoding fails this way.

mysql --help | findstr default-character-set

2Fix

Do not set a UTF-16 character set on the client. Convert the file or dump it again, as in the causes above, and import with a normal set such as utf8mb4.

mysql -u root -p --default-character-set=utf8mb4 mydb < dump-utf8.sql

3Verify

The import finishes without error 1231 or 1064.

mysql -u root -p -e "SHOW TABLES" mydb

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • On Windows, always write dumps with --result-file, or run mysqldump from cmd.exe.
  • Open the first lines of a dump before importing it, especially if it came from someone else.
  • In PowerShell 7 and later, redirection writes UTF-8 without a BOM, so upgrading removes this trap for new dumps.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.