Mysql foreign key failure during db migration from 9 to 10
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Cinder |
Fix Released
|
Critical
|
John Griffith | ||
Fedora |
Fix Released
|
Undecided
|
Bug Description
This happens during the upgrade from grizzly to havana using packages from the Ubuntu Cloud Archive on an Ubuntu 12.04.3 system.
root@openstack01:~# cinder-manage db sync
2013-10-01 23:35:04.977 7923 INFO migrate.
2013-10-01 23:35:05.000 7923 ERROR 010_add_
umn('created_at', DateTime(), table=<transfers>), Column(
d', Boolean(), table=<transfers>), Column('id', String(length=36), table=<transfers>, primary_key=True, nullable=False), Column('volume_id', String(length=36), ForeignKey
('volumes.id'), table=<transfers>), Column(
sh', String(length=255), table=<transfers>), Column(
2013-10-01 23:35:05.003 7923 CRITICAL cinder [-] (OperationalError) (1005, "Can't create table 'cinder.transfers' (errno: 150)") '\nCREATE TABLE transfers (\n\tcreated_at
DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOL, \n\tid VARCHAR(36) NOT NULL, \n\tvolume_id VARCHAR(36), \n\tdisplay_name VARCHAR(255), \n\t
salt VARCHAR(255), \n\tcrypt_hash VARCHAR(255), \n\texpires_at DATETIME, \n\tPRIMARY KEY (id), \n\tCHECK (deleted IN (0, 1)), \n\tFOREIGN KEY(volume_id) REFERENCES volume
s (id)\n)
2013-10-01 23:35:05.003 7923 TRACE cinder Traceback (most recent call last):
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/bin/
2013-10-01 23:35:05.003 7923 TRACE cinder main()
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/bin/
2013-10-01 23:35:05.003 7923 TRACE cinder fn(*fn_args)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/bin/
2013-10-01 23:35:05.003 7923 TRACE cinder return migration.
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder return IMPL.db_
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder return versioning_
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder return _migrate(url, repository, version, upgrade=True, err=err, **opts)
2013-10-01 23:35:05.003 7923 TRACE cinder File "<string>", line 2, in _migrate
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder return f(*a, **kw)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder schema.
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder change.
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder script_func(engine)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
rade
2013-10-01 23:35:05.003 7923 TRACE cinder transfers.create()
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder checkfirst=
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder conn._run_
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder **kwargs)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder return meth(obj, **kw)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder self.connection
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder params)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder compiled
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder context)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder exc_info
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder reraise(
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder context)
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder cursor.
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder self.errorhandl
2013-10-01 23:35:05.003 7923 TRACE cinder File "/usr/lib/
2013-10-01 23:35:05.003 7923 TRACE cinder raise errorclass, errorvalue
2013-10-01 23:35:05.003 7923 TRACE cinder OperationalError: (OperationalError) (1005, "Can't create table 'cinder.transfers' (errno: 150)") '\nCREATE TABLE transfers (\n\
tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOL, \n\tid VARCHAR(36) NOT NULL, \n\tvolume_id VARCHAR(36), \n\tdisplay_name VARCHAR
(255), \n\tsalt VARCHAR(255), \n\tcrypt_hash VARCHAR(255), \n\texpires_at DATETIME, \n\tPRIMARY KEY (id), \n\tCHECK (deleted IN (0, 1)), \n\tFOREIGN KEY(volume_id) REFERE
NCES volumes (id)\n)
2013-10-01 23:35:05.003 7923 TRACE cinder
mysql> SHOW ENGINE INNODB STATUS;
[...]
LATEST FOREIGN KEY ERROR
-------
131001 23:30:06 Error in foreign key constraint of table cinder/transfers:
FOREIGN KEY(volume_id) REFERENCES volumes (id) )ENGINE=InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://
for correct foreign key definition.
[...]
Changed in cinder: | |
status: | In Progress → Confirmed |
importance: | Undecided → Critical |
assignee: | Francois Deppierraz (francois-ctrlaltdel) → John Griffith (john-griffith) |
tags: | added: havana-rc-potential |
Changed in cinder: | |
milestone: | none → havana-rc3 |
Changed in cinder: | |
milestone: | havana-rc3 → 2013.2 |
Changed in fedora: | |
importance: | Unknown → Undecided |
status: | Unknown → Fix Released |
Description of problem:
When upgrading from RHOS 2.1 to 3.0 (I'm using RDO) the cinder db migrate fails to add a new table.
Version-Release number of selected component (if applicable):
upgrade from 2.1 to 3.0
How reproducible:
evertime
Steps to Reproduce:
1. start with a working copy of 2.1
2. stop cinder services
3. yum update
4. update config files so you're using the new config files with the existing ips and users and passwords etc.
5. start services and do an cinder-manage db migrate.
Actual results: volume_ glance_ metadata' (errno: 150)") '\nCREATE TABLE volume_ glance_ metadata (\n\tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOL, \n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tvolume_id VARCHAR(36), \n\tsnapshot_id VARCHAR(36), \n\t`key` VARCHAR(255), \n\tvalue TEXT, \n\tPRIMARY KEY (id), \n\tCHECK (deleted IN (0, 1)), \n\tFOREIGN KEY(volume_id) REFERENCES volumes (id), \n\tFOREIGN KEY(snapshot_id) REFERENCES snapshots (id)\n) ENGINE= InnoDB\ n\n' () main]/Cinder: :Api/Exec[ cinder- manage db_sync]/returns: 2013-05-08 06:09:54 CRITICAL [cinder] (OperationalError) (1005, "Can't create table 'cinder. volume_ glance_ metadata' (errno: 150)") '\nCREATE TABLE volume_ glance_ metadata (\n\tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOL, \n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tvolume_id VARCHAR(36), \n\tsnapshot_id VARCHAR(36), \n\t`key` VARCHAR(255), \n\tvalue TEXT, \n\tPRIMARY KEY (id), \n\tCHECK (deleted IN (0, 1)), \n\tFOREIGN KEY(volume_id) REFERENCES volumes (id), \n\tFOREIGN KEY(snapshot_id) REFERENCES snapshots (id)\n) ENGINE= InnoDB\ n\n' ()
OperationalError: (OperationalError) (1005, "Can't create table 'cinder.
notice: /Stage[
Expected results:
new table created
Additional info: stackoverflow. com/questions/ 1457305/ mysql-creating- tables- with-foreign- keys-giving- errno-150
http://
suggests the relationships are a good starting point for a solution