Before creating a new mysql user and DB, I did:

read sps -s 
    # Inputing and saving the sps (password) value.

echo ${sps}
    # sps password value echoed.

mysql -u root -p[PASSWORD]
        CREATE user "test"@"localhost" IDENTIFIED BY "${sps}";

The problem

When I came to login with mysql -u test -p[SPS_PASSWORD_VALUE] I got an error that access has been denied.

The password wasn't the value I gave for ${sps} but rather ${sps} itself, as a string.

The catch

To prove "formally" that the problem is due to the ${sps} variable not being expanded **inside** mysql shell, and therefore acts as a regular string, I created another usertest1` providing a password manually (not from a variable) and this time I could login just fine.

The question

Why when I'm inside the mysql shell, there's no variable expansion and how could I prevent that behavior or at least getting a behavior close to that?

Maybe it's a bug?

  • 1
    Variable expansion is working in bash (shell), and mysql cli is not bash. This is the expected operation. – Ipor Sircer Dec 11 '17 at 9:10

You can't because the MySQL Shell does not expand environment variables. The Password of user test would be ${sps} literally. But you can let Bash expand the variable and feed the result into MySQL:

echo "CREATE user \"test\"@\"localhost\" IDENTIFIED BY \"${sps}\";" | mysql -u root -p[PASSWORD]
  • Thanks, in the end I used something similar: echo "CREATE USER 'test'@'localhost' IDENTIFIED BY \"${sps}\";" | mysql -u root -p"${rps}". – Arcticooling Dec 12 '17 at 6:41

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.