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 <[email protected]> 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_si ze_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 -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : [email protected] Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp
