yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1662012] Re: column "allocations.consumer_id" must appear in the GROUP BY clause or be used in an aggregate function

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: OpenStack Infra <1662012@xxxxxxxxxxxxxxxxxx>
Date: Wed, 15 Feb 2017 04:53:22 -0000
Reply-to: Bug 1662012 <1662012@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Reviewed:  https://review.openstack.org/430399
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=85c76a7f0827ff8f278d071a0b3f721847079711
Submitter: Jenkins
Branch:    master

commit 85c76a7f0827ff8f278d071a0b3f721847079711
Author: smccully <smccully@xxxxxxxxxxxxxxxxxxxxxxxxx>
Date:   Sun Feb 5 21:43:31 2017 +0000

    allocations.consumer_id is not used in query.
    
    PostGreSQL required consumer_id in group by clause, but
    consumer_id is not being used in the query and is superfluous.
    
    Change-Id: I47b758b949b7fbed70906e9a95cbe7bb99da13c2
    Closes-Bug: 1662012


** 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/1662012

Title:
   column "allocations.consumer_id" must appear in the GROUP BY clause
  or be used in an aggregate function

Status in OpenStack Compute (nova):
  Fix Released
Status in OpenStack Compute (nova) newton series:
  In Progress
Status in OpenStack Compute (nova) ocata series:
  In Progress

Bug description:
  1. Nova Placement API with PostgreSQL Database throws
  psycopg2.ProgrammingError requires allocations.consumer_id in group by
  clause

  
  =============
   [SQL: 'SELECT resource_providers.id AS resource_provider_id, resource_providers.uuid, resource_providers.generation, inventories.resource_class_id, inventories.total, inventories.reserved, inventories.allocation_ratio, inventories.min_unit, inventories.max_unit, inventories.step_size, usage.used \nFROM resource_providers JOIN inventories ON resource_providers.id = inventories.resource_provider_id AND inventories.resource_class_id IN (%(resource_class_id_1)s, %(resource_class_id_2)s, %(resource_class_id_3)s) 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_4)s, %(resource_class_id_5)s, %(resource_class_id_6)s) GROUP BY allocations.resource_provider_id, allocations.resource_class_id) AS usage ON inventories.resource_provider_id = usage.resource_provider_id AND inventories.resource_class_id = usage.resource_class_id \nWHERE resource_providers.uuid IN (%(uuid_1)s) AND inventories.resource_class_id IN (%(resource_class_id_7)s, %(resource_class_id_8)s, %(resource_class_id_9)s)'] [parameters: {'uuid_1': '06a38451-1efd-428a-ad55-34f790e1d0ca', 'resource_class_id_9': 2, 'resource_class_id_8': 1, 'resource_class_id_1': 0, 'resource_class_id_3': 2, 'resource_class_id_2': 1, 'resource_class_id_5': 1, 'resource_class_id_4': 0, 'resource_class_id_7': 0, 'resource_class_id_6': 2}]
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters     context)
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters     cursor.execute(statement, parameters)
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters ProgrammingError: column "allocations.consumer_id" must appear in the GROUP BY clause or be used in an aggregate function
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters LINE 2: ...ons.resource_provider_id AS resource_provider_id, allocation...
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters                                                              ^
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters 
  2017-02-05 21:27:29.412 11050 ERROR oslo_db.sqlalchemy.exc_filters 
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler [req-331f66db-4213-4b5a-92af-062897d7b886 f33df19f53574efdb6019b8cc549519f c4302c69d4c74e22a6386002a36b0e04 - default default] Uncaught exception
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler Traceback (most recent call last):
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/api/openstack/placement/handler.py", line 195, in __call__
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return dispatch(environ, start_response, self._map)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/api/openstack/placement/handler.py", line 122, in dispatch
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return handler(environ, start_response)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/webob/dec.py", line 130, in __call__
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     resp = self.call_func(req, *args, **self.kwargs)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/webob/dec.py", line 195, in call_func
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return self.func(req, *args, **kwargs)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/api/openstack/placement/util.py", line 133, in decorated_function
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return f(req)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/api/openstack/placement/handlers/allocation.py", line 254, in set_allocations
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     allocations.create_all()
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/objects/resource_provider.py", line 1185, in create_all
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     self._set_allocations(self._context, self.objects)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/enginefacade.py", line 894, in wrapper
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return fn(*args, **kwargs)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/objects/resource_provider.py", line 1147, in _set_allocations
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     before_gens = _check_capacity_exceeded(conn, allocs)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/nova/objects/resource_provider.py", line 1012, in _check_capacity_exceeded
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     records = conn.execute(sel)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return meth(self, multiparams, params)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     return connection._execute_clauseelement(self, multiparams, params)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     compiled_sql, distilled_params
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     context)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     util.raise_from_cause(newraise, exc_info)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     reraise(type(exception), exception, tb=exc_tb, cause=cause)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     context)
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler   File "/usr/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler     cursor.execute(statement, parameters)
  2017-02-05 21:27:29.414 11050 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-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler LINE 2: ...ons.resource_provider_id AS resource_provider_id, allocation...
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler                                                              ^
  2017-02-05 21:27:29.414 11050 ERROR nova.api.openstack.placement.handler  [SQL: 'SELECT resource_providers.id AS resource_provider_id, resource_providers.uuid, resource_providers.generation, inventories.resource_class_id, inventories.total, inventories.reserved, inventories.allocation_ratio, inventories.min_unit, inventories.max_unit, inventories.step_size, usage.used \nFROM resource_providers JOIN inventories ON resource_providers.id = inventories.resource_provider_id AND inventories.resource_class_id IN (%(resource_class_id_1)s, %(resource_class_id_2)s, %(resource_class_id_3)s) 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_4)s, %(resource_class_id_5)s, %(resource_class_id_6)s) GROUP BY allocations.resource_provider_id, allocations.resource_class_id) AS usage ON inventories.resource_provider_id = usage.resource_provider_id AND inventories.resource_class_id = usage.resource_class_id \nWHERE resource_providers.uuid IN (%(uuid_1)s) AND inventories.resource_class_id IN (%(resource_class_id_7)s, %(resource_class_id_8)s, %(resource_class_id_9)s)'] [parameters: {'uuid_1': '06a38451-1efd-428a-ad55-34f790e1d0ca', 'resource_class_id_9': 2, 'resource_class_id_8': 1, 'resource_class_id_1': 0, 'resource_class_id_3': 2, 'resource_class_id_2': 1, 'resource_class_id_5': 1, 'resource_class_id_4': 0, 'resource_class_id_7': 0, 'resource_class_id_6': 2}]
  ======

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

[Bug 1662012] [NEW] column "allocations.consumer_id" must appear in the GROUP BY clause or be used in an aggregate function
From: Sean McCully, 2017-02-05