Inconsistency with handling of unique constraints

Bug #1305950 reported by David Stanek on 2014-04-10
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Identity (keystone)
Low
Unassigned

Bug Description

We have many unit tests (simple example[1]) that check to see that indexes are case insensitive. The problem is that our current implementation doesn't explicitly make the index case insensitive. That's left up the the RDBMS. This is problematic because in my testing I have seen different results:
  - SQLite - is case sensitive
  - MySQL - is case insensitive[2]
  - PostgreSQL - is case sensitive

My goal was to get the unit tests working on MySQL/PostgreSQL, so I would like to figure out a direction. Should we drop those tests and leave it up to the environment? It's also possible to get SQLAlchemy create the proper index. I've never done it, but I'm sure it's possible.

[references]
1. http://git.openstack.org/cgit/openstack/keystone/tree/keystone/tests/test_backend.py#n1791
2. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

Tags: sql Edit Tag help

In my opinion, I think we should either try to handle the case insensitivity or very explicitly say that we don't. Users will most likely stumble upon these things and it's better to have some sort of answer. A solution could be to then specify that case sensitivity is supported for some specified platforms.

In MySQL the following approach could be used: http://stackoverflow.com/a/11067627

Dolph Mathews (dolph) wrote :

I think this would be a great topic for the operator session at the summit!

Changed in keystone:
status: New → Confirmed
importance: Undecided → Medium

Damn, we didn't talk about this at the summit :/ Any insights, either way?

Dolph Mathews (dolph) wrote :

Bah! The best approach now is probably to file a bp explaining what scenarios we expect conflicts to occur, those in which we don't, and discuss the viability of a migrating to more consistent enforcement.

Swati Jaiswal (curioswati) wrote :

Hello, I want to contribute to this bug. I am a newbie to openstack so please can you tell me where should I start?

Changed in keystone:
assignee: nobody → Swati Jaiswal (curioswati)
David Stanek (dstanek) wrote :

I think doing what Dolph suggested in comment #4 is probably a good first step. That will show how big (or little) of a problem that this actually is...

Changed in keystone:
assignee: Swati Jaiswal (curioswati) → nobody
tags: added: sql
David Stanek (dstanek) on 2016-03-07
Changed in keystone:
importance: Medium → Low
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers