Importing a mysqldump file fails at line 1 with strange characters
ERROR 1064 (42000) at line 1 when importing a .sql dumpShort 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.
Fix it fast
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 42Match what you see, then jump to its fix
- 1
Error at line 1 on a dump you made yourself in PowerShell with mysqldump ... > dump.sqlThe dump was written with > in Windows PowerShellFix - 2
The same error, and you cannot run mysqldump againThe server is gone and only the UTF-16 file is leftFix - 3
ERROR 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
- 1
What does the error look like?
ERROR 1064 (42000) at line 1 when importing a .sql dumpERROR 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 42Fix
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 mydb3Verify
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.sqlConfirmed by dev.mysql.com
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 32Fix
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 utf8NoBOM3Verify
Import the converted file.
mysql -u root -p mydb < dump-utf8.sqlConfirmed by learn.microsoft.com
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-set2Fix
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.sql3Verify
The import finishes without error 1231 or 1064.
mysql -u root -p -e "SHOW TABLES" mydbConfirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
mysqldump says access denied (1045) with a password that works elsewhere
Access denied for user 'backup'@'localhost' (using password: NO)
Error 1045 means the server matched your connection to an account and rejected it.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix