yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1811873] [NEW] get_l3_agent_with_min_routers fails with postgresql backend

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: Andrew Karpow <1811873@xxxxxxxxxxxxxxxxxx>
Date: Tue, 15 Jan 2019 17:17:28 -0000
Reply-to: Bug 1811873 <1811873@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Public bug reported:

We have our own L3 agent that uses the generic neutron function
get_l3_agent_with_min_routers, rendering following exception if using a
postgresql backend:

2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/neutron/db/l3_agentschedulers_db.py", line 469, in get_l3_agent_with_min_routers
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     context, agent_ids)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/neutron/objects/agent.py", line 102, in get_l3_agent_with_min_routers
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     res = query.filter(agent_model.Agent.id.in_(agent_ids)).first()
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2778, in first
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     ret = list(self[0:1])
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2570, in __getitem__
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return list(res)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2878, in __iter__
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return self._execute_and_instances(context)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2901, in _execute_and_instances
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     result = conn.execute(querycontext.statement, self._params)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return meth(self, multiparams, params)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return connection._execute_clauseelement(self, multiparams, params)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     compiled_sql, distilled_params
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     context)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1409, in _handle_dbapi_exception
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     util.raise_from_cause(newraise, exc_info)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     reraise(type(exception), exception, tb=exc_tb, cause=cause)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     context)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     cursor.execute(statement, parameters)
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource DBError: (psycopg2.ProgrammingError) column "agents.agent_type" must appear in the GROUP BY clause or be used in an aggregate function
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource LINE 1: SELECT agents.id AS agents_id, agents.agent_type AS agents_a...
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource                                        ^
2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource  [SQL: 'SELECT agents.id AS agents_id, agents.agent_type AS agents_agent_type, agents."binary" AS agents_binary, agents.topic AS agents_topic, agents.host AS agents_host, agents.availability_zone AS agents_availability_zone, agents.admin_state_up AS agents_admin_state_up, agents.created_at AS agents_created_at, agents.started_at AS agents_started_at, agents.heartbeat_timestamp AS agents_heartbeat_timestamp, agents.description AS agents_description, agents.configurations AS agents_configurations, agents.resource_versions AS agents_resource_versions, agents.load AS agents_load, count(routerl3agentbindings.router_id) AS count \nFROM agents LEFT OUTER JOIN routerl3agentbindings ON agents.id = routerl3agentbindings.l3_agent_id \nWHERE agents.id IN (%(id_1)s) GROUP BY agents.id, routerl3agentbindings.l3_agent_id ORDER BY count \n LIMIT %(param_1)s'] [parameters: {'id_1': u'fd1952bb-0f16-4e6e-bf57-7108729fac25', 'param_1': 1}] (Background on this error at: http://sqlalche.me/e/f405)


postgresql needs all SELECT columns to be included in the group_by clause, I will shortly add an fix proposal

** Affects: neutron
     Importance: Undecided
         Status: New

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to neutron.
https://bugs.launchpad.net/bugs/1811873

Title:
  get_l3_agent_with_min_routers fails with postgresql backend

Status in neutron:
  New

Bug description:
  We have our own L3 agent that uses the generic neutron function
  get_l3_agent_with_min_routers, rendering following exception if using
  a postgresql backend:

  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/neutron/db/l3_agentschedulers_db.py", line 469, in get_l3_agent_with_min_routers
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     context, agent_ids)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/neutron/objects/agent.py", line 102, in get_l3_agent_with_min_routers
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     res = query.filter(agent_model.Agent.id.in_(agent_ids)).first()
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2778, in first
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     ret = list(self[0:1])
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2570, in __getitem__
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return list(res)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2878, in __iter__
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return self._execute_and_instances(context)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2901, in _execute_and_instances
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     result = conn.execute(querycontext.statement, self._params)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return meth(self, multiparams, params)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     return connection._execute_clauseelement(self, multiparams, params)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     compiled_sql, distilled_params
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     context)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1409, in _handle_dbapi_exception
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     util.raise_from_cause(newraise, exc_info)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     reraise(type(exception), exception, tb=exc_tb, cause=cause)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     context)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource   File "/var/lib/openstack/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource     cursor.execute(statement, parameters)
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource DBError: (psycopg2.ProgrammingError) column "agents.agent_type" must appear in the GROUP BY clause or be used in an aggregate function
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource LINE 1: SELECT agents.id AS agents_id, agents.agent_type AS agents_a...
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource                                        ^
  2019-01-15 16:34:33,091.091 59 ERROR neutron.api.v2.resource  [SQL: 'SELECT agents.id AS agents_id, agents.agent_type AS agents_agent_type, agents."binary" AS agents_binary, agents.topic AS agents_topic, agents.host AS agents_host, agents.availability_zone AS agents_availability_zone, agents.admin_state_up AS agents_admin_state_up, agents.created_at AS agents_created_at, agents.started_at AS agents_started_at, agents.heartbeat_timestamp AS agents_heartbeat_timestamp, agents.description AS agents_description, agents.configurations AS agents_configurations, agents.resource_versions AS agents_resource_versions, agents.load AS agents_load, count(routerl3agentbindings.router_id) AS count \nFROM agents LEFT OUTER JOIN routerl3agentbindings ON agents.id = routerl3agentbindings.l3_agent_id \nWHERE agents.id IN (%(id_1)s) GROUP BY agents.id, routerl3agentbindings.l3_agent_id ORDER BY count \n LIMIT %(param_1)s'] [parameters: {'id_1': u'fd1952bb-0f16-4e6e-bf57-7108729fac25', 'param_1': 1}] (Background on this error at: http://sqlalche.me/e/f405)

  
  postgresql needs all SELECT columns to be included in the group_by clause, I will shortly add an fix proposal

To manage notifications about this bug go to:
https://bugs.launchpad.net/neutron/+bug/1811873/+subscriptions
Follow ups

[Bug 1811873] Re: get_l3_agent_with_min_routers fails with postgresql backend
From: OpenStack Infra, 2019-04-12