Postgres error: column "reservations.expiration" must appear in the GROUP BY clause or be used in an aggregate function

Bug #1486467 reported by Lucas Alvares Gomes
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
neutron
Fix Released
Critical
Ann Taraday

Bug Description

A job in the Ironic gate gate-tempest-dsvm-ironic-pxe_ssh-postgres is broken due some change in Neutron which is affecting the Postgres database backend.

The error is:

2015-08-19 03:23:38.802 ERROR oslo_db.sqlalchemy.exc_filters [req-61b2cae0-f5a2-420e-b2ab-e3db3e3b00da admin admin] DBAPIError exception wrapped from (psycopg2.ProgrammingError) column "reservations.expiration" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...sourcedeltas.resource AS resourcedeltas_resource, reservatio...
                                                             ^
 [SQL: 'SELECT resourcedeltas.resource AS resourcedeltas_resource, reservations.expiration AS reservations_expiration, sum(resourcedeltas.amount) AS sum_1 \nFROM resourcedeltas JOIN reservations ON reservations.id = resourcedeltas.reservation_id \nWHERE reservations.tenant_id = %(tenant_id_1)s AND resourcedeltas.resource IN (%(resource_1)s) AND reservations.expiration >= %(expiration_1)s GROUP BY resourcedeltas.resource'] [parameters: {'resource_1': 'network', 'expiration_1': datetime.datetime(2015, 8, 19, 3, 23, 38, 799013), 'tenant_id_1': u'666f340431e341f89816478ad8460634'}]
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters context)
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters ProgrammingError: column "reservations.expiration" must appear in the GROUP BY clause or be used in an aggregate function
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters LINE 1: ...sourcedeltas.resource AS resourcedeltas_resource, reservatio...
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters ^
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters
2015-08-19 03:23:38.802 13949 ERROR oslo_db.sqlalchemy.exc_filters

More logs: http://logs.openstack.org/56/213856/4/check/gate-tempest-dsvm-ironic-pxe_ssh-postgres/91c7a5c/logs/screen-q-svc.txt.gz#_2015-08-19_03_23_38_802

The command that failed: http://logs.openstack.org/56/213856/4/check/gate-tempest-dsvm-ironic-pxe_ssh-postgres/91c7a5c/logs/devstacklog.txt.gz#_2015-08-19_03_23_38_854

It seems to be caused by https://review.openstack.org/#/c/163659/

description: updated
Changed in neutron:
importance: Undecided → Critical
Revision history for this message
Lucas Alvares Gomes (lucasagomes) wrote :

I have rechecked the problem after this patch [1] have been merged. But seems that was not the cause of the problem [2]

[1] https://review.openstack.org/#/c/214282/

[2] http://logs.openstack.org/56/213856/4/check/gate-tempest-dsvm-ironic-pxe_ssh-postgres/4edf470/logs/screen-q-svc.txt.gz#_2015-08-19_10_28_35_245

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (master)

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

Changed in neutron:
assignee: nobody → Ann Kamyshnikova (akamyshnikova)
status: New → In Progress
Revision history for this message
Valeriy Ponomaryov (vponomaryov) wrote :

Also affects Ci job "gate-manila-tempest-dsvm-neutron-postgres".

Changed in neutron:
milestone: none → liberty-3
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (master)

Reviewed: https://review.openstack.org/214564
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=9ebc9f808316ca249348d2c5cd2b9762373f96dd
Submitter: Jenkins
Branch: master

commit 9ebc9f808316ca249348d2c5cd2b9762373f96dd
Author: Ann Kamyshnikova <email address hidden>
Date: Wed Aug 19 14:19:11 2015 +0300

    Fix query in get_reservations_for_resources

    For PostgreSQL if you're using GROUP BY everything in the SELECT
    list must be an aggregate SUM(...) or used in the GROUP BY.
    For reference:
    http://<email address hidden>
    Closes-bug: #1486467

    Change-Id: Ieb4ead5c785ff17f580bfbc58f370a491733d96d

Changed in neutron:
status: In Progress → Fix Committed
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (feature/pecan)

