[MDEV-25326] mysql_install_db help text incomplete Created: 2021-04-01  Updated: 2021-07-15  Resolved: 2021-04-27

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type: Bug Priority: Minor
Reporter: Sergei Golubchik Assignee: Sergei Golubchik
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Duplicate
duplicates MDEV-25318 mysql_install_db.sh wrong instruction... Closed

 Description   

mysql_install_db prints at the end

    echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !"
    echo "To do so, start the server, then issue the following commands:"
    echo
    echo "'$bindir/mysqladmin' -u root password 'new-password'"
    echo "'$bindir/mysqladmin' -u root -h $hostname password 'new-password'"
    echo
    echo "Alternatively you can run:"
    echo "'$bindir/mysql_secure_installation'"
    echo

But in fact it creates four root accounts, root@localhost, root@hostname, root@127.0.0.1, root@::1, the last two only accessible with skip_name_resolve=1 and (for the last one) with ipv6 support.

How to fix the instruction to cover all root accounts? There are various possibilities.

Option one

    echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !"
    echo "To do so, start the server with the --skip-name-resolve, then issue the following commands:"
    echo
    echo "'$bindir/mysqladmin' -u root password 'new-password'"
    echo "'$bindir/mysqladmin' -u root -h $hostname password 'new-password'"
    echo "'$bindir/mysqladmin' -u root -h 127.0.0.1 password 'new-password'"
    echo "'$bindir/mysqladmin' -u root -h ::1 password 'new-password'"
    echo
    echo "Alternatively you can run:"
    echo "'$bindir/mysql_secure_installation'"
    echo

This explicitly tells the user to "start the server with the --skip-name-resolve", not the easiest approach.

Option two

    echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !"
    echo "To do so, start the server, then issue the following commands:"
    echo
    echo "'$bindir/mysql' -u root -e 'UPDATE mysql.user SET password=password(\"new-password\") WHERE user=\"root\";flush privileges'"
    echo
    echo "Alternatively you can run:"
    echo "'$bindir/mysql_secure_installation'"
    echo

This updates all root users and only one command instead of four (or two). But more sensitive to correct pasting and to quotes inside a password. And it does exactly what the manual tells not to do — direct update of a privilege table.

Option three

    echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !"
    echo "To do so, start the server, then issue the following commands:"
    echo
    echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"$hostname\" = PASSWORD(\"new-password\")'" 
    echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"127.0.0.1\" = PASSWORD(\"new-password\")'" 
    echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"::1\" = PASSWORD(\"new-password\")'" 
    echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"localhost\" = PASSWORD(\"new-password\")'" 
    echo
    echo "Alternatively you can run:"
    echo "'$bindir/mysql_secure_installation'"
    echo

No direct updates, but four commands to copy, four times to repeat the password. And as sensitive to correct pasting and quotes as the option two.

Option four

    echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !"
    echo "To do so, start the server, then run:"
    echo "'$bindir/mysql_secure_installation'"
    echo

The shortest possible. But will ask more questions than just about root password.

Option five

If our task is to make it as easy as possible for the end user, how about this:

mariadb_set_password  --user=root [--for-user=root] [--for-hostname=hostname, hostname]

If for-hostname is not set, then it would set for all accounts for that user. If for-user is not set, then change the account for the current user.
This would also work nicely for installations where the root user is renamed.

The benefit of a script is that in that one we can, in case of no hostname, we can directly manipulate that user table if we would need to do that.

Another option is to extend mariadb_admin to do this, but with the same options as above.

This would allow us to write:

    echo "PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !"
    echo "To do so, start the server, then run:"
    echo "mariadb_set_password --user=root"
    echo "or"
    echo "'$bindir/mysql_secure_installation'"



 Comments   
Comment by Elena Stepanova [ 2021-04-01 ]

It doesn't really affect 10.4+, only 10.2 and 10.3, as 10.4+ creates different accounts and prints an entirely different message.
For 10.2/10.3, I think it's sufficient to print something like

<bindir>/mysql -u root -e "SET PASSWORD FOR root@'::1' = PASSWORD('new-password')"
<bindir>/mysql -u root -e "SET PASSWORD FOR root@127.0.0.1 = PASSWORD('new-password')"
<bindir>/mysql -u root -e "SET PASSWORD FOR root@<hostname> = PASSWORD('new-password')"
<bindir>/mysql -u root -e "SET PASSWORD FOR root@localhost = PASSWORD('new-password')"

Comment by Sergei Golubchik [ 2021-04-01 ]

10.4+ versions are also affected, they print this text if unix_socket authentication was disabled.

Comment by Elena Stepanova [ 2021-04-01 ]

