db sync fails to create cinder.volumes

[Jimmy McCrory]

New deployments of cinder from the master branch fail to create the volumes table when running `cinder-manage db sync` with the error "Foreign key constraint is incorrectly formed"

http://logs.openstack.org/69/331269/1/check/gate-openstack-ansible-os_cinder-ansible-func-ubuntu-trusty/0c6977d/console.html.gz#_2016-06-18_04_50_32_089147

It looks like the consistencygroups table is initially created with the utf8 character set, while the volumes table is not.

https://github.com/openstack/cinder/blob/master/cinder/db/sqlalchemy/migrate_repo/versions/046_cinder_init.py#L70-L71
https://github.com/openstack/cinder/blob/master/cinder/db/sqlalchemy/migrate_repo/versions/046_cinder_init.py#L130

Can't create table `cinder`.`volumes` (errno: 150 "Foreign key constraint is incorrectly formed")') [SQL: u'
CREATE TABLE volumes (
 created_at DATETIME,
 updated_at DATETIME,
 deleted_at DATETIME,
 deleted BOOL,
 id VARCHAR(36) NOT NULL,
 ec2_id VARCHAR(255),
 user_id VARCHAR(255),
 project_id VARCHAR(255),
 host VARCHAR(255),
 size INTEGER,
 availability_zone VARCHAR(255),
 status VARCHAR(255),
 attach_status VARCHAR(255),
 scheduled_at DATETIME,
 launched_at DATETIME,
 terminated_at DATETIME,
 display_name VARCHAR(255),
 display_description VARCHAR(255),
 provider_location VARCHAR(256),
 provider_auth VARCHAR(256),
 snapshot_id VARCHAR(36),
 volume_type_id VARCHAR(36),
 source_volid VARCHAR(36),
 bootable BOOL,
 provider_geometry VARCHAR(255),
 _name_id VARCHAR(36),
 encryption_key_id VARCHAR(36),
 migration_status VARCHAR(255),
 replication_status VARCHAR(255),
 replication_extended_status VARCHAR(255),
 replication_driver_data VARCHAR(255),
 consistencygroup_id VARCHAR(36),
 provider_id VARCHAR(255),
 multiattach BOOL,
 PRIMARY KEY (id),
 CHECK (deleted IN (0, 1)),
 CHECK (bootable IN (0, 1)),
 FOREIGN KEY(consistencygroup_id) REFERENCES consistencygroups (id),
 CHECK (multiattach IN (0, 1))
)ENGINE=InnoDB

# mysql --version
mysql Ver 15.1 Distrib 10.0.25-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[Cao ShuFeng (caosf-fnst)]

Hi, Jimmy:
    This may caused because you didn't create database cinder with charset utf8.

    In my opinion, the reproduce steps of this bug should be:
    1. drop database cinder if exist.
       mysql -uroot -pxxxxxxxx -h127.0.0.1 -e 'DROP DATABASE IF EXISTS cinder;'
    2. create database cinder with non-utf-8 charset(gbk for example).
       mysql -uroot -pxxxxxxxx -h127.0.0.1 -e 'CREATE DATABASE cinder CHARACTER SET gbk;
    3. sync the cinder tables.
       cinder-manage db sync

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/331533
Committed: https://git.openstack.org/cgit/openstack/cinder/commit/?id=dfc69e8e9ac7311aaf2ab918780f940da603a19e
Submitter: Jenkins
Branch: master

commit dfc69e8e9ac7311aaf2ab918780f940da603a19e
Author: Cao ShuFeng <email address hidden>
Date: Mon Jun 20 04:33:53 2016 -0400

    use utf8 as default charset for all tables in mysql db

    When we use "cinder-manager db sync" to create tables in mysql db,
    some tables' charset is set to utf8 but some not. This will cause
    db error when a foreign key is referenced to column in different
    charset. This change set all tables in cinder use utf8 as default
    charset.

    Closes-bug: #1594195

    Change-Id: I15a864dd1ad18aa1b3e3ff7fe511405d62a119cc

[Jimmy McCrory (jimmy-mccrory)]

Thanks, Cao.

It looks like openstack-ansible was configuring a different default collation than the default of the utf8 character set.

I created https://review.openstack.org/#/c/331786/ to address that.

Like you mentioned, this still could be an issue for anyone with a default charset other than utf8.

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/cinder 9.0.0.0b2 development milestone.