SQL Exception querying statistics

[William C. Arnold]

Environment is devstack of 2014/06/20, on Ubuntu 12.04 LTS, including ceilometer and heat, flat dhcp networking. DB is mysql for both ceilometer and the rest of openstack.

I'm testing a heat autoscaling group. The ceilometer alarm evaluator gets statistics for a group, in this case with meter == cpu_util
screen-ceilometer-alarm-evaluator.log contains these errors.

2014-06-20 13:01:30.785 15448 WARNING ceilometerclient.common.http [-] Request returned failure status.
2014-06-20 13:01:30.786 15448 ERROR ceilometer.alarm.evaluator.threshold [-] alarm stats retrieval failed
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold Traceback (most recent call last):
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/ceilometer/ceilometer/alarm/evaluator/threshold.py", line 100, in _statistics
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold period=alarm.rule['period'])
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/v2/statistics.py", line 54, in list
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold q, p))
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/common/base.py", line 58, in _list
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold resp, body = self.api.json_request('GET', url)
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/common/http.py", line 191, in json_request
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold resp, body_iter = self._http_request(url, method, **kwargs)
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/common/http.py", line 174, in _http_request
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold raise exc.from_response(resp, ''.join(body_iter))
2014-06-20 13:01:30.786 15448 TRACE ceilometer.alarm.evaluator.threshold HTTPInternalServerError: HTTPInternalServerError (HTTP 500) ERROR (OperationalError) (1054, "Unknown column 'sample.id' in 'on clause'") 'SELECT\
 meter.unit AS meter_unit, min(sample.timestamp) AS tsmin, max(sample.timestamp) AS tsmax, count(sample.volume) AS count, max(sample.volume) AS max, sum(sample.volume) AS sum, avg(sample.volume) AS avg, min(sample.vol\
ume) AS min \nFROM sample, meter LEFT OUTER JOIN metadata_text AS metadata_text_1 ON sample.id = metadata_text_1.id AND metadata_text_1.meta_key = %s \nWHERE meter.id = sample.meter_id AND meter.name = %s AND sample.t\
imestamp >= %s AND sample.timestamp <= %s AND metadata_text_1.value = %s AND sample.timestamp >= %s AND sample.timestamp < %s GROUP BY meter.unit' ('user_metadata.groupname', 'cpu_util', Decimal('1403283090.451589'), \
Decimal('1403283690.451589'), 'ASCBVM-WSG-ef24cz5hpzsn', Decimal('1403283090.451589'), Decimal('1403283390.451589'))

Also I see a similar error querying on the command line:
barnold@barnubuntu:~$ ceilometer statistics -m cpu_util -q metadata.user_metadata.groupname=ASCBVM-WSG-ef24cz5hpzsn
WARNING (http:173) Request returned failure status.
HTTPInternalServerError (HTTP 500) ERROR (OperationalError) (1054, "Unknown column 'sample.id' in 'on clause'") 'SELECT meter.unit AS meter_unit, min(sample.timestamp) AS tsmin, max(sample.timestamp) AS tsmax, count(sample.volume) AS count, max(sample.volume) AS max, sum(sample.volume) AS sum, avg(sample.volume) AS avg, min(sample.volume) AS min \nFROM sample, meter LEFT OUTER JOIN metadata_text AS metadata_text_1 ON sample.id = metadata_text_1.id AND metadata_text_1.meta_key = %s \nWHERE meter.id = sample.meter_id AND meter.name = %s AND metadata_text_1.value = %s GROUP BY meter.unit' ('user_metadata.groupname', 'cpu_util', 'ASCBVM-WSG-ef24cz5hpzsn')
barnold@barnubuntu:~$

[William C. Arnold (barnold-8)]

A colleague did some digging. This appears to be related to mysql moving closer to the sql 2003 standard.
http://www.wiscorp.com/sql_2003_standard.zip
and (perhaps more useful)
http://downloads.mysql.com/docs/refman-5.5-en.pdf
page 1456
Previously, the ON clause could refer to columns in tables named to its right. Now an ON clause can refer
only to its operands.
Example:
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
Previously, the SELECT statement was legal. Now the statement fails with an Unknown column 'i3'
in 'on clause' error because i3 is a column in t3, which is not an operand of the ON clause. The
statement should be rewritten as follows:
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

