Restoring a dump stops with ERROR at line N: Unknown command
ERROR at line 1153: Unknown command '\''Short answer
The mysql client treats a backslash outside a quoted string as the start of one of its own short commands, so a stray one in an imported file gives Unknown command. Find the line the error names, then either load the file with --binary-mode if it carries binary data, or set --default-character-set to the charset the dump was written in.
Fix it fast
1Find out which cause you have
The error names a line number of the file. Print that line and a few lines around it, trimmed, to see what the client choked on.
sed -n '1150,1155p' dump.sql | cut -c1-2002Match what you see, then jump to its fix
- 1
The line contains raw bytes or a string with \0 or other control characters, for example mysqlbinlog output or an unencoded BLOBThe file holds binary data that the client parses as commandsFix - 2
The dump has non-ASCII text and the client default is a different character setThe client reads the file in a different character set than the dump was written inFix
- 1
What does the error look like?
ERROR at line 1153: Unknown command '\''ERROR at line 1153: Unknown command
Cause 1 of 2
The file holds binary data that the client parses as commands
1Check
By default mysql translates \r\n to \n and treats \0 as a statement terminator, and in this mode it also reads backslash short forms such as \g or \. as client commands. The line shown above will show such characters.
sed -n '1153p' dump.sql | cut -c1-200 | od -c | head2Fix
Load the file with --binary-mode. It switches off both translations and, for piped input or source, disables every mysql command except charset and delimiter. Note that this also disables commands your file may rely on.
mysql --binary-mode -u root -p mydb < dump.sql3Verify
The import runs past the line that failed, and a row count of the affected table is right.
mysql -u root -p mydb -e "SELECT COUNT(*) FROM my_table;"Confirmed by dev.mysql.com
Cause 2 of 2
The client reads the file in a different character set than the dump was written in
1Check
The client and connection character set come from the operating system and client defaults. If the file was written in another one, the text may be read wrongly and output formatted incorrectly. Compare the header of the dump with your client.
head -n 30 dump.sql | grep -i "character_set"
mysql -e "SHOW VARIABLES LIKE 'character_set_client'"2Fix
Tell the client the character set to use instead of leaving it to the system default. Use the one named in the dump header.
mysql --default-character-set=utf8mb4 -u root -p mydb < dump.sql3Verify
The import completes and a few non-ASCII values read back correctly.
mysql --default-character-set=utf8mb4 -u root -p mydb -e "SELECT name FROM my_table LIMIT 5;"Confirmed by dev.mysql.com
How do I stop it coming back?
- Keep binary data out of plain SQL files where you can, and load such files with --binary-mode.
- Record the character set of every dump and pass --default-character-set when you restore it.
- Do not paste or edit dumps in a tool that rewrites quotes or line endings; check the file with the sed command above first.
- Restore into a scratch database first and compare row counts.
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
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 fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix