Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Restoring a dump stops with ERROR at line N: Unknown command

ErrorERROR 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.

  • Applies to MySQL 8.0, MySQL 8.4 client
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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-200
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The 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

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 | head

2Fix

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.sql

3Verify

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

Did this fix it?

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.sql

3Verify

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

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