Open Stack

Hi:

I found that the newest codes have solved these two problems, they were caused by sql errors, I only need to merge right codes to my version.

For ceilometer resource-list:
H version:
agg_subquery = session.query(
            func.max(Meter.id).label("max_id"),
            ts_subquery
        ).filter(
            Meter.resource_id == ts_subquery.c.resource_id,
            Meter.timestamp == ts_subquery.c.max_ts
        ).group_by(Meter.resource_id).subquery()
Newest codes:
agg_subquery = session.query(
            func.max(models.Sample.id).label("max_id"),
            ts_subquery
        ).filter(
            models.Sample.resource_id == ts_subquery.c.resource_id,
            models.Sample.timestamp == ts_subquery.c.max_ts
        ).group_by(
            ts_subquery.c.resource_id,
            ts_subquery.c.max_ts,
            ts_subquery.c.min_ts
        ).subquery()
I used the new 'group_by' to replace the old, then 'resource-list' ran normal.
For ceilometer statistics -m disk.read.bytes:
There are a bug about it in https://bugs.launchpad.net/bugs/1256318.

Thanks



发件人: jordan tardif [mailto:jordan.tardif at gmail.com]
发送时间: 2014年4月10日 15:10
收件人: Yuanjing (D)
主题: Re: [Openstack-operators] [openstack][ceilometer] Some ceilometer commands ran into failed when using postgres as storage backend

Have you tried running ceilometer-dbsync?

On Wed, Apr 9, 2014 at 6:21 AM, Yuanjing (D) <yj.yuan at huawei.com<mailto:yj.yuan at huawei.com>> wrote:
Hi

I deployed a ceilometer environment with storage backend of postgresql.

I got into trouble when running the following commands:


(1)     ceilometer resource-list
Logs:
-->

2014-04-09 10:44:05.578 8242 ERROR wsme.api [-] Server-side error: "(ProgrammingError) column "meter.counter_unit" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT meter.counter_unit AS unit, min(meter.timestamp) AS t...
               ^
 'SELECT meter.counter_unit AS unit, min(meter.timestamp) AS tsmin, max(meter.timestamp) AS tsmax, avg(meter.counter_volume) AS avg, sum(meter.counter_volume) AS sum, min(meter.counter_volume) AS min, max(meter.counter_volume) AS max, count(meter.counter_volume) AS count \nFROM meter \nWHERE meter.counter_name = %(counter_name_1)s AND meter.timestamp >= %(timestamp_1)s AND meter.timestamp <= %(timestamp_2)s AND meter.timestamp >= %(timestamp_3)s AND meter.timestamp < %(timestamp_4)s' {'counter_name_1': u'cpu_util', 'timestamp_4': datetime.datetime(2014, 4, 9, 2, 43, 5, 556125), 'timestamp_2': datetime.datetime(2014, 4, 9, 2, 44, 5, 556125), 'timestamp_3': datetime.datetime(2014, 4, 9, 2, 42, 5, 556125), 'timestamp_1': datetime.datetime(2014, 4, 9, 2, 42, 5, 556125)}". Detail:
Traceback (most recent call last):

  File "/usr/lib/python2.6/site-packages/wsmeext/pecan.py", line 72, in callfunction
    result = f(self, *args, **kwargs)

  File "/usr/lib/python2.6/site-packages/ceilometer/api/controllers/v2.py", line 813, in statistics
    for c in computed]

  File "/usr/lib/python2.6/site-packages/ceilometer/storage/impl_sqlalchemy.py", line 569, in get_meter_statistics
    for r in q.all():

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2115, in all
    return list(self)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2227, in __iter__
    return self._execute_and_instances(context)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1449, in execute
    params)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1851, in _handle_dbapi_exception
    None, sys.exc_info()[2]

