Unhandled SqlAchemy exception indexing trait with large text

[George Peristerakis]

When creating a event with a very long trait, I get a exception. After some investigation, I see that in MySql table trait_text, the field "value" is varchar(255)

2015-04-22 20:35:39.014 21943 ERROR ceilometer.event.storage.impl_sqlalchemy [-] Failed to record event: (DataError) (1406, "Data too long for column 'value' at row 1") 'INSERT INTO trait_text (event_id, `key`, value) VALUES (%s, %s, %s)'
((124L, 'exception', "{u'exit_code': 1, u'cmd': u'qemu-img convert -O raw /opt/stack/data/nova/instances/_base/eb9bea75286b52130e1124c93e2f7631a21275ed.part /opt/stack/data/nova/instances/_base/eb9bea75286b52130e1124c93e2f7631a21275ed.conv
erted', u'description': None, u'stderr': u'qemu-img: error while reading sector 0: Input/output error\\n', u'stdout': u''}"), (124L, 'user_id', '9f4e3b67edb84b19abda0bea11227ff7'), (124L, 'service', 'compute'), (124L, 'resource_id', 'c7776
3bc-0b7f-47c1-95d0-35e4f8245047'), (124L, 'tenant_id', '6c3e49c1036045f49aec32a5a4142194'), (124L, 'state', 'building'), (124L, 'host', 'peristeri-devstack.novalocal'), (124L, 'request_id', 'req-2bd43870-0d5a-44e4-b315-a529a8a1853d'), (124
L, 'instance_type', 'm1.nano'))
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy Traceback (most recent call last):
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/opt/stack/ceilometer/ceilometer/event/storage/impl_sqlalchemy.py", line 203, in record_events
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy trait_map[dtype])
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/orm/session.py", line 1021, in execute
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy bind, close_with_result=True).execute(clause, params or {})
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 841, in execute
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy return meth(self, multiparams, params)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy return connection._execute_clauseelement(self, multiparams, params)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 938, in _execute_clauseelement
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy compiled_sql, distilled_params
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1070, in _execute_context
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy context)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/compat/handle_error.py", line 261, in _handle_dbapi_exception
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy e, statement, parameters, cursor, context)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1267, in _handle_dbapi_exception
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy util.raise_from_cause(newraise, exc_info)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy reraise(type(exception), exception, tb=exc_tb)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1040, in _execute_context
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy context)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/sqlalchemy/connectors/mysqldb.py", line 68, in do_executemany
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy rowcount = cursor.executemany(statement, parameters)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 206, in executemany
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy r = r + self.execute(query, a)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 174, in execute
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy self.errorhandler(self, exc, value)
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy raise errorclass, errorvalue
2015-04-22 20:35:39.014 21943 TRACE ceilometer.event.storage.impl_sqlalchemy DBError: (DataError) (1406, "Data too long for column 'value' at row 1") 'INSERT INTO trait_text (event_id, `key`, value) VALUES (%s, %s, %s)' ((124L, 'exception'
, "{u'exit_code': 1, u'cmd': u'qemu-img convert -O raw /opt/stack/data/nova/instances/_base/eb9bea75286b52130e1124c93e2f7631a21275ed.part /opt/stack/data/nova/instances/_base/eb9bea75286b52130e1124c93e2f7631a21275ed.converted', u'descripti
on': None, u'stderr': u'qemu-img: error while reading sector 0: Input/output error\\n', u'stdout': u''}"), (124L, 'user_id', '9f4e3b67edb84b19abda0bea11227ff7'), (124L, 'service', 'compute'), (124L, 'resource_id', 'c77763bc-0b7f-47c1-95d0-
35e4f8245047'), (124L, 'tenant_id', '6c3e49c1036045f49aec32a5a4142194'), (124L, 'state', 'building'), (124L, 'host', 'peristeri-devstack.novalocal'), (124L, 'request_id', 'req-2bd43870-0d5a-44e4-b315-a529a8a1853d'), (124L, 'instance_type',
 'm1.nano'))

[gordon chung (chungg)]

this happens because our model describes trait_text value as sa.Text but our migration is sa.String(255)

[gordon chung (chungg)]

confirmed... this isn't a regression. but we might want to add better support for this in liberty

[Rohit Jaiswal (rohit-jaiswal-3)]

Also, the key in each of the trait_* tables in the models is Integer, whereas the migration describes those as sa.String(255), Shouldn't that be aligned with the model definition?

[gordon chung (chungg)]

@Rohit... yeah that is definitely a bug... can you open a new bug. it should be sa.String(255)

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/178435
Committed: https://git.openstack.org/cgit/openstack/ceilometer/commit/?id=35e574085dd1d9339e1bee770e12665a6b7228d8
Submitter: Jenkins
Branch: master

commit 35e574085dd1d9339e1bee770e12665a6b7228d8
Author: Rohit Jaiswal <email address hidden>
Date: Tue Apr 28 14:52:38 2015 -0700

    TraitText value restricted to max length 255

    Trait_text has a sqlalchemy model definition with
    value column as Text type, but the migrate scripts
    defined the column type as String(255) leading to
    Sqlalchemy exception when indexing trait with large
    text.

    This fixes the TraitText model value as String(255)
    to match the migrate script definition and crops
    the text input to 255 characters.

    Change-Id: I55eee4e9fcdee8d05f3598a9bc0368bc92a9b238
    Closes-Bug: 1447647