ProgrammingError: "You have an error in your SQL syntax"

[Volodymyr Litovka]

Hi colleagues,

I hit easily reproducible bug in Heat + Neutron. Using the following template:

heat_template_version: 2017-02-24
resources:
  lan-net:
    type: OS::Neutron::Net
    properties:
      name: jt-net
  dm-subnet:
    type: OS::Neutron::Subnet
    properties:
      name: dm-subnet
      network: { get_resource: lan-net }
      enable_dhcp: False
      cidr: 10.0.1.0/24
  rPort:
    type: OS::Neutron::Port
    properties:
      name: rPort
      network: { get_resource: lan-net }
      fixed_ips:
        - { subnet: { get_resource: dm-subnet } }

stack creation is ok. UPDATING stack with commented out 'fixed_ips' section in rPort definition:

  rPort:
    type: OS::Neutron::Port
    properties:
      name: rPort
      network: { get_resource: lan-net }
# fixed_ips:
# - { subnet: { get_resource: dm-subnet } }

causes fail with the message "Resource UPDATE failed: InternalServerError: resources.rPort: Request Failed: internal server error while processing your request. Neutron server returns request_ids: ['req-3f9baf42-3423-4242-bab6-87d34f1d6301']"

and the following message in the neutron-server.log:

2017-08-15 08:33:38.302 5764 DEBUG neutron.api.v2.base [req-3f9baf42-3423-4242-bab6-87d34f1d6301 0d01892c43b0498198c4716d510c6667 d8051a3ff3ad4c4bb380f828992b8178 - - -] Request body: {u'port': {}} prepare_request_body /usr/lib/python2.7/dist-packages/neutron/api/v2/base.py:690
2017-08-15 08:33:38.500 5764 ERROR oslo_db.sqlalchemy.exc_filters [req-3f9baf42-3423-4242-bab6-87d34f1d6301 0d01892c43b0498198c4716d510c6667 d8051a3ff3ad4c4bb380f828992b8178 - - -] DBAPIError exception wrapped from (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} WHERE ml2_port_bindings.port_id = '989214cc-866d-429e-a805-804afa9fbed3' AND m' at line 1") [SQL: u'UPDATE ml2_port_bindings SET vnic_type=%(vnic_type)s WHERE ml2_port_bindings.port_id = %(ml2_port_bindings_port_id)s AND ml2_port_bindings.host = %(ml2_port_bindings_host)s'] [parameters: {'vnic_type': {}, 'ml2_port_bindings_host': u'', 'ml2_port_bindings_port_id': u'989214cc-866d-429e-a805-804afa9fbed3'}]

(full log attached)

On the other hand, CREATION of stack using the same template (one, which caused update to fail, with commented lines) finishes successfully without errors:

doka@lagavulin(admin@bush):~/heat$ openstack stack create -t port.yaml port
+---------------------+--------------------------------------+
| Field | Value |
+---------------------+--------------------------------------+
| id | cfd0148f-3025-423a-8e35-7d4706cd60d2 |
| stack_name | port |
| description | No description |
| creation_time | 2017-08-15T08:46:16Z |
| updated_time | None |
| stack_status | CREATE_IN_PROGRESS |
| stack_status_reason | Stack CREATE started |
+---------------------+--------------------------------------+
doka@lagavulin(admin@bush):~/heat$ openstack stack list
+------------+------------+-----------------+----------------------+--------------+
| ID | Stack Name | Stack Status | Creation Time | Updated Time |
+------------+------------+-----------------+----------------------+--------------+
| cfd0148... | port | CREATE_COMPLETE | 2017-08-15T08:46:16Z | None |
+------------+------------+-----------------+----------------------+--------------+

Seems, something wrong with UPDATE procedure.

Thank you.

[Rabi Mishra (rabi)]

The current behaviour is to keep the already assigned fixed_ips during update[1]. As that is the only thing that's changed in the template above, we're sending an empty dict '{}' and neutron throws an error in that case.

[1] https://github.com/openstack/heat/blob/master/heat/engine/resources/openstack/neutron/port.py#L462

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/493841
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=a7702ff006f2a277de1177942c23bccb9d10d582
Submitter: Jenkins
Branch: master

commit a7702ff006f2a277de1177942c23bccb9d10d582
Author: rabi <email address hidden>
Date: Tue Aug 15 16:57:14 2017 +0530

    Don't call update_port with empty dict

    Change-Id: I98d56645df04a6d0c830428053553b8eeecf5747
    Closes-Bug: #1710833
    Related-Bug: #1538473

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/pike
Review: https://review.openstack.org/504999

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/504999
Committed: https://git.openstack.org/cgit/openstack/heat/commit/?id=dec60a0c9d11ddacdd2c6f7c28f87b52e2a59853
Submitter: Jenkins
Branch: stable/pike

commit dec60a0c9d11ddacdd2c6f7c28f87b52e2a59853
Author: rabi <email address hidden>
Date: Tue Aug 15 16:57:14 2017 +0530

    Don't call update_port with empty dict

    Change-Id: I98d56645df04a6d0c830428053553b8eeecf5747
    Closes-Bug: #1710833
    Related-Bug: #1538473
    (cherry picked from commit a7702ff006f2a277de1177942c23bccb9d10d582)

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/heat 10.0.0.0b1 development milestone.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/heat 9.0.1 release.