In this case I suggest to keep only the part about mysql_secure_installation, as a clear official recommendation.
It doesn't eliminate other possibilities which advanced users would know about anyway and could use if they want to.
The printout is mainly targeted at first-timers, and for them it makes most sense to use the interactive and most secure option. It is not that bad that it asks more questions than just a password change – it is not very long and can be helpful.

Comment by Daniel Black [ 2021-04-02 ]

As part of MDEV-23494 I was planning on adding a mysql_install_db --auth-root-password-env and --auth-root-hostname option. I've got a shell/sql escaping mechanism ready - https://github.com/MariaDB/mariadb-docker/pull/356 .

I'd rather never see an option 2 regardless of release version, because users will try it later anyway, put it in blogs, SO answers and cause everyone to suffer.

Like https://mariadb.com/kb/en/root-password-change-how-can-i-stop-the-mysqld-in-the-mariadb-container/ it seems the use of mysql_secure_installation is still quite popular. There does seem to be an excessive amount of copy/paste of it (https://github.com/vitessio/vitess/pull/7318, https://github.com/MariaDB/mariadb-docker/blob/master/docker-entrypoint.sh#L277), so I'd like to see some cleanup of it eventually so it has more portability than it really needs (https://github.com/vitessio/vitess/pull/7460).

My preference is option 3 so people actually know about `set password`.

Lets remove mysql references as if people search there for mysql_install_db they'll see 8.0 removed documentation which won't help.

When can we drop the root@<hostname>? Its really ugly and depends on proper reverse DNS resolution.

Comment by Roel Van de Paar [ 2021-04-02 ]

I too like option 3, though would reverse it, i.e. mention mysql_secure_installation first, and then the commands to directly set the passwords as the alternative. This gives beginner users an easy method, and leaves the handy commands there for pro users to copy/paste.

Also, changing 'Alternatively you can run:' to 'Alternatively you can run (after starting the server):' makes the text somewhat more clear imho, even if it is mentioned earlier already.

Comment by Valerii Kravchuk [ 2021-04-02 ]

Probably option 3 is the best (with Roel's reverse idea considered, it may be counter productive if the text scrolls up on the terminal).

As a side note, what if we create some stored procedure like mysql.set_root_password('new_password') with the new root password as an argument, and update instructions to call it? Then no matter how many local root accounts are present and what method of update we use, it will be a single stored procedure call for the user.

Comment by Sergei Golubchik [ 2021-04-02 ]

Option 3 one runs mysql four times as passwordless root, so SET PASSWORD FOR root@localhost must be the last. Unless one has protocol=tcp in the ~/.my.cnf in which case some other SET PASSWORD line must be the last. One way out of it would be to run mysql only once, like

    echo "To do so, start the server, then issue the following commands:"
    echo
    echo "'$bindir/mysql' -u root -e 'SET PASSWORD FOR root@\"$hostname\" = PASSWORD(\"new-password\"); SET PASSWORD FOR root@\"127.0.0.1\" = PASSWORD(\"new-password\")'; SET PASSWORD FOR root@\"::1\" = PASSWORD(\"new-password\")'; SET PASSWORD FOR root@\"localhost\" = PASSWORD(\"new-password\")'" 
    echo

or even

    echo "To do so, start the server, then issue the following commands:"
    echo
    echo "'$bindir/mysql' -u root"
    echo "SET PASSWORD FOR root@'$hostname' = PASSWORD('new-password');"
    echo "SET PASSWORD FOR root@'127.0.0.1' = PASSWORD('new-password')';"
    echo "SET PASSWORD FOR root@'::1' = PASSWORD('new-password')';"
    echo "SET PASSWORD FOR root@localhost = PASSWORD('new-password');"
    echo "\q"
    echo

perhaps modified as

    echo "To do so, start the server, then issue the following commands:"
    echo
    echo "'$bindir/mysql' -u root"
    echo "SET @password= PASSWORD('new-password');"
    echo "SET PASSWORD FOR root@'$hostname' = @password;"
    echo "SET PASSWORD FOR root@'127.0.0.1' = @password;"
    echo "SET PASSWORD FOR root@'::1' = @password;"
    echo "SET PASSWORD FOR root@localhost = @password;"
    echo "\q"
    echo

but really, I'd rather prefer a simple solution and just go with option four.

Comment by Daniel Black [ 2021-04-06 ]

I'm ok with option four. I like the last option above too.

I'd rather not end up with another less maintained script like option 5.

I think the main users are going to be packagers/sysadmins rather than first time users, but if we can account for both, more the merrier.

Comment by Sergei Golubchik [ 2021-07-15 ]

Bad merge, the fix didn't get into 10.3+. Re-fixing.

Generated at Thu Feb 08 09:36:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.