yahoo-eng-team team mailing list archive

Thread
Date
[Bug 1811873] Re: get_l3_agent_with_min_routers fails with postgresql backend

To: yahoo-eng-team@xxxxxxxxxxxxxxxxxxx
From: OpenStack Infra <1811873@xxxxxxxxxxxxxxxxxx>
Date: Fri, 12 Apr 2019 13:01:44 -0000
Reply-to: Bug 1811873 <1811873@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
Reviewed:  https://review.openstack.org/631046
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=23ca23cc792011e69e89a3d08f54072500749d75
Submitter: Zuul
Branch:    master

commit 23ca23cc792011e69e89a3d08f54072500749d75
Author: Andrew Karpow <andrew.karpow@xxxxxxx>
Date:   Tue Jan 15 18:22:53 2019 +0100

    postgresql compatibility for get_l3_agent routines
    
    This commit fixes a bug caused by the sqlalchemy group_by
    statement in the get_l3_agent routines when using postgresql.
    All select statements need to be replicated in the group_by statement.
    
    Change-Id: Ib938be79408d78e0973fed10a68934397341c180
    Closes-Bug: #1811873


** Changed in: neutron
       Status: In Progress => Fix Released

-- 
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:
  Fix Released

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
References

[Bug 1811873] [NEW] get_l3_agent_with_min_routers fails with postgresql backend
From: Andrew Karpow, 2019-01-15