-1

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]
    mysql>
        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?

Arcticooling
  • 1
  • 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 09:10
  • Related: https://unix.stackexchange.com/questions/209971/is-there-any-way-to-print-value-inside-variable-inside-single-quote – Jesse Nickles Feb 15 '23 at 19:26

1 Answers1

6

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]
Frank
  • 348
  • 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 06:41