yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1660959] Re: placement resource provider filtering does not work with postgres

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: OpenStack Infra <1660959@xxxxxxxxxxxxxxxxxx>
Date: Thu, 02 Feb 2017 00:06:38 -0000
Reply-to: Bug 1660959 <1660959@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Reviewed:  https://review.openstack.org/427667
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=03eced19f5d6665724a4fa432401be742383f8cf
Submitter: Jenkins
Branch:    master

commit 03eced19f5d6665724a4fa432401be742383f8cf
Author: Mehdi Abaakouk <sileht@xxxxxxxxxx>
Date:   Wed Feb 1 13:31:38 2017 +0100

    placement-api: fix ResourceProviderList query
    
    The ResourceProviderList query use groupby without all grouped columns.
    This works on mysql with unpredicable result, but don't for other RDBMS.
    
    For example, postgresql gating jobs dsvm that use nova are currently
    broken.
    
    This change removes the unused consumer_id on first query,
    and uses the primary key 'id' instead of 'uuid' the second groupby.
    (Because groupby in postgresql requires a PK or all non-primary columns)
    
    The fix is tested by gate-ceilometer-dsvm-tempest-plugin-postgresql-ubuntu-xenial job
    here: https://review.openstack.org/#/c/427668/
    
    closes-bug: #1660959
    Change-Id: I6cc93ba0dd569d56696c9210d38dd2d77b4157c1


** Changed in: nova
       Status: In Progress => Fix Released

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to OpenStack Compute (nova).
https://bugs.launchpad.net/bugs/1660959

Title:
  placement resource provider filtering does not work with postgres

Status in OpenStack Compute (nova):
  Fix Released

Bug description:
  Telemetry tests with postgres found a bug in the sql used to filter
  resource providers that is breaking their gate:

  http://logs.openstack.org/82/405682/8/check/gate-ceilometer-dsvm-
  tempest-plugin-postgresql-ubuntu-xenial/02f896f/logs/apache/placement-
  api.txt.gz?level=ERROR

  The fix appears to be adding to the group_by on the usage join:

           usage = usage.group_by(_ALLOC_TBL.c.resource_provider_id,
  -                               _ALLOC_TBL.c.resource_class_id)
  +                               _ALLOC_TBL.c.resource_class_id,
  +                               _ALLOC_TBL.c.consumer_id)

  Not sure about the ordering.

  (full log example below)




  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler [req-f0c425b6-bd71-44ae-ae33-46ce688d53dd service placement] Uncaught exception
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler Traceback (most recent call last):
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/opt/stack/new/nova/nova/api/openstack/placement/handler.py", line 195, in __call__
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return dispatch(environ, start_response, self._map)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/opt/stack/new/nova/nova/api/openstack/placement/handler.py", line 122, in dispatch
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return handler(environ, start_response)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 130, in __call__
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     resp = self.call_func(req, *args, **self.kwargs)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/webob/dec.py", line 195, in call_func
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return self.func(req, *args, **kwargs)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/opt/stack/new/nova/nova/api/openstack/placement/util.py", line 55, in decorated_function
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return f(req)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/opt/stack/new/nova/nova/api/openstack/placement/handlers/resource_provider.py", line 305, in list_resource_providers
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     context, filters)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/opt/stack/new/nova/nova/objects/resource_provider.py", line 695, in get_all_by_filters
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     resource_providers = cls._get_all_by_filters_from_db(context, filters)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/oslo_db/sqlalchemy/enginefacade.py", line 894, in wrapper
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return fn(*args, **kwargs)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/opt/stack/new/nova/nova/objects/resource_provider.py", line 675, in _get_all_by_filters_from_db
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return query.all()
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2613, in all
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return list(self)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2761, in __iter__
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return self._execute_and_instances(context)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2776, in _execute_and_instances
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     result = conn.execute(querycontext.statement, self._params)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return meth(self, multiparams, params)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     return connection._execute_clauseelement(self, multiparams, params)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     compiled_sql, distilled_params
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     context)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     util.raise_from_cause(newraise, exc_info)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     reraise(type(exception), exception, tb=exc_tb, cause=cause)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     context)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler     cursor.execute(statement, parameters)
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler DBError: (psycopg2.ProgrammingError) column "allocations.consumer_id" must appear in the GROUP BY clause or be used in an aggregate function
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler LINE 2: ...ons.resource_provider_id AS resource_provider_id, allocation...
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler                                                              ^
  2017-02-01 10:20:33.543 8670 ERROR nova.api.openstack.placement.handler  [SQL: 'SELECT resource_providers.created_at AS resource_providers_created_at, resource_providers.updated_at AS resource_providers_updated_at, resource_providers.id AS resource_providers_id, resource_providers.uuid AS resource_providers_uuid, resource_providers.name AS resource_providers_name, resource_providers.generation AS resource_providers_generation, resource_providers.can_host AS resource_providers_can_host \\nFROM resource_providers JOIN inventories ON resource_providers.id = inventories.resource_provider_id LEFT OUTER JOIN (SELECT allocations.resource_provider_id AS resource_provider_id, allocations.consumer_id AS consumer_id, allocations.resource_class_id AS resource_class_id, sum(allocations.used) AS used \\nFROM allocations \\nWHERE allocations.resource_class_id IN (%(resource_class_id_1)s, %(resource_class_id_2)s) GROUP BY allocations.resource_provider_id, allocations.resource_class_id) AS usage ON usage.resource_provider_id = inventories.resource_provider_id AND usage.resource_class_id = inventories.resource_class_id \\nWHERE resource_providers.can_host = %(can_host_1)s AND (inventories.resource_class_id = %(resource_class_id_3)s AND coalesce(usage.used, %(param_1)s) + %(coalesce_1)s <= (inventories.total - inventories.reserved) * inventories.allocation_ratio AND inventories.min_unit <= %(min_unit_1)s AND inventories.max_unit >= %(max_unit_1)s AND %(step_size_1)s %% inventories.step_size = %(param_2)s OR inventories.resource_class_id = %(resource_class_id_4)s AND coalesce(usage.used, %(param_3)s) + %(coalesce_2)s <= (inventories.total - inventories.reserved) * inventories.allocation_ratio AND inventories.min_unit <= %(min_unit_2)s AND inventories.max_unit >= %(max_unit_2)s AND %(step_size_2)s %% inventories.step_size = %(param_4)s) GROUP BY resource_providers.uuid \\nHAVING count(DISTINCT inventories.resource_class_id) = %(count_1)s'] [parameters: {'coalesce_2': 64, 'step_size_1': 1, 'count_1': 2, 'coalesce_1': 1, 'param_4': 0, 'step_size_2': 64, 'param_1': 0, 'param_3': 0, 'param_2': 0, 'can_host_1': 0, 'max_unit_2': 64, 'max_unit_1': 1, 'resource_class_id_1': 0, 'resource_class_id_3': 0, 'resource_class_id_2': 1, 'min_unit_2': 64, 'resource_class_id_4': 1, 'min_unit_1': 1}]

To manage notifications about this bug go to:
https://bugs.launchpad.net/nova/+bug/1660959/+subscriptions
References

[Bug 1660959] [NEW] placement resource provider filtering does not work with postgres
From: Chris Dent, 2017-02-01