OpenStack Compute (nova)

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

Series ocata
Bug #1662012

Bug #1662012 reported by Sean McCully on 2017-02-05

This bug affects 2 people

	Status	Importance	Assigned to
OpenStack Compute (nova)	Fix Released	High	Sean McCully
Newton	Fix Committed	High	Matt Riedemann
Ocata	Fix Committed	High	Matt Riedemann

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}]
======

Tags:

Sean McCully (sean-mccully) on 2017-02-05

Changed in nova:
assignee:	nobody → Sean McCully (sean-mccully)

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-05: Fix proposed to nova (stable/newton)

Fix proposed to branch: stable/newton
Review: https://review.openstack.org/429416

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-07: Fix proposed to nova (master)

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

Changed in nova:
status:	New → In Progress

Revision history for this message

Chris Dent (cdent) wrote on 2017-02-07:

This looks like it is very similar to https://bugs.launchpad.net/nova/+bug/1660959 for which the fix https://review.openstack.org/#/c/427667/ was a little less straightforward than we thought (to be complete). I'll comment on the review too.

Revision history for this message

Sean McCully (sean-mccully) wrote on 2017-02-07:

Should this stay open, does it need to get merged in all the branches?

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-07: Change abandoned on nova (stable/newton)

Change abandoned by Sean McCully (<email address hidden>) on branch: stable/newton
Review: https://review.openstack.org/429416

Chris Dent (cdent) on 2017-02-08

tags:

added: ocata-rc-potential placement scheduler

Matt Riedemann (mriedem) on 2017-02-14

Changed in nova:
importance:	Undecided → High

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-14: Fix proposed to nova (stable/ocata)

Fix proposed to branch: stable/ocata
Review: https://review.openstack.org/433932

Matt Riedemann (mriedem) on 2017-02-14

tags:

added: postgresql
removed: scheduler

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-15: Fix merged to nova (master)

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 <email address hidden>
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

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-15: Fix merged to nova (stable/ocata)

Reviewed: https://review.openstack.org/433932
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=b257e5ad45fffbbd90e5e6e9f4b013a9a0b4291b
Submitter: Jenkins
Branch: stable/ocata

commit b257e5ad45fffbbd90e5e6e9f4b013a9a0b4291b
Author: smccully <email address hidden>
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
    (cherry picked from commit 85c76a7f0827ff8f278d071a0b3f721847079711)

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-02-15: Fix merged to nova (stable/newton)

Reviewed: https://review.openstack.org/429416
Committed: https://git.openstack.org/cgit/openstack/nova/commit/?id=0edc0977aa6e61230655620e9638d4e8e1b85064
Submitter: Jenkins
Branch: stable/newton

commit 0edc0977aa6e61230655620e9638d4e8e1b85064
Author: smccully <email address hidden>
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
    (cherry picked from commit 85c76a7f0827ff8f278d071a0b3f721847079711)
    (cherry picked from commit b257e5ad45fffbbd90e5e6e9f4b013a9a0b4291b)