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;
  • Which version of MySQL? – Olorin Mar 5 '18 at 2:19
  • mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper – user9303970 Mar 5 '18 at 2:21
  • That is new enough for DROP USER IF EXISTS. What is the output of printf "%q\n" "$domain"? – Olorin Mar 5 '18 at 2:24
  • The output for printf "%q\n" "$domain" is the domain: exmaple.com. – user9303970 Mar 5 '18 at 2:33
  • 1
    This is a useless use of cat. You can send stdin straight to mysql. – jordanm Mar 5 '18 at 2:41
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
  • Sadly this outputs an error, pelase see update in question. – user9303970 Mar 5 '18 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 '18 at 3:31
  • Well the output in cat looks fine to me... I see the proper names in their places. – user9303970 Mar 5 '18 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 '18 at 3:34
  • Just updated. Please see. – user9303970 Mar 5 '18 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.