qos-bandwidth-limit-rule-create failed with internal server error

[wuwoo]

When using following command to create a bandwidth rule:
# neutron qos-bandwidth-limit-rule-create --max-kbps 1000 --max-burst-kbps 100 test-policy

error returned:
Request Failed: internal server error while processing your request.

in /var/log/neutron/server.log, error message contains:
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters [req-ecefbd10-e988-43e1-a556-0f7b8a2b58a7 2eaf7ddac8b94a94ab40fad216341232 e91adc92dfea433f9432857edb8af8cb - - -] DBAPIError exception wrapped from (_mysql_exceptions.ProgrammingError) (1064, "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 ')' at line 3") [SQL: u'SELECT qos_policies.tenant_id AS qos_policies_tenant_id, qos_policies.id AS qos_policies_id, qos_policies.name AS qos_policies_name, qos_policies.description AS qos_policies_description, qos_policies.shared AS qos_policies_shared \nFROM qos_policies \nWHERE qos_policies.name = %s'] [parameters: ([u'test-policy'],)]
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters context)
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters self.errorhandler(self, exc, value)
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters raise errorclass, errorvalue
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters ProgrammingError: (1064, "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 ')' at line 3")
2016-05-26 06:15:32.352 1878 ERROR oslo_db.sqlalchemy.exc_filters
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource [req-ecefbd10-e988-43e1-a556-0f7b8a2b58a7 2eaf7ddac8b94a94ab40fad216341232 e91adc92dfea433f9432857edb8af8cb - - -] index failed
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource Traceback (most recent call last):
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/api/v2/resource.py", line 83, in resource
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource result = method(request=request, **args)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/api/v2/base.py", line 340, in index
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource return self._items(request, True, parent_id)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/api/v2/base.py", line 267, in _items
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource obj_list = obj_getter(request.context, **kwargs)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/db/db_base_plugin_common.py", line 49, in inner_filter
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource result = f(*args, **kwargs)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/db/db_base_plugin_common.py", line 35, in inner
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource result = f(*args, **kwargs)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/services/qos/qos_plugin.py", line 84, in get_policies
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource return policy_object.QosPolicy.get_objects(context, **filters)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/objects/qos/policy.py", line 108, in get_objects
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource **kwargs)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/objects/base.py", line 122, in get_objects
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource db_objs = db_api.get_objects(context, cls.db_model, **kwargs)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib/python2.7/site-packages/neutron/db/api.py", line 92, in get_objects
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource .filter_by(**kwargs)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2588, in all
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource return list(self)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2736, in __iter__
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource return self._execute_and_instances(context)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/query.py", line 2751, in _execute_and_instances
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource result = conn.execute(querycontext.statement, self._params)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource return meth(self, multiparams, params)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource return connection._execute_clauseelement(self, multiparams, params)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource compiled_sql, distilled_params
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource context)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1337, in _handle_dbapi_exception
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource util.raise_from_cause(newraise, exc_info)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource reraise(type(exception), exception, tb=exc_tb)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource context)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource cursor.execute(statement, parameters)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource self.errorhandler(self, exc, value)
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource raise errorclass, errorvalue
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource DBError: (_mysql_exceptions.ProgrammingError) (1064, "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 ')' at line 3") [SQL: u'SELECT qos_policies.tenant_id AS qos_policies_tenant_id, qos_policies.id AS qos_policies_id, qos_policies.name AS qos_policies_name, qos_policies.description AS qos_policies_description, qos_policies.shared AS qos_policies_shared \nFROM qos_policies \nWHERE qos_policies.name = %s'] [parameters: ([u'test-policy'],)]
2016-05-26 06:15:32.353 1878 ERROR neutron.api.v2.resource

Openstack version used is Liberty

[Carl Baldwin (carl-baldwin)]

Assigning for triage

[Miguel Angel Ajo (mangelajo)]

I think this bug is related to:

https://bugs.launchpad.net/neutron/+bug/1589969

[Dongcan Ye (hellochosen)]

@Miguel, Assaf Muller said[1] it fixed in https://bugzilla.redhat.com/show_bug.cgi?id=1266849, but we can't see infos about this bug. Another way is upgrade pymysql, but we can't find the rpm in repo.

[1]https://bugzilla.redhat.com/show_bug.cgi?id=1266381

[Miguel Angel Ajo (mangelajo)]

Liberty was tested upstream with pymysql and not the other driver.

Can you change your connection strings to pymysql and use this package:

http://mirror.centos.org/centos/7/cloud/x86_64/openstack-liberty/common/python2-PyMySQL-0.6.7-2.el7.noarch.rpm

probably available with yum install python2-PyMySQL

[Dongcan Ye (hellochosen)]

Thanks Miguel. It works for me.

[Ihar Hrachyshka (ihar-hrachyshka)]

I believe it was fixed in neutron in Newton with the switch to using _get_collection for get_objects() objects API: https://review.openstack.org/#/c/300055/