WP-CLI and database users

JaredBusch

@Pete-S well WTF... Even after the DB exists..

The DB_NAME is correct.....

[jbusch@jb-wp html]$ sudo mysql -e -uroot -p$DB_ROOT_PASS "GRANT ALL ON '$DB_NAME'.* TO '$DB_USER'@'localhost';"
ERROR 1049 (42000): Unknown database 'GRANT ALL ON 'ziiCh6geiqu6'.* TO 'eitaethie9cahX7u'@'localhost';'
[jbusch@jb-wp html]$ sudo mysql -e -uroot -p$DB_ROOT_PASS "GRANT ALL ON $DB_NAME.* TO '$DB_USER'@'localhost';"
ERROR 1049 (42000): Unknown database 'GRANT ALL ON ziiCh6geiqu6.* TO 'eitaethie9cahX7u'@'localhost';'
[jbusch@jb-wp html]$ cat wp-config.php | grep DB_NAME
define( 'DB_NAME', 'ziiCh6geiqu6' );
[jbusch@jb-wp html]$

1337

@JaredBusch said in WP-CLI and database users:

@Pete-S well WTF... Even after the DB exists..

The DB_NAME is correct.....

I don't understand why. What happens if you just login and do it manually? Do you get the same result?

"Unknown database". To me that is related to the default database set in mysql sessions. Do you have a USE command somewhere?

Best action might be to test all the SQL commands manually instead of from the script.

JaredBusch

@Pete-S said in WP-CLI and database users:

Do you have a USE command somewhere?

No, because when the script runs originally, there is no exisiting database to use.

1337

Alright, after some troubleshooting. The line on your script should be:

sudo mysql -e "GRANT ALL ON $DB_NAME.* TO '$DB_USER'@'localhost';"

No ' around $DB_NAME. That gives you a syntax error.

Also since DB_USER doesn't contain spaces and neither does localhost, you don't need any ' around those either (but it doesn't cause any syntax errors).

And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

sudo mysql -uroot -p$DB_ROOT_PASS -e "CREATE USER $DB_USER@localhost IDENTIFIED by '$DB_PASS';"
sudo mysql -uroot -p$DB_ROOT_PASS -e "GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost;"
sudo mysql -uroot -p$DB_ROOT_PASS -e "FLUSH PRIVILEGES;"

I'm sure you know but you can also put more than one command in the execute string. ; is what separates the commands.
Or put the SQL in a file.
For instance:

sudo mysql -uroot -p$DB_ROOT_PASS -e "GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost; FLUSH PRIVILEGES;"

JaredBusch

@Pete-S said in WP-CLI and database users:

And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

That was a once off artifact of me doing it on this system after the root password has been set.

That was also the reason it did not work for me as I tried without the '

1337

@JaredBusch said in WP-CLI and database users:

@Pete-S said in WP-CLI and database users:

And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

That was a once off artifact of me doing it on this system after the root password has been set.

OK, so maybe this then:

sudo mysql -e "CREATE USER $DB_USER@localhost IDENTIFIED by '$DB_PASS';"
sudo mysql -e "GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost;"
sudo mysql -e "FLUSH PRIVILEGES;"

JaredBusch

@Pete-S said in WP-CLI and database users:

I'm sure you know but you can also put more than one command in the execute string. ; is what separates the commands.

My guides are specifically wrote lik this to clearly separate the commands that are used for the people following my guides to see every thing they are doing.

JaredBusch

@Pete-S said in WP-CLI and database users:

@JaredBusch said in WP-CLI and database users:

@Pete-S said in WP-CLI and database users:

And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

That was a once off artifact of me doing it on this system after the root password has been set.

OK, so maybe this then:
sudo mysql -e "CREATE USER $DB_USER@localhost IDENTIFIED by '$DB_PASS';"
sudo mysql -e "GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost;"
sudo mysql -e "FLUSH PRIVILEGES;"

right. Updating the guide. but half tempted to leave the single quotes everywhere it that causes no error in order to protect against spaces by others. Though I am using pwgen to do this.

JaredBusch

Guide updated. I left the ' everywhere else.

Of note, I had to drop the DB_NAME to 16 characters because it broke it longer. Even though current mariadb should accept much longer database names according to my quick google on the subject.

1337

@JaredBusch said in WP-CLI and database users:

@Pete-S said in WP-CLI and database users:
@JaredBusch said in WP-CLI and database users:

@Pete-S said in WP-CLI and database users:

And when you use -e you should have it after user and password so the SQL commands you want to execute comes after the -e.

That was a once off artifact of me doing it on this system after the root password has been set.

OK, so maybe this then:
sudo mysql -e "CREATE USER $DB_USER@localhost IDENTIFIED by '$DB_PASS';"
sudo mysql -e "GRANT ALL ON $DB_NAME.* TO $DB_USER@localhost;"
sudo mysql -e "FLUSH PRIVILEGES;"
right. Updating the guide. but half tempted to leave the single quotes everywhere it that causes no error in order to protect against spaces by others. Though I am using pwgen to to this.

It's kind of f*cked up to have spaces in user names and passwords. Personally I don't use something unless it's specifically needed but either way works.