panko event list got following sql error

[Jeffrey Zhang]

Get following error message when using `panko even list`. More logs please check the attached file.

DBError: (pymysql.err.InternalError) (1271, u"Illegal mix of collations for operation 'UNION'") [SQL: u'SELECT anon_1.trait_datetime_event_id AS anon_1_trait_datetime_event_id, anon_1.trait_datetime_key AS anon_1_trait_datetime_key, anon_1.trait_datetime_value AS anon_1_trait_datetime_value, anon_1.anon_2 AS anon_1_anon_2, anon_1.anon_3 AS anon_1_anon_3, anon_1.anon_4 AS anon_1_anon_4 \nFROM (SELECT trait_datetime.event_id AS trait_datetime_event_id, trait_datetime.`key` AS trait_datetime_key, trait_datetime.value AS trait_datetime_value, CAST(NULL AS SIGNED INTEGER) AS anon_2, NULL AS anon_3, CAST(NULL AS CHAR(255)) AS anon_4 \nFROM trait_datetime \nWHERE EXISTS (SELECT * \nFROM (SELECT event.id AS id \nFROM event INNER JOIN event_type ON event_type.id = event.event_type_id \nWHERE EXISTS (SELECT * \nFROM (SELECT anon_7.trait_text_event_id AS trait_text_event_id \nFROM (SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text \nWHERE NOT (EXISTS (SELECT * \nFROM (SELECT trait_text.event_id AS event_id \nFROM trait_text \nWHERE trait_text.`key` = %(key_1)s) AS anon_8 \nWHERE trait_text.event_id = anon_8.event_id)) UNION SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text, event \nWHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s AND event.id = trait_text.event_id) AS anon_7) AS anon_6 \nWHERE event.id = anon_6.trait_text_event_id) ORDER BY event.`generated` ASC, event.message_id ASC \n LIMIT %(param_1)s) AS anon_5 \nWHERE trait_datetime.event_id = anon_5.id) UNION ALL SELECT trait_int.event_id AS trait_int_event_id, trait_int.`key` AS trait_int_key, NULL AS anon_9, trait_int.value AS trait_int_value, NULL AS anon_10, NULL AS anon_11 \nFROM trait_int \nWHERE EXISTS (SELECT * \nFROM (SELECT event.id AS id \nFROM event INNER JOIN event_type ON event_type.id = event.event_type_id \nWHERE EXISTS (SELECT * \nFROM (SELECT anon_7.trait_text_event_id AS trait_text_event_id \nFROM (SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text \nWHERE NOT (EXISTS (SELECT * \nFROM (SELECT trait_text.event_id AS event_id \nFROM trait_text \nWHERE trait_text.`key` = %(key_1)s) AS anon_8 \nWHERE trait_text.event_id = anon_8.event_id)) UNION SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text, event \nWHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s AND event.id = trait_text.event_id) AS anon_7) AS anon_6 \nWHERE event.id = anon_6.trait_text_event_id) ORDER BY event.`generated` ASC, event.message_id ASC \n LIMIT %(param_2)s) AS anon_12 \nWHERE trait_int.event_id = anon_12.id) UNION ALL SELECT trait_float.event_id AS trait_float_event_id, trait_float.`key` AS trait_float_key, NULL AS anon_13, NULL AS anon_14, trait_float.value AS trait_float_value, NULL AS anon_15 \nFROM trait_float \nWHERE EXISTS (SELECT * \nFROM (SELECT event.id AS id \nFROM event INNER JOIN event_type ON event_type.id = event.event_type_id \nWHERE EXISTS (SELECT * \nFROM (SELECT anon_7.trait_text_event_id AS trait_text_event_id \nFROM (SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text \nWHERE NOT (EXISTS (SELECT * \nFROM (SELECT trait_text.event_id AS event_id \nFROM trait_text \nWHERE trait_text.`key` = %(key_1)s) AS anon_8 \nWHERE trait_text.event_id = anon_8.event_id)) UNION SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text, event \nWHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s AND event.id = trait_text.event_id) AS anon_7) AS anon_6 \nWHERE event.id = anon_6.trait_text_event_id) ORDER BY event.`generated` ASC, event.message_id ASC \n LIMIT %(param_3)s) AS anon_16 \nWHERE trait_float.event_id = anon_16.id) UNION ALL SELECT trait_text.event_id AS trait_text_event_id, trait_text.`key` AS trait_text_key, NULL AS anon_17, NULL AS anon_18, NULL AS anon_19, trait_text.value AS trait_text_value \nFROM trait_text \nWHERE EXISTS (SELECT * \nFROM (SELECT event.id AS id \nFROM event INNER JOIN event_type ON event_type.id = event.event_type_id \nWHERE EXISTS (SELECT * \nFROM (SELECT anon_7.trait_text_event_id AS trait_text_event_id \nFROM (SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text \nWHERE NOT (EXISTS (SELECT * \nFROM (SELECT trait_text.event_id AS event_id \nFROM trait_text \nWHERE trait_text.`key` = %(key_1)s) AS anon_8 \nWHERE trait_text.event_id = anon_8.event_id)) UNION SELECT trait_text.event_id AS trait_text_event_id \nFROM trait_text, event \nWHERE trait_text.`key` = %(key_2)s AND trait_text.value = %(value_1)s AND event.id = trait_text.event_id) AS anon_7) AS anon_6 \nWHERE event.id = anon_6.trait_text_event_id) ORDER BY event.`generated` ASC, event.message_id ASC \n LIMIT %(param_4)s) AS anon_20 \nWHERE trait_text.event_id = anon_20.id)) AS anon_1 ORDER BY anon_1.trait_datetime_key'] [parameters: {u'param_4': 100, u'value_1': u'c832fd4f91c44f159d985085ecdd5772', u'param_3': 100, u'param_2': 100, u'key_1': 'project_id', u'param_1': 100, u'key_2': 'project_id'}]

[Jeffrey Zhang (jeffrey4l)]

after delete some char setting in mariadb, it works.

the attachment is the OK and error sql.

[Jeffrey Zhang (jeffrey4l)]

sql when issue happen.

[Dai Dang Van (daikk115)]

This is mine when using utf8_general_ci, it's working well.

Note:
We don't have any table or column level collation setting.
In server level, I have set (via Kolla) like bellow:

[mysqld]
collation-server = utf8_general_ci

[Jeffrey Zhang (jeffrey4l)]

This is cause by bad mysql configuration. So it is a bug in kolla-ansible actually.