[image] glance-manage db_sync failed

[zhiwei]

When using MySQL, `glance-manage db_sync` command will be failed because the glance database is not utf-8 charset.

Oslo-DB code require all table using utf-8 charset by default when project use MySQL backend.

[JJ Asghar (d-jj)]

Yep, this is a known issue. I thought we had one in for this, but it dove tails with an upstream glance bug.

You can fix it via a dirty hack though:
# mysql -u root -pilikerandompasswords glance
mysql> alter table migrate_version convert to character set utf8 collate utf8_unicode_ci;
mysql> flush privileges;
mysql> quit

https://bugs.launchpad.net/glance/+bug/1279000

[zhiwei (chenzhiwei)]

But, after this when I bootstrap again, this error will still appear.

I intended to add encoding attribute to openstack-common cookbook.

https://github.com/stackforge/cookbook-openstack-common/blob/stable/icehouse/libraries/database.rb#L90

[Ionuț Arțăriși (mapleoin)]

I think we already have an attribute for setting the mysql encoding, but maybe we're not using it?

https://github.com/stackforge/cookbook-openstack-common/blob/b8633843349809e2675744f211c89bf06d249b7e/attributes/database.rb#L84-L90

[JJ Asghar (d-jj)]

Yep, it seems that it's set up but it's not actually working. Here is a new build:

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> create database blah;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "glance";
+----------------------------+
| default_character_set_name |
+----------------------------+
| latin1 |
+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "blah";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8 |
+----------------------------+
1 row in set (0.00 sec)

mysql>

And as you can see creating a new db it is utf8 but the glance db/migrate_version table is created before the utf8 change takes effect. I attempted to move the order around but i cant see to figure out the correct incantation.

I linked the upstream fix because that seems like the correct way to get this resolved.

[JJ Asghar (d-jj)]

This is the review to fix it.

https://review.openstack.org/#/c/109154/

I'm maybe if we all +1 it? ;)

[zhiwei (chenzhiwei)]

I mean, add `encoding` here like this:

      # create database
      database "create #{db_name} database" do
        provider db_prov
        connection connection_info
        database_name db_name
        encoding 'utf8'
        action :create
      end

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/115229
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-common/commit/?id=f0e55b8742d9285742163d8c2633833dc6f4241c
Submitter: Jenkins
Branch: master

commit f0e55b8742d9285742163d8c2633833dc6f4241c
Author: Chen Zhiwei <email address hidden>
Date: Fri Aug 15 01:00:28 2014 +0000

    Add database charset during create

    When using MySQL, the database charset should be utf-8.

    And the default charset is latin1, which will cause glance db sync
    failed.

    Closes-bug: #1356887
    Change-Id: I50e299410de8068150d1c405994ecb96918fbc7d

[pranesh (pranesh-advankar)]

I am seeing this issue with ubuntu 14.04 image . Even tried the steps mention in comment #1 by "JJ Asghar (d-jj)".

Still able to reproduce this issue.

root@controller:~# dpkg -l | grep -i glance
ii glance 1:2014.1.2-0ubuntu1.1 all OpenStack Image Registry and Delivery Service - Daemons
ii glance-api 1:2014.1.2-0ubuntu1.1 all OpenStack Image Registry and Delivery Service - API
ii glance-common 1:2014.1.2-0ubuntu1.1 all OpenStack Image Registry and Delivery Service - Common
ii glance-registry 1:2014.1.2-0ubuntu1.1 all OpenStack Image Registry and Delivery Service - Registry
ii python-glance 1:2014.1.2-0ubuntu1.1 all OpenStack Image Registry and Delivery Service - Python library
ii python-glanceclient 1:0.12.0-0ubuntu1 all Client library for Openstack glance server.

glance-manage db_sync
2014-09-15 16:35:08.341 8673 CRITICAL glance [-] ValueError: Tables "image_locations,image_members,image_properties,image_tags,images,task_info,tasks" have non utf8 collation, please make sure all tables are CHARSET=utf8

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/114407
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-common/commit/?id=9de44a87fc61ede65ea386b87c3a186058779f37
Submitter: Jenkins
Branch: stable/icehouse

commit 9de44a87fc61ede65ea386b87c3a186058779f37
Author: Chen Zhiwei <email address hidden>
Date: Fri Aug 15 01:00:28 2014 +0000

    Add database charset during create

    When using MySQL, the database charset should be utf-8.

    And the default charset is latin1, which will causes glance db sync
    failed.

    Change-Id: Ic0a0ac33938836c7ec3035e677c837d0306c619e
    Closes-bug: #1356887

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/124441

[OpenStack Infra (hudson-openstack)]

