Open Stack

On Mon, Aug 3, 2015 at 3:50 PM, David Medberry <openstack at medberry.net> wrote:
> The column type for the foreign keys don't match EXACTLY. (Strings). They
> have to be exact in value.... and the collation order must be the same.
>
> Two issues: a character set (UTFetc) and the collation order must be
> IDENTICAL.
> It will use the database default if you don't SET it in the new column....

Spot on thanks!

mysql> ALTER DATABASE cinder CHARACTER SET utf8 COLLATE utf8_general_ci;

and all's well.

longer story I needed to change the database names & when I created
the new DB I didn't set character set or collation so they ended up
wrong, though the data was right in existing tables when I restored
those.  So the migration, assuming sane defaults, created (well was
trying to create) the new table as latin1 rather than utf-8.

Thanks again,
-Jon

> (We've run into this at Time Warner Cable using puppet.)
>
> The work around is to turn the collation order back.
>
> IRC help: look for "clayton" or "med_" in freenode.
>
> On Mon, Aug 3, 2015 at 12:43 PM, Jonathan Proulx <jon at jonproulx.com> wrote:
>>
>> Hi All,
>>
>> Attempting to upgrade from juno -> kilo I'm getting DB migration
>> errors from cinder:
>>
>> 2015-08-03 14:31:19.612 16831 ERROR 032_add_volume_type_projects [-]
>> Table |Table('volume_type_projects',
>> MetaData(bind=Engine(mysql://cinder:***@10.0.128.15/cinder?charset=utf8)),
>> Column('id', Integer(), table=<volume_type_projects>,
>> primary_key=True, nullable=False), Column('created_at', DateTime(),
>> table=<volume_type_projects>), Column('updated_at', DateTime(),
>> table=<volume_type_projects>), Column('deleted_at', DateTime(),
>> table=<volume_type_projects>), Column('volume_type_id',
>> String(length=36), ForeignKey('volume_types.id'),
>> table=<volume_type_projects>), Column('project_id',
>> String(length=255), table=<volume_type_projects>), Column('deleted',
>> Boolean(), table=<volume_type_projects>), schema=None)| not created!
>> 2015-08-03 14:31:19.612 16831 CRITICAL cinder [-] OperationalError:
>> (OperationalError) (1005, "Can't create table
>> 'cinder.volume_type_projects' (errno: 150)") '\nCREATE TABLE
>> volume_type_projects (\n\tid INTEGER NOT NULL AUTO_INCREMENT,
>> \n\tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at
>> DATETIME, \n\tvolume_type_id VARCHAR(36), \n\tproject_id VARCHAR(255),
>> \n\tdeleted BOOL, \n\tPRIMARY KEY (id), \n\tUNIQUE (volume_type_id,
>> project_id, deleted), \n\tFOREIGN KEY(volume_type_id) REFERENCES
>> volume_types (id), \n\tCHECK (deleted IN (0, 1))\n)ENGINE=InnoDB\n\n'
>> ()
>>
>> This looks like a foreign constraint error if I'm reading it
>> correctly, but there's only one foreign Key that I see
>> 'ForeignKey('volume_types.id')' and that certainly exists:
>>
>> mysql> select id from volume_types;
>> +--------------------------------------+
>> | id                                   |
>> +--------------------------------------+
>> | 349f7672-f958-41bf-9742-50eff62978e9 |
>> | 4f8a731c-0c46-44bd-8412-42ff51f47661 |
>> | ce9b5285-109d-4a70-88a5-58c9a9443f06 |
>> +--------------------------------------+
>> 3 rows in set (0.00 sec)
>>
>> full trace output from 'cinder-manage db sync' is at
>> http://paste.openstack.org/show/406782/
>>
>> has anyone else seen this, know of a work around, or existing bug report?
>>
>>
>> Thanks,
>> -Jon
>>
>> _______________________________________________
>> Mailing list:
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack
>> Post to     : openstack at lists.openstack.org
>> Unsubscribe :
>> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack
>
>