Using postgresql and creating a security group rule with protocol value as integer getting DBAPIError exception

[Ashish Kumar Gupta]

Using postgressql and creating a scurity group rule protocol value as integer getting error DBAPIError exception wrapped from operator does not exist.

Running the jenkins :check-tempest-dsvm-ironic-pxe_ssh-postgres-nv fails

Code :
curl -i -X POST http://$Server_ip:9696/v2.0/security-group-rules.json -H "User-Agent: python-neutronclient" -H "X-Auth-Token: $TOKENID" -d '{"security_group_rule": {"ethertype": "IPv4", "direction": "ingress", "protocol": "17", "security_group_id": "$Security_goup_id"}}'

Error in the log:
2014-10-15 06:24:22.756 23647 DEBUG neutron.policy [req-4e3855ad-ef66-4a63-b69d-7351d4a1a4b3 None] Enforcing rules: ['create_security_group_rule'] _build_match_rule /opt/stack/new/neutron/neutron/policy.py:221
2014-10-15 06:24:22.774 23647 ERROR oslo.db.sqlalchemy.exc_filters [req-4e3855ad-ef66-4a63-b69d-7351d4a1a4b3 ] DBAPIError exception wrapped from (ProgrammingError) operator does not exist: character varying = integer
LINE 3: ...on IN ('ingress') AND securitygrouprules.protocol IN (17) AN...
                                                             ^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
 '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.tenant_id = %(tenant_id_1)s AND securitygrouprules.tenant_id IN (%(tenant_id_2)s) AND securitygrouprules.direction IN (%(direction_1)s) AND securitygrouprules.protocol IN (%(protocol_1)s) AND securitygrouprules.ethertype IN (%(ethertype_1)s) AND securitygrouprules.security_group_id IN (%(security_group_id_1)s)' {'direction_1': u'ingress', 'tenant_id_2': u'a0ec4b20678a472ebbab28526cb53fef', 'ethertype_1': 'IPv4', 'protocol_1': 17, 'tenant_id_1': u'a0ec4b20678a472ebbab28526cb53fef', 'security_group_id_1': u'e9936f7a-00dd-4afe-9871-f1ab21fe7ea4'}
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters Traceback (most recent call last):
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/local/lib/python2.7/dist-packages/oslo/db/sqlalchemy/compat/handle_error.py", line 59, in _handle_dbapi_exception
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters e, statement, parameters, cursor, context)
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters exc_info
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters reraise(type(exception), exception, tb=exc_tb)
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters context)
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 324, in do_execute
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters ProgrammingError: (ProgrammingError) operator does not exist: character varying = integer
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters LINE 3: ...on IN ('ingress') AND securitygrouprules.protocol IN (17) AN...
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters ^
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters '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.tenant_id = %(tenant_id_1)s AND securitygrouprules.tenant_id IN (%(tenant_id_2)s) AND securitygrouprules.direction IN (%(direction_1)s) AND securitygrouprules.protocol IN (%(protocol_1)s) AND securitygrouprules.ethertype IN (%(ethertype_1)s) AND securitygrouprules.security_group_id IN (%(security_group_id_1)s)' {'direction_1': u'ingress', 'tenant_id_2': u'a0ec4b20678a472ebbab28526cb53fef', 'ethertype_1': 'IPv4', 'protocol_1': 17, 'tenant_id_1': u'a0ec4b20678a472ebbab28526cb53fef', 'security_group_id_1': u'e9936f7a-00dd-4afe-9871-f1ab21fe7ea4'}
2014-10-15 06:24:22.774 23647 TRACE oslo.db.sqlalchemy.exc_filters
2014-10-15 06:24:22.776 23647 ERROR neutron.api.v2.resource [req-4e3855ad-ef66-4a63-b69d-7351d4a1a4b3 None] create failed

Logs link:

http://logs.openstack.org/54/107654/4/check/check-tempest-dsvm-ironic-pxe_ssh-postgres-nv/18b3c09/logs/screen-q-svc.txt.gz#_2014-10-15_06_24_22_774

