[bionic-ussuri] next charm fails to connect to local host as nagios

[Alexander Balderson]

During openstack-charmers-next, charm errors during the nrpe-external-master-relation-joined hook, failing to connect to local host as nagios.

2021-04-18 03:07:36 ERROR juju-log nrpe-external-master:282: Failed to connect to database due to '(1045, "Access denied for user 'nagios'@'localhost' (using password: YES)")'
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined Traceback (most recent call last):
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/charmhelpers/contrib/database/mysql.py", line 358, in set_mysql_password
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined self.connect(user=username, password=current_password)
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/charmhelpers/contrib/database/mysql.py", line 111, in connect
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined self.connection = MySQLdb.connect(**_connection_info)
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/__init__.py", line 86, in Connect
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined return Connection(*args, **kwargs)
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 204, in __init__
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined super(Connection, self).__init__(*args, **kwargs2)
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined _mysql_exceptions.OperationalError: (1045, "Access denied for user 'nagios'@'localhost' (using password: YES)")
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined During handling of the above exception, another exception occurred:
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined Traceback (most recent call last):
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/nrpe-external-master-relation-joined", line 1235, in <module>
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined main()
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/nrpe-external-master-relation-joined", line 1225, in main
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined hooks.execute(sys.argv)
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/charmhelpers/core/hookenv.py", line 956, in execute
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined self._hooks[hook_name]()
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/nrpe-external-master-relation-joined", line 1098, in update_nrpe_config
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined set_nagios_user()
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/percona_utils.py", line 1837, in set_nagios_user
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined create_nagios_user()
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/percona_utils.py", line 1831, in create_nagios_user
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined m_helper.set_mysql_password('nagios', nagios_password())
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/charmhelpers/contrib/database/mysql.py", line 362, in set_mysql_password
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined 'leader settings (%s)') % ex, ex)
2021-04-18 03:07:36 WARNING nrpe-external-master-relation-joined charmhelpers.contrib.database.mysql.MySQLSetPasswordError: ('Cannot connect using password in leader settings ((1045, "Access denied for user \'nagios\'@\'localhost\' (using password: YES)"))', OperationalError(1045, "Access denied for user 'nagios'@'localhost' (using password: YES)"))

It looks like its only failing on unit 0, which happens to be the leader as well.
The test doesnt have retry-errors running when integrating lma with openstack, we'll update the count to try 5 times.

testrun artifcats at:
https://oil-jenkins.canonical.com/artifacts/f341cd8b-21bd-4cdc-8a06-3ee705f70cc6/index.html

crashdump at:
https://oil-jenkins.canonical.com/artifacts/f341cd8b-21bd-4cdc-8a06-3ee705f70cc6/generated/generated/openstack/juju-crashdump-openstack-2021-04-18-03.07.20.tar.gz

[Alex Kavanagh (ajkavanagh)]

Yes, it's definitely broken. I'm pretty sure that this change breaks it (ironically, I added it): https://github.com/openstack/charm-percona-cluster/commit/d55dcdebc9049c509e2f5ab40c8d497dc0a3236d

This issue is that the codepath now creates a new password in leader-settings, and then tries to connect to the database using that password that it hasn't yet set for that user.

i.e. the call to nagios_password() here in create_nagios_user(): (line 1830 in hooks/percona_utils.py):

    # NOTE (rgildein): Update the user's password if it has changed.
    m_helper.set_mysql_password('nagios', nagios_password())

where nagios_password() is (line 1057):

nagios_password = partial(_get_password, 'nagios-password')

and _get_password (line 1033):

def _get_password(key):
    _password = leader_get(key)
    if not _password and is_leader():
        _password = config(key) or pwgen()
        leader_set({key: _password})
    return _password

means that set_mysql_password() (charmhelpers/contrib/database/mysql.py at 328) has this bit of code:

        if not current_password:
            current_password = self.get_mysql_password(rel_username)

        # password that needs to be set
        new_passwd = password

        # update password for all users (e.g. root@localhost, root@::1, etc)
        try:
            self.connect(user=username, password=current_password)
            cursor = self.connection.cursor()
        except MySQLdb.OperationalError as ex:
            raise MySQLSetPasswordError(('Cannot connect using password in '
                                         'leader settings (%s)') % ex, ex)

where get_mysql_password() looks up the password from leader_settings! And thus it fails, as there is no password set against the user.

I'll unpick what is happening, and workout where to set the actual password.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/c/openstack/charm-percona-cluster/+/786947

[Alex Kavanagh (ajkavanagh)]

Review https://review.opendev.org/c/openstack/charm-percona-cluster/+/786947 addresses the issue.

[Alex Kavanagh (ajkavanagh)]

It's a bit of a mess. The fix I proposed doesn't work because there are TWO nagios passwords by the time the -changed hook fires:

root@juju-fa753d-zaza-5208722fd4f4-2:/var/lib/juju/agents/unit-percona-cluster-0/charm# leader-get
bootstrap-uuid: b12020ab-a1a2-11eb-a2fe-db57b19813e2
leader-ip: 172.20.1.7
mysql-nagios.passwd: kccSWtSZcy2RSB3LTFjhc55qFLGGjpbq
mysql.passwd: bggL3TTxtfPwfTZKCX2c7MLPbY2T8XrJBwXMb
nagios-password: qzMG3qJBsfRGc3F2bPNHxK3xBVf4x7cLZtWgb2bm6L
root-password: bggL3TTxtfPwfTZKCX2c7MLPbY2T8XrJBwXMb
sst-password: bqGYpSZMtnqnz4xWGJNwjh75CX66TgBCs6bwp

i.e. mysql-nagios.passwd and nagios-password. The database is actually set up with nagios-password, so I need to find out where mysql-nagios.passwd is coming from and why it is being selected.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/c/openstack/charm-percona-cluster/+/786947
Committed: https://opendev.org/openstack/charm-percona-cluster/commit/432c3f0be45780bb2938ea39fab0c8a6ef9057ba
Submitter: "Zuul (22348)"
Branch: master

commit 432c3f0be45780bb2938ea39fab0c8a6ef9057ba
Author: Alex Kavanagh <email address hidden>
Date: Mon Apr 19 20:27:55 2021 +0100

    Ensure that nagios user gets created with a password

    The associated bug is due to a change introduced in commit d55dcde
    which was to ensure that the correct password update is used for
    different versions of mysql (pre and post 5.7.5). However, this change
    has broken the nagios user creation due to not setting the password.

    This patch creates the nagios user and passord at the same time. The
    updating of the password is only done if the account already exists.

    The change also corrects the nagios password store in leader-settings to
    the 'mysql-nagios.passwd' key instead of 'nagios-password'. This was an
    unfortuante error when the nagios change password was introduced. The
    charm detects if the 'nagios-password' key is used on charm-upgrade and
    moves it to 'mysql-nagios.passwd'. This enables the key to work with
    the standard MySQLHelper functions.

    Finally, the ALTER command (on percona) doesn't update non-InnoDB tables
    and thus needs to be run for each unit when the nagios password is
    changed via the action. The changes in percona_utils.py enable this to
    happen.

    Whilst the change looks large it ONLY affects the nagios password parts
    of the charm.

    The related bug is a tracking bug to serve as a reminder to fix this in
    charm-helpers and this charm (i.e. make the charm-helpers code work to
    change a password for any user other than root, and then enable this
    charm to use that code).

    Change-Id: Ibc751bef7b4654ebffdf843c556b193373e6e80c
    Related-Bug: #1925377
    Closes-Bug: #1925042