Bug 510724 – mysql-multilib-r1.eclass: Setting ROOT password via "emerge --config dev-db/{mysql,mariadb,percona-server}" through "/root/.my.cnf" doesn't work

• Product: Gentoo Linux
• Status: RESOLVED FIXED

[Description Thomas Deutschmann (RETIRED) 2014-05-19 10:28:07 UTC]

Hi,

please see my "/root/.my.cnf" and "/etc/mysql/my.cnf" from bug 510720.

When you initialize a new DATADIR via "emerge --config percona-server" (well, as learned from bug 510720, you have to set "tempdir" in your my.cnf) which read the password from "/root/.my.cnf" you will be unable to login after you initialized the DATADIR and restarted the mysqld:

# emerge --config percona-server


Configuring pkg...

 * Creating the mysql database and setting proper
 * permissions on it ...
 * Command: '/usr/share/mysql/scripts/mysql_install_db' '--basedir=/usr' --log-warnings=0 --loose-skip-grant-tables --loose-skip-host-cache --loose-skip-name-resolve --loose-skip-networking --loose-skip-slave-start --loose-skip-ssl --loose-skip-log-bin --loose-skip-relay-log --loose-skip-slow-query-log --loose-skip-external-locking --loose-skip-log-slave-updates --user=mysql '--datadir=///srv/mysql/data/'
 * Starting mysqld ...
 * Command //usr/sbin/mysqld            --log-warnings=0 --loose-skip-grant-tables --loose-skip-host-cache --loose-skip-name-resolve --loose-skip-networking --loose-skip-slave-start --loose-skip-ssl --loose-skip-log-bin --loose-skip-relay-log --loose-skip-slow-query-log --loose-skip-external-locking --loose-skip-log-slave-updates --user=mysql              --user=mysql         --log-warnings=0                 --basedir=//usr                 --datadir=///srv/mysql/data/            --max_allowed_packet=8M                 --net_buffer_length=16K                 --default-storage-engine=MyISAM               --socket=//var/run/mysqld/mysqld17362.sock              --pid-file=//var/run/mysqld/mysqld14828.pid
.2014-05-19 11:27:26 0 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --slow-query-log is not set
2014-05-19 11:27:26 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).  [ ok ]
 * Setting root password ...                                                                                                                                                           [ ok ]
 * Loading "zoneinfo", this step may require a few seconds ... ...
Warning: Using a password on the command line interface can be insecure.                                                                                                               [ ok ]
 * Stopping the server ...
 * Done

 /etc/init.d/mysql status
 * status: stopped
# pgrep mysql
# /etc/init.d/mysql start
 * Starting mysql ...                                                                                                                                                                   [ ok ]
# pgrep mysql
15498
# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)


It doesn't look like a DNS problem (@localhost).

When I restart mysqld with "--skip-grant-tables" and set the password from "/root/.my.cnf" on my own, e.g.

# mysql -uroot mysql
mysql> UPDATE user SET Password=PASSWORD('cvNTCLgb23qLE1X0') where USER='root';
mysql> FLUSH PRIVILEGES;
# mysqladmin -h 127.0.0.1 -u root shutdown

...and restart mysqld in normal mode, it will work.

When I don't have "/root/.my.cnf" so that I'll get a password prompt, this will work.

Setting the env var "MYSQL_ROOT_PASSWORD" doesn't work to (I still get a password prompt, seems like the env var isn't recognized):

 # export MYSQL_ROOT_PASSWORD=cvNTCLgb23qLE1X0
 # emerge --config percona-server


Configuring pkg...

 * Please provide a password for the mysql 'root' user now, in the
 * MYSQL_ROOT_PASSWORD env var or through the /root/.my.cnf file.
 * Avoid ["'\_%] characters in the password
    >

[Comment 1 Thomas Deutschmann (RETIRED) 2015-09-26 04:45:00 UTC]

Still a problem:

1) emerge dev-db/percona-server

2) rm /usr/share/zoneinfo/Factory (required due to bug 510696)

3) Create /root/.my.cnf with

# cat ~/.my.cnf
[mysql]
user     = root
password = iSib7ME01cdM5kTT

[mysqladmin]
user     = root
password = iSib7ME01cdM5kTT

[client]
user     = root
password = iSib7ME01cdM5kTT

4) emerge --config percona-server

5) /etc/init.d/mysql start

6) mysql
=> Access denied

config will finish but your password (iSib7ME01cdM5kTT) won't be accepted.

Looks like there's something wrong with reading/parsing of "/root/.my.cnf" because when I remove this file so that `emerge --config` will prompt me for a password, the password will be accepted as expected.

After setup you can re-create your ".my.cnf" file which will work, too.

[Comment 2 Gaetan 2015-10-01 18:32:59 UTC]

This issue is also present now with dev-db/mysql (tested with latest version (5.2.26 atm)) and wasn't causing troubles a few versions ago.

This is causing troubles for automated deployments.

[Comment 3 Eric Johnson 2015-10-27 20:48:34 UTC]

Perhaps worth changing the description. I just lost multiple hours to this issue.

In the eclass for Mysql, I found this:

MYSQL_ROOT_PASSWORD="$(mysql-v2_getoptval 'client mysql' password)"

Which effectively results in this:

/usr/bin/my_print_defaults 'client'

.. being piped into sed.

Running this manually, and looking at the output, I see

--password=*****

Looks like this is a new feature:

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-25.html (search for my_print_defaults)

For a possible solution, it looks like my_print_defaults needs the "--show" parameter.

[Comment 4 Thomas Deutschmann (RETIRED) 2016-09-26 18:55:10 UTC]

Fixed via

> commit 58d95ca48419704686b7ad7115b609a98d2adea9
> Author: Thomas Deutschmann
> Date:   Mon Sep 26 20:48:50 2016 +0200
> 
>     mysql-multilib-r1.eclass: Fix password reading from my.cnf
> 
>     We are reading multiple sections from my.cnf at once from my.cnf when
>     looking for the password for the mysql root user in
>     mysql-multilib-r1_pkg_config().
> 
>     If each section has set a password option this will result in the following
>     invalid password value (with "set -x"):
> 
>      ++ local extra_options=
>      ++ //usr/bin/my_print_defaults client mysql
>      ++ sed -n '/^--password=/s,--password=,,gp'
>      + MYSQL_ROOT_PASSWORD='*****
>      *****'
>      + [[ *****
>      ***** == \*\*\*\*\* ]]
>      + set +x
> 
>     Like you can see the two passwords are concatenated via newline in one
>     string which is not what we want.
> 
>     With this commit we will no longer read all sections at once instead we
>     read section per section. We are now also telling the user where we are
>     looking for the password and where we found one. In addition this commit
>     adds a sanity check for newline to catch scenarios where the user for
>     example has inadvertently set multiple password options in one section
>     which we can't handle: In that case it is better to prompt for a password
>     like no password was set in my.cnf instead of initializing mysqld with a
>     mysql root password the user is not expecting.
> 
>     Gentoo-Bug: https://bugs.gentoo.org/510724

But keep in mind: You have to re-emerge you dev-db/<server> after eclass was updated to get the fix. I.e. existing installations won't get that fix...