0

I use Ubuntu 16.04 with Bash and MySQL Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper.

I try to create a DB stack for a webapp (WordPress) directly from command line and I share here the best way I know to do so and the problem I have with it (an answer might offer a better way).

I try to execute the following MySQL CLI code in which I use to create an authorized, all-privileged user + a DB instance with the same name:

cat <<-EOF | mysql -u root -p"$dbrootp_1"
    DROP USER IF EXISTS "$domain"@"localhost";
    DROP DATABASE IF EXISTS "$domain";
    CREATE USER "$domain"@"localhost" IDENTIFIED BY "$dbuserp_1";
    CREATE DATABASE "$domain";
    GRANT ALL PRIVILEGES ON "$domain".* TO "$domain"@"localhost";
EOF

Execution outputs the following error:

ERROR 1064 (42000) at line 2: 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 '"example.com"' at line 1

I'm confused by this error: Is the problem in line 1 or line 2? What is the syntax error here? I must use variable expansion.

Update according to answer

User l0b0, I tried working with ${x} instead "$x" as you have shown but sadly it outputs the error:

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 '-law.co.il@localhost' at line 1.

Output of cat without piping data to mysql:

DROP USER IF EXISTS example.com@localhost;
DROP DATABASE IF EXISTS example.com;
CREATE USER example.com@localhost IDENTIFIED BY MYPASSWORD;
CREATE DATABASE example.com;
GRANT ALL PRIVILEGES ON example.com.* TO example.com@localhost;
9
  • Which version of MySQL?
    – Olorin
    Mar 5, 2018 at 2:19
  • mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper Mar 5, 2018 at 2:21
  • That is new enough for DROP USER IF EXISTS. What is the output of printf "%q\n" "$domain"?
    – Olorin
    Mar 5, 2018 at 2:24
  • The output for printf "%q\n" "$domain" is the domain: exmaple.com. Mar 5, 2018 at 2:33
  • 1
    This is a useless use of cat. You can send stdin straight to mysql.
    – jordanm
    Mar 5, 2018 at 2:41

1 Answer 1

1

As @jordanm points out, within here documents quotes are literal, not syntactical. So you want

mysql -u root -p"$dbrootp_1" <<-EOF
    DROP USER IF EXISTS ${domain}@localhost;
    DROP DATABASE IF EXISTS ${domain};
    CREATE USER ${domain}@localhost IDENTIFIED BY ${dbuserp_1};
    CREATE DATABASE ${domain};
    GRANT ALL PRIVILEGES ON ${domain}.* TO ${domain}@localhost;
EOF

If you have special characters in your $domain or $dbuserp_1 you'll need to quote them in backticks:

mysql -u root -p"$dbrootp_1" <<-EOF
    DROP USER IF EXISTS \`${domain}\`@localhost;
    DROP DATABASE IF EXISTS \`${domain}\`;
    CREATE USER \`${domain}\`@localhost IDENTIFIED BY \`${dbuserp_1}\`;
    CREATE DATABASE `${domain}`;
    GRANT ALL PRIVILEGES ON \`${domain}\`.* TO \`${domain}\`@localhost;
EOF
9
  • Sadly this outputs an error, pelase see update in question. Mar 5, 2018 at 3:28
  • Try catting the command rather than passing it to mysql to see what the command actually ends up being.
    – l0b0
    Mar 5, 2018 at 3:31
  • Well the output in cat looks fine to me... I see the proper names in their places. Mar 5, 2018 at 3:33
  • Please update your original question with the output of catting your command, so we can see what is being sent.
    – l0b0
    Mar 5, 2018 at 3:34
  • Just updated. Please see. Mar 5, 2018 at 3:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .