can't create security group rule by ip protocol when using postgresql

Bug #1330490 reported by gustavo panizzo
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
neutron
Fix Released
Undecided
Ralf Haferkamp
Icehouse
Fix Released
Undecided
Ralf Haferkamp

Bug Description

when i try to create a rule in sec group using ip protocol number it fails if the db in use is postgresql

i can repeat the problem in havana, icehouse and master

2014-06-16 08:41:07.009 15134 ERROR neutron.api.v2.resource [req-3d2d03a3-2d8a-4ad0-b41d-098aecd5ecb8 None] create failed
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource Traceback (most recent call last):
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/api/v2/resource.py", line 87, in resource
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource result = method(request=request, **args)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/api/v2/base.py", line 419, in create
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource obj = obj_creator(request.context, **kwargs)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/db/securitygroups_rpc_base.py", line 43, in create_security_group_rule
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource bulk_rule)[0]
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/db/securitygroups_db.py", line 266, in create_security_group_rule_bulk_native
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource self._check_for_duplicate_rules(context, r)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/db/securitygroups_db.py", line 394, in _check_for_duplicate_rules
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource db_rules = self.get_security_group_rules(context, filters)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/db/securitygroups_db.py", line 421, in get_security_group_rules
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource page_reverse=page_reverse)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/neutron/db/db_base_plugin_v2.py", line 197, in _get_collection
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource items = [dict_func(c, fields) for c in query]
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2353, in __iter__
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource return self._execute_and_instances(context)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/orm/query.py", line 2368, in _execute_and_instances
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource result = conn.execute(querycontext.statement, self._params)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 662, in execute
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource params)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 761, in _execute_clauseelement
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource compiled_sql, distilled_params
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource context)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource exc_info
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource reraise(type(exception), exception, tb=exc_tb)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource context)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource cursor.execute(statement, parameters)
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource ProgrammingError: (ProgrammingError) operator does not exist: character varying = integer
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource LINE 3: ...on IN ('ingress') AND securitygrouprules.protocol IN (112) A...
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource ^
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource 'SELECT securitygrouprules.tenant_id AS securitygrouprules_tenant_id, securitygrouprules.id AS securitygrouprules_id, securitygrouprules.security_group_id AS securitygrouprules_security_group_id, securitygrouprules.remote_group_id AS securitygrouprules_remote_group_id, securitygrouprules.direction AS securitygrouprules_direction, securitygrouprules.ethertype AS securitygrouprules_ethertype, securitygrouprules.protocol AS securitygrouprules_protocol, securitygrouprules.port_range_min AS securitygrouprules_port_range_min, securitygrouprules.port_range_max AS securitygrouprules_port_range_max, securitygrouprules.remote_ip_prefix AS securitygrouprules_remote_ip_prefix \nFROM securitygrouprules \nWHERE securitygrouprules.direction IN (%(direction_1)s) AND securitygrouprules.protocol IN (%(protocol_1)s) AND securitygrouprules.tenant_id IN (%(tenant_id_1)s) AND securitygrouprules.security_group_id IN (%(security_group_id_1)s) AND securitygrouprules.remote_ip_prefix IN (%(remote_ip_prefix_1)s) AND securitygrouprules.ethertype IN (%(ethertype_1)s)' {'direction_1': u'ingress', 'tenant_id_1': u'd42aacf8661045beb3a9ee7585bb0c8a', 'protocol_1': 112, 'ethertype_1': 'IPv4', 'security_group_id_1': u'05e7d48c-c163-4dc9-8b46-d100f136e786', 'remote_ip_prefix_1': u'0.0.0.0/0'}
2014-06-16 08:41:07.009 15134 TRACE neutron.api.v2.resource

Changed in neutron:
assignee: nobody → Ralf Haferkamp (rhafer)
status: New → In Progress
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (master)

Reviewed: https://review.openstack.org/100942
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=3be62f878e8165cf3bce357a00b8a583773f7d3b
Submitter: Jenkins
Branch: master

commit 3be62f878e8165cf3bce357a00b8a583773f7d3b
Author: Ralf Haferkamp <email address hidden>
Date: Wed Jun 18 17:01:26 2014 +0200

    Don't convert numeric protocol values to int

    They are treated as strings everywhere. Converting them to int causes problems
    when using postgresql as the database backend because it doesn't automatically
    cast them back to integer.

    Change-Id: I9f0a5149d24a4c003409728e50376569c97e7325
    Closes-bug: 1330490

Changed in neutron:
status: In Progress → Fix Committed
Ralf Haferkamp (rhafer)
tags: added: havana-backport-potential icehouse-backport-potential
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to neutron (stable/havana)

Fix proposed to branch: stable/havana
Review: https://review.openstack.org/102753

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

Fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/102754

Changed in neutron:
milestone: none → juno-2
status: Fix Committed → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Change abandoned on neutron (stable/havana)

Change abandoned by Alan Pevec (<email address hidden>) on branch: stable/havana
Review: https://review.openstack.org/102753
Reason: Final Havana release 2013.2.4 has been cut and stable/havana is going to be removed in a week.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to neutron (stable/icehouse)

Reviewed: https://review.openstack.org/102754
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=a17a50057594027739c819264cebb3405ee8be42
Submitter: Jenkins
Branch: stable/icehouse

commit a17a50057594027739c819264cebb3405ee8be42
Author: Ralf Haferkamp <email address hidden>
Date: Wed Jun 18 17:01:26 2014 +0200

    Don't convert numeric protocol values to int

    They are treated as strings everywhere. Converting them to int causes problems
    when using postgresql as the database backend because it doesn't automatically
    cast them back to integer.

    Change-Id: I9f0a5149d24a4c003409728e50376569c97e7325
    Closes-bug: 1330490
    (cherry picked from commit 3be62f878e8165cf3bce357a00b8a583773f7d3b)

Thierry Carrez (ttx)
Changed in neutron:
milestone: juno-2 → 2014.2
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.