Also http://www.wiscorp.com/sql_2003_standard.zip

[Liusheng (liusheng)]

I have tested the sql statements, if change the "FROM sample, meter" to "FROM (sample, meter)", the problem is resolved, I guess this is the reason of this issue (personally).

[Liusheng (liusheng)]

To reproduce this bug, can simply use "ceilometer statistics -m cpu_util -q metadata.abc=abc" command

[OpenStack Infra (hudson-openstack)]

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

[Arthur Svechnikov (asvechnikov)]

I tested your commit with postgresql and got next. Is that related to this bug or should I create a new bug? My environment is Ubuntu 12.04.

2014-06-30 09:04:46.778 27336 WARNING ceilometerclient.common.http [-] Request returned failure status.
2014-06-30 09:04:46.778 27336 ERROR ceilometer.alarm.evaluator.threshold [-] alarm stats retrieval failed
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold Traceback (most recent call last):
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/ceilometer/ceilometer/alarm/evaluator/threshold.py"
, line 100, in _statistics
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold period=alarm.rule['period'])
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/v2/statist
ics.py", line 54, in list
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold q, p))
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/common/base.py", line 58, in _list
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold resp, body = self.api.json_request('GET', url)
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/common/http.py", line 191, in json_request
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold resp, body_iter = self._http_request(url, method, **kwargs)
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold File "/opt/stack/python-ceilometerclient/ceilometerclient/common/http.py", line 174, in _http_request
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold raise exc.from_response(resp, ''.join(body_iter))
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold HTTPInternalServerError: HTTPInternalServerError (HTTP 500) ERROR (ProgrammingError) invalid reference to FROM-clause entry for table "sample"
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold LINE 2: ...FT OUTER JOIN metadata_text AS metadata_text_1 ON sample.id ...
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold ^
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold HINT: There is an entry for table "sample", but it cannot be referenced from this part of the query.
2014-06-30 09:04:46.778 27336 TRACE ceilometer.alarm.evaluator.threshold 'SELECT meter.unit AS meter_unit, min(sample.timestamp) AS tsmin, max(sample.timestamp) AS tsmax, count(sample.volume) AS count, max(sample.volume) AS max, sum(sample.volume) AS sum, avg(sample.volume) AS avg, min(sample.volume) AS min \nFROM sample, meter LEFT OUTER JOIN metadata_text AS metadata_text_1 ON sample.id = metadata_text_1.id AND metadata_text_1.meta_key = %(meta_key_1)s \nWHERE meter.id = sample.meter_id AND meter.name = %(name_1)s AND sample.timestamp >= %(timestamp_1)s AND sample.timestamp <= %(timestamp_2)s AND sample.project_id...

[Liusheng (liusheng)]

Hi Svechnikov Artur, it is strange, the patch has change the SQL statements from "......FROM sample, meter.........." to "......FROM sample JOIN meter..........", but in your error message, there is a "......FROM sample, meter..........", can you run ceilometer statistics -m cpu_util -q metadata.abc=abc" command with postgresql (I use mysql)?

[Arthur Svechnikov (asvechnikov)]

Oh I'm so sorry something was wrong with devstack settings, all is working good now.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/102150
Committed: https://git.openstack.org/cgit/openstack/ceilometer/commit/?id=4f49dd403bd84f9babf1d19ee13c5592342dfc18
Submitter: Jenkins
Branch: master

commit 4f49dd403bd84f9babf1d19ee13c5592342dfc18
Author: liu-sheng <email address hidden>
Date: Tue Jun 24 16:31:53 2014 +0800

    Fix SQL exception getting statitics with metaquery

    In the scenario with mysql as storage backend, when getting statistics
    with metaquery, sqlalchemy will raise an exception of "Unknown column
    'sample.id' in 'on clause'", this is because of a wrong usage of JOIN
    clause.

    Change-Id: I6f140952082ab245f27d576ee9629a086104f8b1
    Closes-bug: #1332673