Change abandoned by JJ Asghar (<email address hidden>) on branch: stable/icehouse
Review: https://review.openstack.org/124441
Reason: Yeah this was wrong.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124138
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-compute/commit/?id=75bed0cc831ed0a45b42b5a02e20efdcead04c3f
Submitter: Jenkins
Branch: stable/icehouse

commit 75bed0cc831ed0a45b42b5a02e20efdcead04c3f
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:08:37 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: Ie6452ddfff56c44a76417d3a7d9468a4e8b40de9

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124175
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-object-storage/commit/?id=bb9c607292a80dbd18364be838c2f293302c2abd
Submitter: Jenkins
Branch: stable/icehouse

commit bb9c607292a80dbd18364be838c2f293302c2abd
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 15:22:34 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I1e5dea476e67b26c29fd3851dc3028a5c1679b20

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124142
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-telemetry/commit/?id=3350d2c4508b58909235cdaac9d62b58cc05cfa8
Submitter: Jenkins
Branch: stable/icehouse

commit 3350d2c4508b58909235cdaac9d62b58cc05cfa8
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:21:11 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I20bfbd57b63b8521742990f80f6d86f7acd9a6f9

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124139
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-image/commit/?id=91f74dc8e7f3a6cbb21faec3628d4e4c390761e8
Submitter: Jenkins
Branch: stable/icehouse

commit 91f74dc8e7f3a6cbb21faec3628d4e4c390761e8
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:15:54 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I67b55308cb06432bd54c67d7203a38d6c16312a3

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124140
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-database/commit/?id=0691c9c615f47047bd95f9d53ab1a6b4fc7ae3ec
Submitter: Jenkins
Branch: stable/icehouse

commit 0691c9c615f47047bd95f9d53ab1a6b4fc7ae3ec
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:17:29 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I3302559cf8fe0feae8fc85afca3bb7312d6b8b3a

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124135
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-network/commit/?id=bb72a3e3343c6c33f9834335437f4bc4a7cd0b59
Submitter: Jenkins
Branch: stable/icehouse

commit bb72a3e3343c6c33f9834335437f4bc4a7cd0b59
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:06:08 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I00f7f423a05ee604ddfe964cacf66fdb2caed16f

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124137
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-orchestration/commit/?id=c62732c1c7089fbebb47df29785bd81166806ffd
Submitter: Jenkins
Branch: stable/icehouse

commit c62732c1c7089fbebb47df29785bd81166806ffd
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:07:32 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I3d2431353c3c1f2f7d776d1a5cfb1133989e8048

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124134
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-identity/commit/?id=1ce974dc2542369cae72cf605dc0b12f52a714ca
Submitter: Jenkins
Branch: stable/icehouse

commit 1ce974dc2542369cae72cf605dc0b12f52a714ca
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 14:04:07 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I42b109b36edfd1e4fe103f433916f4ad0fe9d9f4

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124165
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-block-storage/commit/?id=1c6dcc235957caca7e74841b4465bc1ab18bbdc6
Submitter: Jenkins
Branch: stable/icehouse

commit 1c6dcc235957caca7e74841b4465bc1ab18bbdc6
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 15:03:55 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I7a9ef5a3cd6e95759c7aac14028f5dcd85a9c3c3

[OpenStack Infra (hudson-openstack)]

Change abandoned by JJ Asghar (<email address hidden>) on branch: stable/icehouse
Review: https://review.openstack.org/124144
Reason: I hate merge conflicts.

[OpenStack Infra (hudson-openstack)]

Related fix proposed to branch: stable/icehouse
Review: https://review.openstack.org/127293

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/127293
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-ops-database/commit/?id=417d897b793d603997fb8f77e9fc91ea9fcd780b
Submitter: Jenkins
Branch: stable/icehouse

commit 417d897b793d603997fb8f77e9fc91ea9fcd780b
Author: JJ Asghar <email address hidden>
Date: Thu Oct 9 12:02:22 2014 -0500

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Change-Id: I30f187aa8c48bb1e2df50161fe8f5596bcdeb596
    Related-Bug: #1356887

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/124132
Committed: https://git.openstack.org/cgit/stackforge/cookbook-openstack-dashboard/commit/?id=9c74583f0aaf34b66a1b15413a87d7c8cc12c919
Submitter: Jenkins
Branch: stable/icehouse

commit 9c74583f0aaf34b66a1b15413a87d7c8cc12c919
Author: JJ Asghar <email address hidden>
Date: Thu Sep 25 13:59:25 2014 -0500

    Updated for UTF8 issue

    Updated for UTF8 issue

    - This bumps the cookbook for common so the new Berks.lock file can
    pull down the UTF8 glance fix.
    - Updated CHANGELOG.md to reflect this

    Related-Bug: #1356887
    Change-Id: I8ea93f5a81a484a8b2c29b8fa2baf5b7020a060d

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/cookbook-openstack-common ocata-eol release.