Deleting a tenant where a user is a member leaves the user with a tenant link that's not existant.

[Endre Karlson]

I used the sample data script that comes with keystone and deleted the demo tenant, this leaves the "admin" user with a tenant link in the user_tenant_membership which doesn't exist:

mysql> select * from user_tenant_membership;
+----------------------------------+----------------------------------+
| user_id | tenant_id |
+----------------------------------+----------------------------------+
| 05b9b4c0dc994efd93fe71149dd15999 | 3e7c05f85ea54a41b059efd6e18bfcc1 |
| 6291f1b9416645d4a5ca863595b36ce1 | 2f644ea36b784d4391b169e6a8e9b06a |
| 6291f1b9416645d4a5ca863595b36ce1 | 421ec0e580244957bbff63d83ae44ebc |
| 7d6eb22ad11c4e90b79f34896fdc985f | e60ba0e9e2ad41739f3b7d1e9ea8963d |
| 8b08a6b5f14141e8b40efe632fd23c30 | c8528a175a4a4cd99a2b1973e5ded5f1 |
| a7ff5ecf5d1840109b74b476694f48f8 | bc27984dfc79414bba97a4ec9f64f799 |
| d0087b5c8723461481c859934c353939 | 50a5f2c692d44934949a107c8611457b |
| d0087b5c8723461481c859934c353939 | bbf57b976fda45b5bb404b008be2916e |
| dc2627ff9ca54074a4aab913d0b5ee1e | c8528a175a4a4cd99a2b1973e5ded5f1 |
| dd788f5f882a4209bf7bd474c79a0c3f | c8528a175a4a4cd99a2b1973e5ded5f1 |
| e409cec6ab4d4bfa88ef70b2caad18f9 | 56b3b82f8b9e488688bf2f28c5444a41 |
| f5c59a15173f442093a7b6bbf61a8b51 | c8528a175a4a4cd99a2b1973e5ded5f1 |
+----------------------------------+----------------------------------+
12 rows in set (0.00 sec)

6291f1b9416645d4a5ca863595b36ce1 is the admin user - the 421ec0e580244957bbff63d83ae44ebc tenant_id is the one that's deleted (demo)

mysql> select * from tenant where id = '2f644ea36b784d4391b169e6a8e9b06a' or id = '421ec0e580244957bbff63d83ae44ebc';
+----------------------------------+-------+----------------------------------------+
| id | name | extra |
+----------------------------------+-------+----------------------------------------+
| 2f644ea36b784d4391b169e6a8e9b06a | admin | {"enabled": true, "description": null} |
+----------------------------------+-------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from tenant where id = '421ec0e580244957bbff63d83ae44ebc';
Empty set (0.00 sec)

mysql> delete from user_tenant_membership where tenant_id = '421ec0e580244957bbff63d83ae44ebc';
Query OK, 1 row affected (0.00 sec)

This broken relationship makes the admin user unable to login to the dashboard at all. Though utils like "nova" seems to work fine.

[Joseph Heck (heckj)]

Endre -

What Identity backend were you using when you did this operation? I need a touch more data to make a reproducible issue for the keystone side of this.

[Endre Karlson (endre-karlson)]

MySQL Joseph.

[Gabriel Hurley (gabriel-hurley)]

Horizon can't solve data integrity problems in Keystone's DB and validating all the data that keystone gives us would be absurdly expensive. So while Horizon *could* handle this situation, the appropriate fix is 100% in Keystone.

[Chris Jansen (jansen-chris)]

This bug is related to https://bugs.launchpad.net/keystone/+bug/998137, the same logic should be applied.

I raised a duplicate to that bug here: https://bugs.launchpad.net/keystone/+bug/1001203 as under InnoDB the problem manifests itself as a fatal error! Please see my comments there pertaining to this bug, and another likely bug in the service catalog/endpoint relationship.

It appears that Keystone has not been tested under a RDBMS that enforces referential integrity. Seeing as InnoDB (which enforces referential integrity) is now the default storage engine for MySQL I find this quite alarming!

I am happy to raise a parent bug for these referential integrity issues and submit patches if necessary, however these bugs could be picked up during unit testing in the future if database server used were switched to MySQL with the InnoDB engine during the testing.

[Rafael Durán Castañeda (rafadurancastaneda)]

SQLite can check for foreign keys too if properly configured. We need just define:

def on_checkout(dbapi_con, connection_rec, connection_proxy):
    db_cursor = dbapi_con.execute('pragma foreign_keys=ON')

And then use it when the engine is created:

from sqlalchemy import events

if options['sql_connection'].startswith('sqlite'):
            events.event.listen(_ENGINE, 'checkout', on_checkout)

I think we should add something like this for SQLite so we can catch integrity errors in SQLite development enviro instead of MySQL (ot whatever RDBMS people is using).

[Morgan Fainberg (mdrnstm)]

This has been resolved as of https://review.openstack.org/#/c/71159/ (Icehouse) and possibly earlier.