ProgrammingError: (ProgrammingError) column "meter.counter_unit" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT meter.counter_unit AS unit, min(meter.timestamp) AS t...
               ^
 'SELECT meter.counter_unit AS unit, min(meter.timestamp) AS tsmin, max(meter.timestamp) AS tsmax, avg(meter.counter_volume) AS avg, sum(meter.counter_volume) AS sum, min(meter.counter_volume) AS min, max(meter.counter_volume) AS max, count(meter.counter_volume) AS count \nFROM meter \nWHERE meter.counter_name = %(counter_name_1)s AND meter.timestamp >= %(timestamp_1)s AND meter.timestamp <= %(timestamp_2)s AND meter.timestamp >= %(timestamp_3)s AND meter.timestamp < %(timestamp_4)s' {'counter_name_1': u'cpu_util', 'timestamp_4': datetime.datetime(2014, 4, 9, 2, 43, 5, 556125), 'timestamp_2': datetime.datetime(2014, 4, 9, 2, 44, 5, 556125), 'timestamp_3': datetime.datetime(2014, 4, 9, 2, 42, 5, 556125), 'timestamp_1': datetime.datetime(2014, 4, 9, 2, 42, 5, 556125)}
<---


(2)      ceilometer statistics -m disk.read.bytes
Logs:
--->
LINE 1: SELECT meter.counter_unit AS unit, min(meter.timestamp) AS t...
               ^
'SELECT meter.counter_unit AS unit, min(meter.timestamp) AS tsmin, max(meter.timestamp) AS tsmax, avg(meter.counter_volume) AS avg, sum(meter.counter_volume) AS sum, min(meter.counter_volume) AS min, max(me
ter.counter_volume) AS max, count(meter.counter_volume) AS count \nFROM meter \nWHERE meter.counter_name = %(counter_name_1)s AND meter.timestamp >= %(timestamp_1)s AND meter.timestamp <= %(timestamp_2)s AND
meter.project_id = %(project_id_1)s' {'counter_name_1': u'disk.read.bytes', 'project_id_1': u'6b3e95ea39f74b2897e6581394fe21ed', 'timestamp_2': datetime.datetime(2014, 4, 9, 19, 26, 28, 119292), 'timestamp_
1': datetime.datetime(2014, 3, 10, 19, 26, 28, 119273)}". Detail:
Traceback (most recent call last):

  File "/usr/lib/python2.6/site-packages/wsmeext/pecan.py", line 72, in callfunction
    result = f(self, *args, **kwargs)

  File "/usr/lib/python2.6/site-packages/ceilometer/api/controllers/v2.py", line 813, in statistics
    for c in computed]

  File "/usr/lib/python2.6/site-packages/ceilometer/storage/impl_sqlalchemy.py", line 547, in get_meter_statistics
    for res in self._make_stats_query(sample_filter, groupby):

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2227, in __iter__
    return self._execute_and_instances(context)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2242, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1449, in execute
    params)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)

  File "/usr/lib64/python2.6/site-packages/SQLAlchemy-0.7.8-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1851, in _handle_dbapi_exception
    None, sys.exc_info()[2]

ProgrammingError: (ProgrammingError) column "meter.counter_unit" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT meter.counter_unit AS unit, min(meter.timestamp) AS t...
               ^
'SELECT meter.counter_unit AS unit, min(meter.timestamp) AS tsmin, max(meter.timestamp) AS tsmax, avg(meter.counter_volume) AS avg, sum(meter.counter_volume) AS sum, min(meter.counter_volume) AS min, max(me
ter.counter_volume) AS max, count(meter.counter_volume) AS count \nFROM meter \nWHERE meter.counter_name = %(counter_name_1)s AND meter.timestamp >= %(timestamp_1)s AND meter.timestamp <= %(timestamp_2)s AND
meter.project_id = %(project_id_1)s' {'counter_name_1': u'disk.read.bytes', 'project_id_1': u'6b3e95ea39f74b2897e6581394fe21ed', 'timestamp_2': datetime.datetime(2014, 4, 9, 19, 26, 28, 119292), 'timestamp_
1': datetime.datetime(2014, 3, 10, 19, 26, 28, 119273)}
<---

I tested SQL above in postgres command model and found the above errors were caused by SQL grammatical mistakes.
For problem one, I added anon_2.resource_id AS resource_id, anon_2.max_ts AS max_ts, anon_2.min_ts AS min_ts to 'group by' segment, then it ran normal.
For problem two, I added meter.counter_unit to 'group by' segment, then it ran normal.

Can somebody give help about this problem, it was the bug of ceilometer or some mistake configuration?

Thanks




_______________________________________________
OpenStack-operators mailing list
OpenStack-operators at lists.openstack.org<mailto:OpenStack-operators at lists.openstack.org>
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-operators

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-operators/attachments/20140410/b3648f1f/attachment-0001.html>