Hi

Firstly, thank you to Thanh for working on the bug.  Since your patch has been submitted, I've taken a moment to review the original code and patch code, and there's a few more complications lurking in this problem space.  I added the following comment (and a -2 hold) on the review:

>    I'm putting a tentative do-no-merge on this whilst we discuss a few issues.  The complicating factor is the switch from percona-cluster to mysql-innodb-cluster which introduced the MITM mysql-router charm.  This has changed the semantics of 'breaking the shared-db relation' which means the original fix in #1797229 to say the DB is not initialised now doesn't quite hold.  I'm going to take this discussion back to the bug to try to sort this out.

---

The current code in keystone looks like this for shared-db 'going away':

@hooks.hook('shared-db-relation-departed',
            'shared-db-relation-broken')
def db_departed_or_broken():
    if is_leader():
        leader_set({'db-initialised': None})


This code's purpose was to allow for removing the database cluster and then re-adding it so that keystone would re-create the database.  It's basically assumes that the only reason the relation is going away is because the database (cluster) is being destroyed, and the way the keystone charm is currently written is that when the charm is related to a database it will *always* be necessary to re-create the database.

Thus, the code was added (due to https://bugs.launchpad.net/charm-keystone/+bug/1797229) to essentially put the charm back into 'database not initialised' if the relation went away.  It didn't take into account scale up/down and doing that to the leader, and the leader being the one scaled down; this broke the charm as it's only a unit being removed.

---

So let's set up a scenario:  3 keystone units k/0*, k/1, k/2 and each with it's shared-db relation to a cluster with mysql-router.  The state is that the DB is initialised and everything is running.

There are two operational scenarios that need to be resolved:

a) Remove the old database and get it to re-initialise (bug #1797229)
b) Scale-up and then scale down (remove the leader) - database remains the same.

The leader setting 'db-initialised' is a cached flag indicating that the leader initialised the database, and when the leader loses its relation with the database, that cached value (rightly, I think) should also go away.

In both cases, the decision on whether to re-initialise the database or not rests with the leader.  If leadership changes without the shared-db relation changing then the cached value is still valid for the new leader.

---

So what the issue really is is the validity of the cached flag 'db-initialised' in leader settings.  I don't think that an additional flag (caching a cache) is the right approach.  What I propose is:

a) The current leader 'owns' the cached flag 'db-initialised'.
b) when the leader's shared-db relation is broken, it clears the db-initialised flag.
c) If a leader checks the 'db-initialised' flag and finds that it is not set then (this is new behaviour) checks the database to see whether it is actually initialised or not. (i.e. verifying whether a key set of tables exist and/or admin usernames, etc.)  If they do, then it updates the cached flag, otherwise it initialises the database.

This covers the following scenarios (I think!):

1. If the leader is removed, then a new leader is elected, it checks the DB, sees it's initialised and sets the flag.
2. If shared-db relation is removed and then re-added, the existing leader then checks for the DB, sees it's initialised and simply sets the flag.
3. If the shared-db relation is removed, the database destroyed, and then the charm related to a new database then the flag will not be set, the DB will not be initialised, and the leader will then initialise the database.
4. If the shared-db relation is removed, the keystone database dumped and imported into a new cluster, and the keytone shared-db is related to the new cluster, then the database will be already initialised (based on physical checks) and thus the flag will just be set.

---

In each case, the db-initialised flag is just a cache, and if not set, the keystone leader unit does extra work to determine if the database is really 'there' or needs to be initialised.

I think that this is probably in line with solving the use cases presented.  There may well be others that I've not fully understood.