kolla-ansible

Illegal mix of collations for operation 'UNION' during Panko project deployment

Bug #1680783 reported by Dai Dang Van on 2017-04-07

This bug affects 4 people

Affects		Status	Importance	Assigned to	Milestone
	kolla-ansible	Fix Released	High	Dai Dang Van	kolla-ansible pike-2 "pike"
	Ocata	Fix Released	High	Dai Dang Van	kolla-ansible 4.0.1 "ocata"

Bug Description

When I run "ceilometer event-list", I got something like below:

DEBUG (client) Request returned failure status: 500
Traceback (most recent call last):
  File "/home/daidv/Envs/ocata/bin/ceilometer", line 11, in <module>
    sys.exit(main())
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/shell.py", line 280, in main
    CeilometerShell().main(args)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/shell.py", line 229, in main
    args.func(client, args)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/v2/shell.py", line 1141, in do_event_list
    limit=args.limit)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/v2/events.py", line 35, in list
    return self._list(options.build_url(path, q, params))
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/common/base.py", line 63, in _list
    resp = self.api.get(url)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/apiclient/client.py", line 355, in get
    return self.client_request("GET", url, **kwargs)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/apiclient/client.py", line 345, in client_request
    self, method, url, **kwargs)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/apiclient/client.py", line 261, in client_request
    method, self.concat_url(endpoint, url), **kwargs)
  File "/home/daidv/Envs/ocata/local/lib/python2.7/site-packages/ceilometerclient/apiclient/client.py", line 201, in request
    raise exceptions.from_response(resp, method, url)
ceilometerclient.apiclient.exceptions.InternalServerError: (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'f71dc98ca4c64912b0f11dd44dd31ad9', u'param_3': 100, u'param_2': 100, u'key_1': 'project_id', u'param_1': 100, u'key_2': 'project_id'}] (HTTP 500) (Request-ID: req-9ed9ec06-ef52-42d7-b313-86863e002e06)

I have used temporary way to fix it via set utf8 collation for all of tables.
For instance: ALTER TABLE trait_text CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Tags:

Revision history for this message

franklin798 (franklin798) wrote on 2017-04-08:

I meet the same issue.and I fix it by @Dai Dang Van's way

Revision history for this message

Dai Dang Van (daikk115) wrote on 2017-04-10:

I think this bug relate to Panko bug.

- https://bugs.launchpad.net/panko/+bug/1672156
- https://bugs.launchpad.net/panko/+bug/1661847

Dai Dang Van (daikk115) on 2017-04-10

Changed in kolla-ansible:
assignee:	nobody → Dai Dang Van (daidv)

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-04-10: Fix proposed to kolla-ansible (master)

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

Changed in kolla-ansible:
status:	New → In Progress

Revision history for this message

Jeffrey Zhang (jeffrey4l) wrote on 2017-04-11:

copy from https://review.openstack.org/455154 comments

after debuging this issue, i found the root cause is:
panko is using CAST with UNION, but the "connection_collation" is still utf8_generic_ci.
The CAST return utf8_generic_ci collation and UNION with other utf8_unicode_ci field. So only Panko raise this error and others not.
There are two fix directs
1. like daidv does in this patch
2. add init-connect = 'SET NAMES utf8 COLLATE utf8_unicode_ci' into mysqld group in my.cnf
both are OK.
@inc for 1), the exsitance table will not be change when u changing the default mysql character and collation. So this patch is OK.
for 2) is setting the default collation connection variable during connection which works too.
For me, i prefer to use daidv's soltuion. due to utf8_generic_ci is the default value in mariadb when using utf8. and utf8_unicode_ci is random choosed by me actually.
So i +2 for this patch.

Changed in kolla-ansible:
importance:	Undecided → High
milestone:	none → pike-1

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-04-11: Fix merged to kolla-ansible (master)

Reviewed: https://review.openstack.org/455154
Committed: https://git.openstack.org/cgit/openstack/kolla-ansible/commit/?id=74a26a91e55c75727d09ac2cebe6d39113aa7bb5
Submitter: Jenkins
Branch: master

commit 74a26a91e55c75727d09ac2cebe6d39113aa7bb5
Author: Dai Dang Van <email address hidden>
Date: Mon Apr 10 14:42:32 2017 +0700

Use utf8_general_ci collation as a default collation

Now, I see mariadb are using utf8_general_ci as a default collation.
- https://mariadb.com/kb/en/mariadb/supported-character-sets-and-collations/

    This mean all of Devstack database will be created with utf8_general_ci collation,
    so may be, one service/project can be deployed successfully via Devstack
    but will be fail with Kolla deployment.

Therefore, we should use above default collation for Kolla-ansible.

Change-Id: Icbb6c15f536fc6986816c58f4fd68bfb95813e46
Closes-Bug: 1680783

Changed in kolla-ansible:
status:	In Progress → Fix Released

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-04-11: Fix proposed to kolla-ansible (stable/ocata)

Fix proposed to branch: stable/ocata
Review: https://review.openstack.org/455747

Revision history for this message

OpenStack Infra (hudson-openstack) wrote on 2017-04-12: Fix merged to kolla-ansible (stable/ocata)

Reviewed: https://review.openstack.org/455747
Committed: https://git.openstack.org/cgit/openstack/kolla-ansible/commit/?id=b7c06f053541f36410d466108c80e11a1a41a81a
Submitter: Jenkins
Branch: stable/ocata

commit b7c06f053541f36410d466108c80e11a1a41a81a
Author: Dai Dang Van <email address hidden>
Date: Mon Apr 10 14:42:32 2017 +0700

Use utf8_general_ci collation as a default collation

Now, I see mariadb are using utf8_general_ci as a default collation.
- https://mariadb.com/kb/en/mariadb/supported-character-sets-and-collations/

    This mean all of Devstack database will be created with utf8_general_ci
    collation, so may be, one service/project can be deployed successfully
    via Devstack but will be fail with Kolla deployment.

Therefore, we should use above default collation for Kolla-ansible.

    Change-Id: Icbb6c15f536fc6986816c58f4fd68bfb95813e46
    Closes-Bug: 1680783
    (cherry picked from commit 74a26a91e55c75727d09ac2cebe6d39113aa7bb5)