On Mon, Aug 3, 2015 at 3:50 PM, David Medberry <[email protected]> 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 <[email protected]> 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 : [email protected] >> Unsubscribe : >> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack > > _______________________________________________ Mailing list: http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack Post to : [email protected] Unsubscribe : http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack
