Enable shared-db tests when keystone is functional again

[shayancanonical]

Bug Description

The shared-db relation tests deploy keystone and test that the keystone application is able to migrate its tables. However, due to a bug in keystone, the tests are failing. There is no prior version of the charm we can pin the test to to make it pass.

This issue is a tracker to enable the shared-db tests once the bug is resolved. To do so, we will have to uncomment the github workflows from the CI files.

To Reproduce

n/a

Environment

n/a

Relevant log output

n/a

Additional context

No response

[taurus-forever]

@shayancanonical the bug in keystone is "resolved", but the issue is still reproducible:

tox -e integration-shared-db
...
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed Traceback (most recent call last):
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/hooks/shared-db-relation-changed", line 937, in <module>
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     main()
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/hooks/shared-db-relation-changed", line 930, in main
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     hooks.execute(sys.argv)
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/core/hookenv.py", line 962, in execute
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     self._hooks[hook_name]()
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/contrib/openstack/utils.py", line 1862, in wrapped_f
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     return restart_on_change_helper(
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/core/host.py", line 837, in restart_on_change_helper
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     r = lambda_f()
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/contrib/openstack/utils.py", line 1863, in <lambda>
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     (lambda: f(*args, **kwargs)),
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/hooks/shared-db-relation-changed", line 410, in db_changed
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     leader_init_db_if_ready(use_current_context=True)
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/contrib/openstack/utils.py", line 1862, in wrapped_f
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     return restart_on_change_helper(
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/core/host.py", line 837, in restart_on_change_helper
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     r = lambda_f()
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/contrib/openstack/utils.py", line 1863, in <lambda>
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     (lambda: f(*args, **kwargs)),
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/hooks/shared-db-relation-changed", line 391, in leader_init_db_if_ready
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     migrate_database()
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/charmhelpers/core/decorators.py", line 40, in _retry_on_exception_inner_2
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     return f(*args, **kwargs)
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/var/lib/juju/agents/unit-keystone-1/charm/hooks/keystone_utils.py", line 828, in migrate_database
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     subprocess.check_output(cmd)
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/usr/lib/python3.8/subprocess.py", line 415, in check_output
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     return run(*popenargs, stdout=PIPE, timeout=timeout, check=True,
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed   File "/usr/lib/python3.8/subprocess.py", line 516, in run
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed     raise CalledProcessError(retcode, process.args,
unit-keystone-1: 22:14:57 WARNING unit.keystone/1.shared-db-relation-changed subprocess.CalledProcessError: Command '['sudo', '-u', 'keystone', 'keystone-manage', 'db_sync']' returned non-zero exit status 1.
unit-keystone-1: 22:14:57 ERROR juju.worker.uniter.operation hook "shared-db-relation-changed" (via explicit, bespoke hook script) failed: exit status 1

Should we report is as a new issue to keystone?

Also, the test might need some love too, as mysqlrouter units 1 and 0 behave differently:

Model                Controller  Cloud/Region         Version  SLA          Timestamp
test-shared-db-a5u8  lxd         localhost/localhost  2.9.37   unsupported  22:17:18+01:00

App          Version          Status   Scale  Charm         Channel        Rev  Exposed  Message
keystone     17.0.1           waiting      2  keystone      latest/stable  539  no       Allowed_units list provided but this unit not present
mysql        8.0.31-0ubun...  active       1  mysql         edge            87  no       Unit is ready: Mode: RW
mysqlrouter                   active       2  mysql-router                   0  no       

Unit              Workload  Agent  Machine  Public address  Ports     Message
keystone/0        blocked   idle   1        10.183.161.187  5000/tcp  Database not initialised
  mysqlrouter/1   active    idle            10.183.161.187            
keystone/1*       error     idle   2        10.183.161.21   5000/tcp  hook failed: "shared-db-relation-changed" for mysqlrouter:shared-db
  mysqlrouter/0*  active    idle            10.183.161.21             
mysql/0*          active    idle   0        10.183.161.114            Unit is ready: Mode: RW

Machine  State    Address         Inst id        Series  AZ  Message
0        started  10.183.161.114  juju-bb897b-0  jammy       Running
1        started  10.183.161.187  juju-bb897b-1  focal       Running
2        started  10.183.161.21   juju-bb897b-2  focal       Running

Relation provider         Requirer                      Interface           Type         Message
keystone:cluster          keystone:cluster              keystone-ha         peer         
mysql:database            mysqlrouter:backend-database  mysql_client        regular      
mysql:database-peers      mysql:database-peers          mysql-peers         peer         
mysql:restart             mysql:restart                 rolling_op          peer         
mysqlrouter:mysql-router  mysqlrouter:mysql-router      mysql-router-peers  peer         
mysqlrouter:shared-db     keystone:shared-db            mysql-shared        subordinate  

BTW. it is possible to relate mysql with keystone without any issues:

juju deploy keystone --channel edge --series focal
juju deploy ch:mysql --channel edge
juju relate mysql:shared-db keystone:shared-db

but not possible through mysql-router.
Also relation mysql<>keystone is broken IF MySQL is related with TLS operator first.
0_o

[shayancanonical]

1 and 2.
I was able to replicate the state you mentioned above. I was then able to download mysql-client on the keystone lxd container, and connect to the database (connecting via mysqlrouter locally). Thus, I think that it may be an issue with the keystone app running on focal (the command sudo -u keystone keystone-manage db_sync exits with code 1 and no logs).

When the mysql-router operator is related with keystone, the juju leader unit is the only unit bootstrapped. The remaining units are then bootstrapped after the leader unit's bootstrap is complete. The reason for this is the juju leader mysqlrouter unit requests a user with escalated privileges to bootstrap mysqlrouter (this escalated user is not shared with the application). Then it creates the application (keystone) user with limited privileges to provide to the application. This application user needs to be created only once (on the juju leader unit of mysqlrouter).

Below, keystone/0 is in error state because it was unable to run sudo -u keystone-manage db_sync. However keystone/1 is in blocked state because (my guess) keystone/0 was unable to migrate its databases.

Unit              Workload  Agent  Machine  Public address  Ports     Message
keystone/0*       error     idle   1        10.201.101.62   5000/tcp  hook failed: "shared-db-relation-changed" for mysqlrouter:shared-db
  mysqlrouter/0*  active    idle            10.201.101.62
keystone/1        blocked   idle   2        10.201.101.168  5000/tcp  Database not initialised
  mysqlrouter/1   active    idle            10.201.101.168
mysql/0*          active    idle   0        10.201.101.67             Unit is ready: Mode: RW

3. The relation mysql<> keystone is broken when mysql is related with the tls operator because the keystone cannot run migrations against the database. This (my guess) is due to the keystone app not knowing how to handle tls connections with the mysql database. The following is the database section of the keystone conf, and I do not see anything related to secure connections:

[database]
connection = mysql+pymysql://keystone:wePclsAahm5I58eTuQLdMOKY@juju-948a7f-1/keystone
connection_recycle_time = 200

Also, manually running sudo -u keystone keystone-manage db_sync within the keystone lxd container fails with exit code 1 without any logs. This I think is something we might be able to report as a bug against the keystone project.

4. I was also able to confirm that relating directly to the database without tls or router (both in focal and jammy) works correctly.

@taurus-forever please let me know your thoughts, and I can create the appropriate tickets when ready