Fix proposed to branch: feature/pecan
Review: https://review.openstack.org/218710

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (feature/pecan)
Download full text (155.6 KiB)

Reviewed: https://review.openstack.org/218710
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=2c5f44e1b3bd4ed8a0b7232fd293b576cc8c1c87
Submitter: Jenkins
Branch: feature/pecan

commit f35d1c5c50dccbef1a2e079f967b82f0df0e22e9
Author: Adelina Tuvenie <email address hidden>
Date: Thu Aug 27 02:27:28 2015 -0700

    Fixes wrong neutron Hyper-V Agent name in constants

    Change Id03fb147e11541be309c1cd22ce27e70fadc28b5 moved the
    AGENT_TYPE_HYPERV constant from common.constants to
    plugins.ml2.drivers.hyperv.constants but change the value of the
    constant from 'HyperV agent' to 'hyperv'. This patch changes
    the name back to 'HyperV agent'

    Change-Id: If74b4b2a84811e266c8b12e70bf6bfe74ed4ea21
    Partial-Bug: #1487598

commit de604de334854e2eb6b4312ff57920564cbd4459
Author: OpenStack Proposal Bot <email address hidden>
Date: Sun Aug 30 01:39:06 2015 +0000

    Updated from global requirements

    Change-Id: Ie52aa3b59784722806726e4046bd07f4a4d97328

commit f0415ac20eaf5ab4abb9bd4839bf6d04ceee85d0
Author: armando-migliaccio <email address hidden>
Date: Fri Aug 28 13:53:04 2015 -0700

    Revert "Add support for unaddressed port"

    This implementation may expose a vulnerability where a malicious
    user can sieze the opportunity of a time window where a port
    may land unaddressed on a shared network, thus allowing him/her
    to suck up all the tenant traffic he/she wants....oh the shivers.

    This reverts commit d4c52b7f5a36a103a92bf9dcda7f371959112292.

    Change-Id: I7ebdaa8d3defa80eab90e460fde541a5bdd8864c

commit 013fdcd2a6d45dbe4de5d6e7077e5e9b60985ef9
Author: Assaf Muller <email address hidden>
Date: Fri Aug 28 16:41:07 2015 -0400

    Improve logging upon failure in iptables functional tests

    This will help us nail down a more accurate and efficient logstash
    query.

    Change-Id: Iee4238e358f7b056e373c7be8d6aa3202117a680
    Related-Bug: #1478847

commit 622dea818d851224a43d5276a81d5ce8a6eebb76
Author: Ivar Lazzaro <email address hidden>
Date: Mon Aug 17 17:17:42 2015 -0700

    handle gw_info outside of the db transaction on router creation

    Move the gateway interface creation outside the DB transaction
    to avoid lock timeout.

    Change-Id: I5a78d7f32e8ca912016978105221d5f34618af19
    Closes-bug: 1485809

commit 5b27d290a0a95f6247fc5a0fe6da1e7d905e6b2d
Author: Assaf Muller <email address hidden>
Date: Wed Aug 26 10:07:03 2015 -0400

    Remove ml2 resource extension success logging

    This is the cause of a tremendous amount of logs, for no
    perceivable gain. A normal dvr run in the gate shows this debug
    message around 120K times, which is way too much.

    Closes-Bug: #1489952

    Change-Id: I26fca8515d866a7cc1638d07fa33bc04479ae221

commit 8d3faf549cba2f58c872ef4121b2481e73464010
Author: huangpengtao <email address hidden>
Date: Fri Aug 28 23:20:46 2015 +0800

    Replace "prt" variable by "port"

    the local variable prt is meaningless,
    and port is used popular.

    Change-Id: I20849102cf5b4d84433c46791b4b1e2a22dc4739

commit ee374e7a5f4dea538fcd942f5...

tags: added: in-feature-pecan
Thierry Carrez (ttx)
Changed in neutron:
status: Fix Committed → Fix Released
Thierry Carrez (ttx)
Changed in neutron:
milestone: liberty-3 → 7.0.0
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.