R/O but leader node trying to create users/databases

[Alex Kavanagh]

Whilst doing some testing with keystone on Focal/Ussuri I recieved the error:

Error on observer while running replication hook 'before_commit'.

which caused a hook to fail in the mysql-innodb-cluster. (Unfortunately, I don't have the model anymore). It looks like it was the client trying to execute a GRANT on a R/O node, which resulted in the error, which might mean that the client wasn't cluster aware?

[David Ames (thedac)]

The issue is the mysql-innodb-cluster charm is using the mysql client to do grants for requesting services.

It should use mysqlsh for all executions.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.opendev.org/718569

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.opendev.org/718569
Committed: https://git.openstack.org/cgit/openstack/charm-mysql-innodb-cluster/commit/?id=2ee2031a5f22af9d7b32ab312966d6ac37e75516
Submitter: Zuul
Branch: master

commit 2ee2031a5f22af9d7b32ab312966d6ac37e75516
Author: David Ames <email address hidden>
Date: Thu Apr 9 14:34:43 2020 -0700

    Send writes to the cluster RW primary

    Before this change, DB setup for client applications would occur on
    whichever node was the juju leader. This may not be the cluster RW
    primary node. This led to Bug #1866164.

    This change guarantees that any post-clustering writes get sent to the
    cluster RW primary node.

    Change-Id: If6beb4450be755a1b204c63759de32d486201202
    Closes-Bug: #1866164

[David Ames (thedac)]

This has re-appeared. The node is starting with the correct primary IP but the charm helpers is re-connecting with the wrong address and password:

PRIMARY: 10.5.0.5
<charmhelpers.contrib.database.mysql.MySQL8Helper object at 0x7f8f1a364ee0>
Traceback (most recent call last):
  File "hooks/update-status", line 22, in <module>
    main()
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/__init__.py", line 74, in main
    bus.dispatch(restricted=restricted_mode)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 390, in dispatch
    _invoke(other_handlers)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 359, in _invoke
    handler.invoke()
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charms/reactive/bus.py", line 181, in invoke
    self._action(*args)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/charm/reactive/mysql_innodb_cluster_handlers.py", line 274, in db_router_respond
    if instance.create_databases_and_users(db_router):
  File "lib/charm/openstack/mysql_innodb_cluster.py", line 1062, in create_databases_and_users
    password = self.configure_db_for_hosts(
  File "lib/charm/openstack/mysql_innodb_cluster.py", line 1139, in configure_db_for_hosts
    password = rw_helper.configure_db(host, database, username)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 464, in configure_db
    self.create_database(database)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/charmhelpers/contrib/database/mysql.py", line 115, in create_database
    cursor.execute("CREATE DATABASE `{}` CHARACTER SET UTF8"
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/var/lib/juju/agents/unit-mysql-innodb-cluster-0/.venv/lib/python3.8/site-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1290, 'The MySQL server is running with the --super-read-only option so it cannot execute this statement')

charmhelpers:
    def configure_db(self, hostname, database, username, admin=False):
        """Configure access to database for username from hostname."""
        self.connect(password=self.get_mysql_root_password())

[David Ames (thedac)]

Liam's work here [0] and [1] here should resolve this last edge case.

[0] https://github.com/juju/charm-helpers/pull/478
[1] https://review.opendev.org/#/c/735206/