Fix MySQL ERROR 1064 via SSH Escaping | NOC.org Support

MySQL ERROR 1064 is one of the most common database errors you will encounter. It indicates a syntax error in your SQL statement — MySQL could not parse the query you sent. While this error can occur for many reasons, one of the most frequent causes is shell escaping issues when passing SQL commands through the command line or SSH. This guide explains why it happens and how to fix it.

What ERROR 1064 Means

The full error typically looks like this:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near '...' at line 1

The 42000 is the SQL state code for a syntax error. MySQL is telling you that the SQL it received is not valid. The text after "near" shows you the point where MySQL stopped being able to parse the statement — the error is at or just before that position.

Why Shell Escaping Causes This Error

When you type a SQL command directly inside a MySQL prompt, the query goes to MySQL exactly as you typed it. But when you pass a SQL command through Bash or SSH, the shell interprets certain characters before MySQL ever sees them. Characters that the shell treats as special include:

• ! — history expansion in Bash
• $ — variable expansion
• ` — command substitution (backticks)
• \ — escape character
• ' and " — quoting characters
• *, ? — globbing/wildcards
• # — comment in shell context

If your SQL statement contains any of these characters — which passwords and identifiers very often do — the shell will modify or strip them before MySQL receives the query. MySQL then gets a malformed statement and returns ERROR 1064.

Example of the Problem

Suppose you run this from the command line:

mysql -u root -p -e "CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ss!w0rd$123';"

Bash will attempt to expand !w0rd as a history reference and $123 as a variable. The SQL that MySQL actually receives will look nothing like what you intended, resulting in ERROR 1064.

Method 1: Use Single Quotes for the Outer Shell String

Single quotes in Bash prevent all shell expansion. Nothing inside single quotes is interpreted by the shell:

mysql -u root -p -e 'CREATE USER '"'"'dbuser'"'"'@'"'"'localhost'"'"' IDENTIFIED BY '"'"'P@ss!w0rd$123'"'"';'

This approach is technically correct but extremely difficult to read because SQL itself uses single quotes. Every SQL single quote must be escaped by ending the Bash single-quoted string, adding an escaped quote, and starting a new single-quoted string. This method is not recommended for complex statements.

Method 2: Use the -e Flag with Proper Escaping

A cleaner approach with double quotes is to escape every special character individually:

mysql -u root -p -e "CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ss\!w0rd\$123';"

You must escape ! with \! and $ with \$. While this works for simple cases, it is error-prone for complex passwords or statements with many special characters.

Method 3: Use a Heredoc (Recommended)

A heredoc passes multi-line input to MySQL's standard input, bypassing shell expansion entirely when you quote the delimiter:

mysql -u root -p <<'EOF'
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ss!w0rd$123';
GRANT ALL PRIVILEGES ON mydb.* TO 'dbuser'@'localhost';
FLUSH PRIVILEGES;
EOF

The key detail is quoting the delimiter: <<'EOF' (with single quotes around EOF). This tells Bash not to expand any variables or special characters inside the heredoc. Without the quotes — <<EOF — Bash would still perform variable expansion inside the block.

Method 4: Use a SQL File (Recommended)

The most reliable method, especially for complex operations, is to put your SQL in a file and redirect it into MySQL:

# Create the SQL file
cat > /tmp/create_user.sql <<'EOF'
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ss!w0rd$123';
GRANT ALL PRIVILEGES ON mydb.* TO 'dbuser'@'localhost';
FLUSH PRIVILEGES;
EOF

# Execute it
mysql -u root -p < /tmp/create_user.sql

# Clean up (especially important if the file contains passwords)
rm /tmp/create_user.sql

Because the SQL is read from a file, the shell never interprets the contents. This method also makes it easy to test the SQL in an interactive MySQL session first, then run the same file from a script.

Method 5: Use the MySQL Prompt Directly

If you only need to run the command once and are already SSH'd into the server, the simplest approach is to enter the MySQL shell interactively:

mysql -u root -p
# Now at the mysql> prompt:
mysql> CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ss!w0rd$123';
mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'dbuser'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Inside the MySQL prompt, the shell is not involved at all — MySQL receives your input directly.

Other Common Causes of ERROR 1064

While shell escaping is a very common cause, ERROR 1064 also occurs when:

• Using reserved words as identifiers: Column or table names like order, group, select, or key must be wrapped in backticks: `order`.
• Missing commas or parentheses: A forgotten comma between column definitions in a CREATE TABLE statement will trigger this error.
• Version mismatch: Using syntax from a newer MySQL version on an older server. For example, CREATE USER IF NOT EXISTS was added in MySQL 5.7.
• Encoding issues: Copying SQL from a word processor or web page that uses "smart quotes" (curly quotes) instead of straight ASCII quotes.
• Incomplete statement: Missing a closing quote, parenthesis, or semicolon.

Debugging Tips

When you encounter ERROR 1064, use these steps to identify the cause:

1. Read the error message carefully. The text after "near" tells you exactly where parsing failed. The error is at or just before that point.
2. Echo the command first. Before piping to MySQL, echo the command to see what the shell produces:

   echo "CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'P@ss!w0rd$123';"

   If the output does not match your intended SQL, the shell is modifying it.
3. Test in an interactive session. Copy and paste the exact SQL into a mysql> prompt. If it works there but not from the command line, the issue is shell escaping.
4. Check MySQL version compatibility. Run SELECT VERSION(); and verify the syntax you are using is supported by that version.

Summary

ERROR 1064 means MySQL received a statement it could not parse. When this happens specifically when running SQL from Bash or SSH, the root cause is almost always shell escaping. The safest approaches are:

1. Use a quoted heredoc (<<'EOF') to pass SQL without shell expansion.
2. Write SQL to a file and execute with mysql < file.sql.
3. Use the interactive MySQL prompt for one-off commands.

Avoid passing complex SQL — especially statements containing passwords — directly on the command line with the -e flag unless you are certain about the escaping.