1
mysql -u root -p"$MYSQL_ROOT_PASS" -e "CREATE DATABASE IF NOT EXISTS $MYSQL_DB; GRANT ALL PRIVILEGES ON  $MYSQL_DB.* TO $MYSQL_DB@localhost;"

Prints this error:

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

db name is losk_net-simple_ru

3
  • 3
    You must backtick-quote the database name since it contains -. You'll probably have to backslash escape the backticks in bash so they don't cause execution. See stackoverflow.com/questions/11321491/… Commented Sep 8, 2013 at 13:26
  • mysql -u root -p"$MYSQL_ROOT_PASS" -e "CREATE DATABASE IF NOT EXISTS "$MYSQL_DB"; GRANT ALL PRIVILEGES ON "$MYSQL_DB".* TO "$MYSQL_DB"@localhost;" same error mysql -u root -p"$MYSQL_ROOT_PASS" -e "CREATE DATABASE IF NOT EXISTS $MYSQL_DB; GRANT ALL PRIVILEGES ON $MYSQL_DB.* TO $MYSQL_DB@localhost;" error ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' ' at line 1 Commented Sep 8, 2013 at 13:43
  • See my full answer below. You need backslashes, and also to quote the value as a username in the GRANT. Commented Sep 8, 2013 at 14:11

1 Answer 1

2

Since your database name contains a hyphen -, you will need to backtick-quote it. In order to do that successfully inside a double-quoted Bash string, to surround a variable for expansion, you will need to backslash-escape the backticks.

Later, you reuse the variable $MYSQL_DB as a username. In that context, it must be single-quoted as a string like '$MYSQL_DB'.

mysql -u root -p"$MYSQL_ROOT_PASS" -e "CREATE DATABASE IF NOT EXISTS \`$MYSQL_DB\`; GRANT ALL PRIVILEGES ON \`$MYSQL_DB\`.* TO '$MYSQL_DB'@localhost;"
#------------------------------------------------------------------^^^^^^^^^^^^^^^^^-------------------------------------------^^^^^^^^^^^^

When testing this execution however, I receive:

ERROR 1470 (HY000) at line 1: String 'losk_net-simple_ru' is too long for user name (should be no longer than 16)

You must therefore choose a different username than the database name if you encounter a similar error.

For reference on what types of quotes are used on MySQL strings and identifiers, refer to this question. Although it is PHP-related, most of it is still relevant here.

In general, for this situation, I would recommend against including a - in the database name. You will always have to backtick-quote it in every context, not just via Bash. Stick to the set of [0-9,a-z,A-Z$_] for identifiers, to save yourself future headaches.

Sign up to request clarification or add additional context in comments.

1 Comment

I do this MYSQL_DB=echo $USER | sed -e 's/\./\_/g;s/\-/\_/g' | cut -c1-15 mysql -u root -p"$MYSQL_ROOT_PASS" -e "CREATE DATABASE IF NOT EXISTS "$MYSQL_DB"; GRANT ALL PRIVILEGES ON "$MYSQL_DB".* TO "$MYSQL_DB"@localhost;" and all good now thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.