http://logs.openstack.org/54/107654/4/check/check-tempest-dsvm-ironic-pxe_ssh-postgres-nv/18b3c09/logs/

[Ann Taraday (akamyshnikova)]

Checked on devstack with PostgreSQL. There is no such error appeared. http://paste.openstack.org/show/125368/

[Ashish Kumar Gupta (ashish-kumar-gupta)]

Make sure you are passing a interger value in the request:
"protocol": "17" --> Value is String in the request

 curl -i -X POST http://127.0.0.1:9696/v2.0/security-group-rules.json -H "User-Agent: python-neutronclient" -H "X-Auth-Token: ab0c81906d7448649b5965e9c9a35f59" -H "Content-type: application/json" -d '{"security_group_rule": {"ethertype": "IPv4", "direction": "ingress", "protocol": "17", "security_group_id": "0633f8eb-ce67-4e98-9e6b-9d83cbd60a86"}}'

Pass as "protocol": 17 value should be integer.

[Ann Taraday (akamyshnikova)]

I use the same request that you add in bug description there was "17" used.
With "protocol": 17 request fails http://paste.openstack.org/show/127449/

The thing is that 'protocol' expects to be string https://github.com/openstack/neutron/blob/4a28612f60f863ac97d67d4ee48fe8ac8345966d/neutron/db/securitygroups_db.py#L79 and I can't find test_create_security_group_rule_with_protocol_integer_value in https://github.com/openstack/tempest/blob/master/tempest/api/network/test_security_groups.py

[OpenStack Infra (hudson-openstack)]

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

[Ann Taraday (akamyshnikova)]

As far as I understand this problem appears, because before creation it is checked that such rule does not exist, so it is tried to do "select ... from securitygrouprule where ... protocol=17" MySQL has more flexible type converting so it finds correct rows in the table, but PostgreSQL fails on this comparing.

[OpenStack Infra (hudson-openstack)]

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

commit bc34f26302faea116be7e61051c32f8787530836
Author: Ann Kamyshnikova <email address hidden>
Date: Mon Nov 10 17:05:54 2014 +0300

    Convert all incoming protocol numbers to string

    PostgreSQL is more sensitive for types than MySQL when it selects
    something from columns in database. So it fails when it tries to
    select from string field comparing with integer value.

    Added unit test to verify conversion of protocol numbers to
    strings.

    Closes-bug:#1381379

    Change-Id: I0a29595403a07c66888871088d5549705a097f68

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/juno
Review: https://review.openstack.org/137173

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/137173
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=c00b4b31276f9ae21fa0a7a213391277bda4cfa7
Submitter: Jenkins
Branch: stable/juno

commit c00b4b31276f9ae21fa0a7a213391277bda4cfa7
Author: Ann Kamyshnikova <email address hidden>
Date: Mon Nov 10 17:05:54 2014 +0300

    Convert all incoming protocol numbers to string

    PostgreSQL is more sensitive for types than MySQL when it selects
    something from columns in database. So it fails when it tries to
    select from string field comparing with integer value.

    Added unit test to verify conversion of protocol numbers to
    strings.

    Closes-bug:#1381379

    (cherry picked from commit bc34f26302faea116be7e61051c32f8787530836)

    Conflicts:
     neutron/tests/unit/test_extension_security_group.py

    Change-Id: I0a29595403a07c66888871088d5549705a097f68

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

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

commit 56fcf7dae508a7a3d437d69fe64aaa1d72acc004
Author: Ann Kamyshnikova <email address hidden>
Date: Mon Nov 10 17:05:54 2014 +0300

    Convert all incoming protocol numbers to string

    PostgreSQL is more sensitive for types than MySQL when it selects
    something from columns in database. So it fails when it tries to
    select from string field comparing with integer value.

    Added unit test to verify conversion of protocol numbers to
    strings.

    Closes-bug:#1381379

    (cherry picked from commit bc34f26302faea116be7e61051c32f8787530836)

    Conflicts:
     neutron/tests/unit/test_extension_security_group.py

    Change-Id: I0a29595403a07c66888871088d5549705a097f68
    (cherry picked from commit c00b4b31276f9ae21fa0a7a213391277bda4cfa7)