Public bug reported: 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 u sage.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}] ** Affects: nova Importance: Undecided Status: Triaged ** Tags: placement scheduler -- 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): Triaged 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
