strict constraint for database table creation

Bug #987121 reported by Vincent Hou
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
OpenStack Identity (keystone)
Fix Released
Medium
Dolph Mathews

Bug Description

OpenStack claims that any type of database supporting SQLAlchemy can be taken as the database for OpenStack use.
In some databases, if a column is defined as UNIQUE, it must be specified NOT NULL at the same time, e.g. IBM DB2, which is SQLAlchemy supporting. I am doing some tests with DB2 now.
For the tables TENANT, USER and ROLE, they all have the column NAME, but they don't define this column NOT NULL. For database like MYSQL, it is permitted and keystone-manage db_sync works well. However, for database with strict constrains, like IBM DB2, this is not allowed. Running keystone-manage db_sync will prompt the error, which tells me UNIQUE and NOT NULL must be specified for the column NAME.
Suggestion:
In the code keystone/identity/backends/sql.py, we have
name = sql.Column(sql.String(64), unique=True)

If we change it into
name = sql.Column(sql.String(64), unique=True, nullable=False),
this issue will be solved.

Revision history for this message
Dolph Mathews (dolph) wrote :

For the User, Tenant, and Role names, it definitely makes sense to me that they should be treated as unique and non-nullable.

I don't believe the catalog or token drivers are affected.

Changed in keystone:
assignee: nobody → Dolph Mathews (dolph)
importance: Undecided → Medium
milestone: none → folsom-1
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to keystone (master)

Fix proposed to branch: master
Review: https://review.openstack.org/6730

Changed in keystone:
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to keystone (master)

Reviewed: https://review.openstack.org/6730
Committed: http://github.com/openstack/keystone/commit/3dd36e3e87b8dfe3e9468dbe0a413a34ce6f63df
Submitter: Jenkins
Branch: master

commit 3dd36e3e87b8dfe3e9468dbe0a413a34ce6f63df
Author: Dolph Mathews <email address hidden>
Date: Wed Apr 25 13:49:28 2012 -0500

    Non-nullable User, Tenant, Role names (bug 987121)

    Change-Id: I100732e52fe0478280d647fc25ca82f9c14dc64c

Changed in keystone:
status: In Progress → Fix Committed
Revision history for this message
Rohit Karajgi (rohitk) wrote :

Can this be backported to stable/essex?

Thierry Carrez (ttx)
Changed in keystone:
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in keystone:
milestone: folsom-1 → 2012.2
Revision history for this message
Kevin-Yang (benbenzhuforever) wrote :
Download full text (3.2 KiB)

I am using DB2 for Keystone 2012.2 Folsom version. I pre-created a DB named 'KEYSTONE' with command "db2 create db" on the DB2 server. In the keystone configuration file, i config it as ->
[sql]
connection = ibmdb://db2inst1:aq1sw2de@146.89.7.95:60000/KEYSTONE.

However, when i call the command "keystone-manage db_sync", i came across such issue.

########################
[root@usaxnso044ccpra keystone-2012.2]# keystone-manage db_sync
Traceback (most recent call last):
  File "/usr/local/bin/keystone-manage", line 5, in <module>
    pkg_resources.run_script('keystone==2012.2', 'keystone-manage')
  File "build/bdist.linux-x86_64/egg/pkg_resources.py", line 499, in run_script
  File "build/bdist.linux-x86_64/egg/pkg_resources.py", line 1239, in run_script
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/EGG-INFO/scripts/keystone-manage", line 28, in <module>
    cli.main(argv=sys.argv, config_files=config_files)
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 164, in main
    return run(cmd, (args[:1] + args[2:]))
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 147, in run
    return CMDS[cmd](argv=args).run()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 35, in run
    return self.main()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 56, in main
    driver.db_sync()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/identity/backends/sql.py", line 136, in db_sync
    migration.db_sync()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/common/sql/migration.py", line 52, in db_sync
    return versioning_api.upgrade(CONF.sql.connection, repo_path, version)
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 186, in upgrade
    return _migrate(url, repository, version, upgrade=True, err=err, **opts)
  File "<string>", line 2, in _migrate
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.
    return f(*a, **kw)
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/api.py", line
    schema.runchange(ver, change, changeset.step)
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/schema.py", li
    change.run(self.engine, step)
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/script/py.py",
    script_func(engine)
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keyston
    old_id_col.alter(name='id_hash')
  File "/usr/local/lib/python2.7/site-packages/migrate/changeset/schema.py", lin
    return alter_column(self, *p, **k)
  File "/usr/local/lib/python2.7/site-packages/migrate/changeset/schema.py", lin
    visitorcallable = get_engine_visitor(engine, 'schemachanger')
  File "/usr/local/lib/python2.7/site-packages/migrate/changeset/databases/visit
    return get_dialect_visitor(engine.dialect, name)
  File "/usr/local/lib/python2.7/site-packages/migrate/changeset/databases/visit
    migrate_dialect_cls = DIALECTS[sa_dialect_name]

KeyError: 'ibmdb'
#...

Read more...

Revision history for this message
Dolph Mathews (dolph) wrote : Re: [Bug 987121] Re: strict constraint for database table creation
Download full text (5.0 KiB)

SqlAlchemy doesn't support DB2 out of the box, so I assume you're using
IBM's proprietary driver. I believe the protocol specified by IBM's driver
for SqlAlchemy is "ibm_db_sa" instead of "ibmdb".

For supported databases, see
http://docs.sqlalchemy.org/en/rel_0_7/core/engines.html

On Tuesday, December 18, 2012, Kevin-Yang wrote:

> I am using DB2 for Keystone 2012.2 Folsom version. I pre-created a DB
> named 'KEYSTONE' with command "db2 create db" on the DB2 server. In the
> keystone configuration file, i config it as ->
> [sql]
> connection = ibmdb://db2inst1:aq1sw2de@146.89.7.95:60000/KEYSTONE.
>
> However, when i call the command "keystone-manage db_sync", i came
> across such issue.
>
> ########################
> [root@usaxnso044ccpra keystone-2012.2]# keystone-manage db_sync
> Traceback (most recent call last):
> File "/usr/local/bin/keystone-manage", line 5, in <module>
> pkg_resources.run_script('keystone==2012.2', 'keystone-manage')
> File "build/bdist.linux-x86_64/egg/pkg_resources.py", line 499, in
> run_script
> File "build/bdist.linux-x86_64/egg/pkg_resources.py", line 1239, in
> run_script
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/EGG-INFO/scripts/keystone-manage",
> line 28, in <module>
> cli.main(argv=sys.argv, config_files=config_files)
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py",
> line 164, in main
> return run(cmd, (args[:1] + args[2:]))
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py",
> line 147, in run
> return CMDS[cmd](argv=args).run()
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py",
> line 35, in run
> return self.main()
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py",
> line 56, in main
> driver.db_sync()
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/identity/backends/sql.py",
> line 136, in db_sync
> migration.db_sync()
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/common/sql/migration.py",
> line 52, in db_sync
> return versioning_api.upgrade(CONF.sql.connection, repo_path, version)
> File "/usr/local/lib/python2.7/site-packages/migrate/versioning/api.py",
> line 186, in upgrade
> return _migrate(url, repository, version, upgrade=True, err=err,
> **opts)
> File "<string>", line 2, in _migrate
> File
> "/usr/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.
> return f(*a, **kw)
> File "/usr/local/lib/python2.7/site-packages/migrate/versioning/api.py",
> line
> schema.runchange(ver, change, changeset.step)
> File
> "/usr/local/lib/python2.7/site-packages/migrate/versioning/schema.py", li
> change.run(self.engine, step)
> File
> "/usr/local/lib/python2.7/site-packages/migrate/versioning/script/py.py",
> script_func(engine)
> File
> "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keyston
> old_id_col.alter(name='id_hash')
> File
> "/usr/local/lib/python2.7/site-packages/migrate/changeset/schema.py", lin
> ...

Read more...

Revision history for this message
Kevin-Yang (benbenzhuforever) wrote :

Dolph,

Really appreciated for your response.

My VM configuration is:

OS ->
Red Hat Enterprise Linux Server release 6.3 (Santiago)

DB2 ->
Informational tokens are "DB2 v9.7.0.0", "s090521", "LINUXAMD6497", and Fix
Pack "0"
ibm_db -> http://pypi.python.org/packages/source/i/ibm_db/ibm_db-2.0.0.tar.gz#md5=709c576c0ec2379ca15049f5c861beb1
ibm_db_sa ->

When i changed from "ibmdb" -> "ibm_db_sa", I came with a different error -> Could not determine dialect for 'ibm_db_sa'.
##################################################################################
Traceback (most recent call last):
  File "/usr/local/bin/keystone-manage", line 5, in <module>
    pkg_resources.run_script('keystone==2012.2', 'keystone-manage')
  File "build/bdist.linux-x86_64/egg/pkg_resources.py", line 499, in run_script
  File "build/bdist.linux-x86_64/egg/pkg_resources.py", line 1239, in run_script
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/EGG-INFO/scripts/keystone-manage", line 28, in <module>
    cli.main(argv=sys.argv, config_files=config_files)
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 164, in main
    return run(cmd, (args[:1] + args[2:]))
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 147, in run
    return CMDS[cmd](argv=args).run()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 35, in run
    return self.main()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/cli.py", line 56, in main
    driver.db_sync()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/identity/backends/sql.py", line 136, in db_sync
    migration.db_sync()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/common/sql/migration.py", line 49, in db_sync
    current_version = db_version()
  File "/usr/local/lib/python2.7/site-packages/keystone-2012.2-py2.7.egg/keystone/common/sql/migration.py", line 61, in db_version
    return versioning_api.db_version(CONF.sql.connection, repo_path)
  File "<string>", line 2, in db_version
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 155, in with_engine
    engine = construct_engine(url, **kw)
  File "/usr/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 140, in construct_engine
    return create_engine(engine, **kwargs)
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/__init__.py", line 338, in create_engine
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/strategies.py", line 50, in create
  File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/url.py", line 123, in get_dialect

sqlalchemy.exc.ArgumentError: Could not determine dialect for 'ibm_db_sa'.
##################################################################################

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.