incorrect SQL syntax to update user password on Xenial (mysql version 5.6.37-82.2)

[Linda Guo]

2021-02-24 01:17:39 [INFO] Deploying bundle '/tmp/charm-nrpe/tests/functional/tests/bundles/xenial.yaml' on to 'zaza-2428033b2384' model
2021-02-24 01:17:39 [INFO] Rendered template '<Template 'local-charm-overlay.yaml.j2'>' to file '/tmp/tmpqdt1vrd6/local-charm-overlay.yaml'
2021-02-24 01:17:40 [INFO] Deploying overlay '/tmp/tmpqdt1vrd6/local-charm-overlay.yaml' on to 'zaza-2428033b2384' model
2021-02-24 01:17:40 [INFO] Resolving charm: cs:percona-cluster
2021-02-24 01:17:40 [INFO] Resolving charm: cs:nagios
2021-02-24 01:17:40 [INFO] Executing changes:
2021-02-24 01:17:40 [INFO] - upload charm cs:percona-cluster-295 for series xenial
2021-02-24 01:17:40 [INFO] - deploy application mysql on xenial using cs:percona-cluster-295
2021-02-24 01:17:41 [INFO] - upload charm cs:nagios-42 for series xenial
2021-02-24 01:17:41 [INFO] - deploy application nagios on xenial using cs:nagios-42
2021-02-24 01:17:41 [INFO] - upload charm /tmp/charm-nrpe/.build/nrpe for series xenial
2021-02-24 01:17:42 [INFO] - deploy application nrpe on xenial using /tmp/charm-nrpe/.build/nrpe
2021-02-24 01:17:42 [INFO] - add relation mysql:nrpe-external-master - nrpe:nrpe-external-master
2021-02-24 01:17:42 [INFO] - add relation nrpe:monitors - nagios:monitors
2021-02-24 01:17:42 [INFO] - add unit mysql/0 to new machine 0
2021-02-24 01:17:42 [INFO] - add unit nagios/0 to new machine 1
2021-02-24 01:17:42 [INFO] Deploy of bundle completed.
2021-02-24 01:17:42 [INFO] Waiting for environment to settle
2021-02-24 01:17:42 [INFO] Timeout for deployment to settle set to: 3600
2021-02-24 01:17:42 [INFO] Waiting for a unit to appear
2021-02-24 01:17:42 [INFO] Waiting for all units to be idle
2021-02-24 01:20:19 [ERROR] {'default_alias': 'zaza-2428033b2384'}
2021-02-24 01:20:19 [ERROR] Model default_alias (zaza-2428033b2384)
2021-02-24 01:20:19 [ERROR] Applications in error state: mysql
2021-02-24 01:20:19 [ERROR] Units in error state: mysql/0
2021-02-24 01:20:19 [ERROR] Juju log for mysql/0
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/percona_utils.py", line 1832, in create_nagios_user
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined "'{passwd}';".format(passwd=nagios_password()))
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/charmhelpers/contrib/database/mysql.py", line 193, in execute
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined cursor.execute(sql)
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 226, in execute
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined self.errorhandler(self, exc, value)
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined raise errorvalue
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 217, in execute
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined res = self._query(query)
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 378, in _query
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined rowcount = self._do_query(q)
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 341, in _do_query
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined db.query(q)
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 280, in query
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined _mysql.connection.query(self, query)
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 WARNING nrpe-external-master-relation-joined _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'' at line 1")
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 ERROR juju.worker.uniter.operation runhook.go:136 hook "nrpe-external-master-relation-joined" (via explicit, bespoke hook script) failed: exit status 1
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:16 INFO juju.worker.uniter resolver.go:143 awaiting error resolution for "relation-joined" hook
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log: 2021-02-24 01:20:18 INFO juju.worker.uniter resolver.go:143 awaiting error resolution for "relation-joined" hook
2021-02-24 01:20:20 [ERROR] unit-mysql-0.log:
Traceback (most recent call last):
  File "/tmp/charm-nrpe/.tox/func/bin/functest-run-suite", line 8, in <module>
    sys.exit(main())
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/charm_lifecycle/func_test_runner.py", line 279, in main
    test_directory=args.test_directory)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/charm_lifecycle/func_test_runner.py", line 214, in func_test_runner
    force=force, test_directory=test_directory)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/charm_lifecycle/func_test_runner.py", line 129, in run_env_deployment
    test_directory=test_directory)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/charm_lifecycle/deploy.py", line 388, in deploy
    timeout=timeout)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/__init__.py", line 48, in _wrapper
    return run(_run_it())
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/__init__.py", line 36, in run
    return task.result()
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/__init__.py", line 47, in _run_it
    return await f(*args, **kwargs)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/model.py", line 1115, in async_wait_for_application_states
    timeout=timeout)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/juju/model.py", line 730, in block_until
    loop=self.loop)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/juju/utils.py", line 87, in block_until
    await asyncio.wait_for(_block(), timeout, loop=loop)
  File "/usr/lib/python3.6/asyncio/tasks.py", line 358, in wait_for
    return fut.result()
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/juju/utils.py", line 85, in _block
    while not all(c() for c in conditions):
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/juju/utils.py", line 85, in <genexpr>
    while not all(c() for c in conditions):
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/juju/model.py", line 725, in done
    return _disconnected() or all(c() for c in conditions)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/juju/model.py", line 725, in <genexpr>
    return _disconnected() or all(c() for c in conditions)
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/model.py", line 1114, in <lambda>
    model) or model.all_units_idle(),
  File "/tmp/charm-nrpe/.tox/func/lib/python3.6/site-packages/zaza/model.py", line 980, in check_model_for_hard_errors
    raise UnitError(errored_units)
zaza.model.UnitError: Units mysql/0 in error state
ERROR: InvocationError for command /tmp/charm-nrpe/.tox/func/bin/functest-run-suite (exited with code 1)
_______________________________________________________ summary _______________________________________________________
ERROR: func: commands failed
Makefile:67: recipe for target 'functional' failed

[Linda Guo (lihuiguo)]

mysql> ALTER USER 'nagios'@'localhost' IDENTIFIED BY 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'' at line 1

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+-------------------------------------------------------------------------+
| innodb_version | 5.6.37-82.2 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1.1,TLSv1.2 |
| version | 5.6.37-82.2-82.2 |
| version_comment | Percona XtraDB Cluster (GPL), Release 5.6.37-26.21.c70c68b, wsrep_26.21 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
+-------------------------+-------------------------------------------------------------------------+

[Linda Guo (lihuiguo)]

In charm/hooks/percona_utils.py", line 1832 should be updated to use the SQL right syntax on xenial

[Linda Guo (lihuiguo)]

Xenial running mysql version 5.6.37-82.2. The syntax is as follows for mysql database server version 5.7.5 or older:

SET PASSWORD FOR 'nagios'@'localhost' = PASSWORD('new-password');

OR

UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE USER='nagios' AND Host='localhost';

https://www.cyberciti.biz/faq/mysql-change-user-password/

[Alex Kavanagh (ajkavanagh)]

Thanks for raising the bug:

Triage:

The charm needs to support both forms of password setting:

On xenial or earlier:

SET PASSWORD FOR 'nagios'@'localhost' = PASSWORD('new-password');

On bionic and later:

ALTER USER 'nagios'@'localhost' IDENTIFIED BY 'new-password';

[Alex Kavanagh (ajkavanagh)]

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

[Xav Paice (xavpaice)]

is this fix available in cs:~openstack-charmers-next/percona-cluster-393? I'm getting slightly different errors now:

2021-04-20 10:05:16 [INFO] Executing changes:
2021-04-20 10:05:16 [INFO] - upload charm cs:~openstack-charmers-next/percona-cluster-393 for series xenial
2021-04-20 10:05:20 [INFO] - deploy application mysql on xenial using cs:~openstack-charmers-next/percona-cluster-393
2021-04-20 10:05:21 [INFO] - upload charm cs:nagios-43 for series xenial
2021-04-20 10:05:24 [INFO] - deploy application nagios on xenial using cs:nagios-43
2021-04-20 10:05:25 [INFO] - upload charm /home/xav/charms/charm-nrpe/.build/nrpe for series xenial
2021-04-20 10:05:39 [INFO] - deploy application nrpe on xenial using /home/xav/charms/charm-nrpe/.build/nrpe
2021-04-20 10:05:40 [INFO] - add relation mysql:nrpe-external-master - nrpe:nrpe-external-master
2021-04-20 10:05:40 [INFO] - add relation nrpe:monitors - nagios:monitors
2021-04-20 10:05:41 [INFO] - add unit mysql/0 to new machine 0
2021-04-20 10:05:41 [INFO] - add unit nagios/0 to new machine 1
2021-04-20 10:05:42 [INFO] Deploy of bundle completed.
2021-04-20 10:05:42 [INFO] Waiting for environment to settle
2021-04-20 10:05:42 [INFO] Timeout for deployment to settle set to: 3600
2021-04-20 10:05:45 [INFO] Waiting for a unit to appear
2021-04-20 10:05:45 [INFO] Waiting for all units to be idle
2021-04-20 10:09:39 [ERROR] {'default_alias': 'zaza-4f1033787dfe'}
2021-04-20 10:09:39 [ERROR] Model default_alias (zaza-4f1033787dfe)
2021-04-20 10:09:44 [ERROR] Applications in error state: mysql
2021-04-20 10:09:44 [ERROR] Units in error state: mysql/0
2021-04-20 10:09:44 [ERROR] Juju log for mysql/0
2021-04-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 WARNING nrpe-external-master-relation-joined
2021-04-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 WARNING nrpe-external-master-relation-joined Traceback (most recent call last):
2021-04-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 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-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 WARNING nrpe-external-master-relation-joined main()
2021-04-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 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-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 WARNING nrpe-external-master-relation-joined hooks.execute(sys.argv)
2021-04-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 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-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 WARNING nrpe-external-master-relation-joined self._hooks[hook_name]()
2021-04-20 10:09:52 [ERROR] unit-mysql-0.log: 2021-04-19 22:09:38 WARNING nrpe-external-master-relation-joined File "/var/lib/juju/agents/unit-mysql-0/charm/hooks/nrpe-external-master-relation-joined", line 1098, ...