DBNonExistentTable: (sqlite3.OperationalError) no such table: credential

[Marian Gasparovic]

During ussuri-focal deployment keystone units were green in juju but keystone log contains

2020-11-16 20:29:53.355 22871 ERROR keystone oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: credential
2020-11-16 20:29:53.355 22871 ERROR keystone [SQL: SELECT credential.encrypted_blob AS credential_encrypted_blob, credential.id AS credential_id, credential.user_id AS credential_user_id, credential.project_id AS credential_project_id, credential.type AS credential_type, credential.key_hash AS credential_key_hash, credential.extra AS credential_extra

which cause 500 response in nova-cloud-controller

logs and other files:
https://solutions.qa.canonical.com/testruns/testRun/aac4ba06-8e5b-49b5-8a99-fbba6fafa2b4

[Ryan Mickler (ryanmickler)]

I'm getting the same error. Can't log in to my dashboard because keystone wont work.

[Ryan Mickler (ryanmickler)]

This likely is related to https://bugs.launchpad.net/charm-keystone/+bug/1894866

as the line
2020-09-25 22:06:58.834 22172 ERROR keystone sqlite3.OperationalError: no such table: credential

appears in those logs

[Marco Cecconi (marco-cecconi-o)]

Missing credential table reported in the keystone manage log. Authentication service not available to the other services.

2021-05-19 16:26:42.938 22647 CRITICAL keystone [-] Unhandled error: oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: credential
[SQL: SELECT credential.encrypted_blob AS credential_encrypted_blob, credential.id AS credential_id, credential.user_id AS credential_user_id, credential.project_id AS credential_project_id, credential.type AS credential_type, credential.key_hash AS credential_key_hash, credential.extra AS credential_extra
FROM credential]
(Background on this error at: http://sqlalche.me/e/e3q8)
2021-05-19 16:26:42.938 22647 ERROR keystone Traceback (most recent call last):
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1245, in _execute_context
2021-05-19 16:26:42.938 22647 ERROR keystone self.dialect.do_execute(
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 581, in do_execute
2021-05-19 16:26:42.938 22647 ERROR keystone cursor.execute(statement, parameters)
2021-05-19 16:26:42.938 22647 ERROR keystone sqlite3.OperationalError: no such table: credential
2021-05-19 16:26:42.938 22647 ERROR keystone
2021-05-19 16:26:42.938 22647 ERROR keystone The above exception was the direct cause of the following exception:
2021-05-19 16:26:42.938 22647 ERROR keystone
2021-05-19 16:26:42.938 22647 ERROR keystone Traceback (most recent call last):
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/bin/keystone-manage", line 10, in <module>
2021-05-19 16:26:42.938 22647 ERROR keystone sys.exit(main())
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/manage.py", line 41, in main
2021-05-19 16:26:42.938 22647 ERROR keystone cli.main(argv=sys.argv, developer_config_file=developer_config)
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/cli.py", line 1379, in main
2021-05-19 16:26:42.938 22647 ERROR keystone CONF.command.cmd_class.main()
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/cli.py", line 780, in main
2021-05-19 16:26:42.938 22647 ERROR keystone klass.migrate_credentials()
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/cli.py", line 752, in migrate_credentials
2021-05-19 16:26:42.938 22647 ERROR keystone credentials = self.credential_api.driver.list_credentials(
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/common/driver_hints.py", line 42, in wrapper
2021-05-19 16:26:42.938 22647 ERROR keystone return f(self, hints, *args, **kwargs)
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/credential/backends/sql.py", line 69, in list_credentials
2021-05-19 16:26:42.938 22647 ERROR keystone return [s.to_dict() for s in credentials]
2021-05-19 16:26:42.938 22647 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/orm/qu...

[Alex Kavanagh (ajkavanagh)]

The sqlite3 issue is likely to not be the issue; it's most likely due to the charm not yet being related to the database (as sqlite3 is the default from the packages, the service gets started by the package install, and the charm hasn't written the config file for keystone yet .. it's harmless noise in the log.).

[Alex Kavanagh (ajkavanagh)]

I'm marking this as incomplete due to the sqlite3 error, which probably indicates the charm is not yet related to the database at this point. However, if this happened AFTER the charm was related to mysql then it might be a problem.

[Marco Cecconi (marco-cecconi-o)]

The relations should be there, will check for network issues

keystone-mysql-router:shared-db keystone:shared-db mysql-shared subordinate

[Alex Kavanagh (ajkavanagh)]

> The relations should be there, will check for network issues

> keystone-mysql-router:shared-db keystone:shared-db mysql-shared subordinate

Sorry, I didn't make myself clear: the error message in the log indicates that that log entry was created before the charm had connected to the mysql charm, exchanged details, and written the configuration file for keystone. Later in the log file, there should be indicates of when the share-db relation changed and the configuration files were written.

[Marco Cecconi (marco-cecconi-o)]

Thanks for the clarification Alex! The problem in this case was actually related to a missing parameter for the mysql router and that was the reason why the installation was failing. There is a db-router parameter that has to be defined in order to assign the mysql router and the inno db cluster to a specific network. This is already documented in https://bugs.launchpad.net/charm-mysql-router/+bug/1881171

[...]
services:
  keystone-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space
  nova-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space
  glance-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space
  neutron-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space
  dashboard-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space
  heat-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space
  placement-mysql-router:
    charm: cs:~openstack-charmers-next/mysql-router
    bindings:
     db-router: *internal-space

[...]

 mysql-innodb-cluster:
    charm: cs:~openstack-charmers-next/mysql-innodb-cluster
    num_units: 3
    bindings:
      "": *oam-space
      cluster: *internal-space
      # added shared-db to db-router
      db-router: *internal-space
      shared-db: *internal-space
    options:
      source: *openstack-origin
      innodb-buffer-pool-size: 512M
      wait-timeout: 3600
      enable-binlogs: True
      max-connections: *mysql-connections
    to:
    - lxd:1
    - lxd:2
    - lxd:3

[Alex Kavanagh (ajkavanagh)]

Hi Marco. Excellent; I'm pleased that you've sorted it out. Is the bug resolved for you then? If so, please feel free to mark it invalid. If not, please let me know if there are further details. Many thanks!

[Moises Emilio Benzan Mora (moisesbenzan)]

Triggered this bug once more on a Bionic deployment.

On keystone[1], we see `No such table: credential` error. The other two units (keystone[0] and keystone[2]) boot up correctly and start serving requests for a while:

```
2021-10-02 05:22:54.767 23885 ERROR keystone Traceback (most recent call last):
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context
2021-10-02 05:22:54.767 23885 ERROR keystone cursor, statement, parameters, context
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 581, in do_execute
2021-10-02 05:22:54.767 23885 ERROR keystone cursor.execute(statement, parameters)
2021-10-02 05:22:54.767 23885 ERROR keystone sqlite3.OperationalError: no such table: credential
2021-10-02 05:22:54.767 23885 ERROR keystone
2021-10-02 05:22:54.767 23885 ERROR keystone The above exception was the direct cause of the following exception:
2021-10-02 05:22:54.767 23885 ERROR keystone
2021-10-02 05:22:54.767 23885 ERROR keystone Traceback (most recent call last):
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/bin/keystone-manage", line 10, in <module>
2021-10-02 05:22:54.767 23885 ERROR keystone sys.exit(main())
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/manage.py", line 41, in main
2021-10-02 05:22:54.767 23885 ERROR keystone cli.main(argv=sys.argv, developer_config_file=developer_config)
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/cli.py", line 1379, in main
2021-10-02 05:22:54.767 23885 ERROR keystone CONF.command.cmd_class.main()
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/cli.py", line 780, in main
2021-10-02 05:22:54.767 23885 ERROR keystone klass.migrate_credentials()
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/cmd/cli.py", line 753, in migrate_credentials
2021-10-02 05:22:54.767 23885 ERROR keystone driver_hints.Hints()
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/common/driver_hints.py", line 42, in wrapper
2021-10-02 05:22:54.767 23885 ERROR keystone return f(self, hints, *args, **kwargs)
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/keystone/credential/backends/sql.py", line 69, in list_credentials
2021-10-02 05:22:54.767 23885 ERROR keystone return [s.to_dict() for s in credentials]
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/orm/query.py", line 3367, in __iter__
2021-10-02 05:22:54.767 23885 ERROR keystone return self._execute_and_instances(context)
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/sqlalchemy/orm/query.py", line 3392, in _execute_and_instances
2021-10-02 05:22:54.767 23885 ERROR keystone result = conn.execute(querycontext.statement, self._params)
2021-10-02 05:22:54.767 23885 ERROR keystone File "/usr/lib/python3/dist-packages/s...

[Alexander Balderson (asbalderson)]

Also bumped into this on aodh during a Focal Ussuri deployment:

oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: alarm

I'll attach the crashdump but the errors are comming from aodh 2, while the other 2 aodh units came up.

[Alex Kavanagh (ajkavanagh)]

Just as a note: any log messages that indicate sqlite3 are pre-configuration of keystone. e.g. before the charms have written the config files and restarted the service. This is because the default for the packages is to start keystone, and keystone's defaults are to use a local sqlite3 database. Thus you can 'ignore' the error messages as happening before the mysql relations are made. Obviously, if there are no log messages indicating that the mysql relation was ever made, then that would be a bug.

This is true for all the services that use the mysql database; e.g. aodh, neutron-api, nova-cloud-controller, etc.

[Bas de Bruijne (basdbruijne)]

I am seeing this in this testrun too:
https://oil-jenkins.canonical.com/job/fce_build/515//console

https://solutions.qa.canonical.com/testruns/testRun/90a10128-8bbf-4cf4-b9ae-210bced4de7a

Keystone leader is giving the error message:
oslo_db.exception.DBNonExistentTable: (sqlite3.OperationalError) no such table: credential

and stays blocked with:

keystone/0* (...) blocked (...) Allowed_units list provided but this unit not present
keystone/1 (...) blocked (...) Database not initialised
keystone/2 (...) blocked (...) Database not initialised

My gut feeling is that this happens due to unfortunate timing of the leader changing.

Crashdump: https://oil-jenkins.canonical.com/artifacts/90a10128-8bbf-4cf4-b9ae-210bced4de7a/generated/generated/openstack/juju-crashdump-openstack-2022-04-01-06.42.07.tar.gz

[Alex Kavanagh (ajkavanagh)]

> My gut feeling is that this happens due to unfortunate timing of the leader changing.

Bas, is this on a very resource-constrained set of hardware? I'm wondering why leadership would change during a deploy.