migration 30 -> 31 (031_stack_lock) fails

[Robert Collins]

2013-12-17 02:51:05.951 1911 INFO migrate.versioning.api [-] 28 -> 29...
2013-12-17 02:51:06.313 1911 INFO migrate.versioning.api [-] done
2013-12-17 02:51:06.313 1911 INFO migrate.versioning.api [-] 29 -> 30...
2013-12-17 02:51:06.498 1911 INFO migrate.versioning.api [-] done
2013-12-17 02:51:06.498 1911 INFO migrate.versioning.api [-] 30 -> 31...
ERROR: (OperationalError) (1005, "Can't create table 'heat.stack_lock' (errno: 150)") '\nCREATE TABLE stack_lock (\n\tstack_id VARCHAR(36) NOT NULL, \n\tcreated_at DATETIME, \n\tupdated_at DATETIME, \n\tengine_id VARCHAR(36), \n\tPRIMARY KEY (stack_id), \n\tFOREIGN KEY(stack_id) REFERENCES stack (id)\n)ENGINE=InnoDB CHARSET=utf8\n\n' ()

[Robert Collins (lifeless)]

CREATE TABLE stack_lock (
    stack_id VARCHAR(36) NOT NULL,
   created_at DATETIME,
  updated_at DATETIME,
  engine_id VARCHAR(36),
  PRIMARY KEY (stack_id),
  FOREIGN KEY(stack_id) REFERENCES stack (id)
)ENGINE=InnoDB CHARSET=utf8

[Robert Collins (lifeless)]

Based on a hunch from angus I tried dropping the FK and the table was successfullly created.

[Robert Collins (lifeless)]

To revert it:
2047 git revert 6fd92223a9ebc693041616253b0b6b7d93516209
 2048 git revert 23fe65d71494b89059a54611b0c0fef05d181b94
 2049 git revert d7ae961fcae5e0e75bf232606a6878d2414dfeef
 2050 git revert ba57220bb50d993882d540fdad777e6d505570cb

[OpenStack Infra (hudson-openstack)]

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

[Robert Collins (lifeless)]

current schema we have =
http://paste.openstack.org/show/55244/

[Robert Collins (lifeless)]

------------------------
131218 2:11:44 Error in foreign key constraint of table heat/stack_lock:
FOREIGN KEY(stack_id) REFERENCES stack (id) )ENGINE=InnoDB CHARSET=utf8:
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://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

[Angus Salkeld (asalkeld)]

So Robert sent me the sqldump that he saw the failure in. Now I can reproduce the problem.
I can also make the problem go away by changing the charset on his tables:

from:
  ENGINE=InnoDB AUTO_INCREMENT=1166 DEFAULT CHARSET=latin1;
to:
  ENGINE=InnoDB AUTO_INCREMENT=1166 DEFAULT CHARSET=utf8;

Note: we do set the charset in our schema to utf8
https://github.com/openstack/heat/blob/master/heat/db/sqlalchemy/migrate_repo/versions/031_stack_lock.py#L31

[Angus Salkeld (asalkeld)]

nova also sets the utf8 on it's tables:
https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/migrate_repo/versions/170_havana.py#L665

Robert any reason you set latin1?

[Angus Salkeld (asalkeld)]

Have a read here: http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
(grep for latin1)

"
If you're seeing errno 150 when trying to alter a table to add a foreign key, you have to check the properties of the *table* as well as the types of the columns being referenced.

For example, if you're trying to create a foreign key constraint between two varchar(30) columns, but one table is using latin1 encoding and the other table is using utf8 encoding, the ALTER will fail with errno 150 and no clear indication of the above.
"
Note if I grep in Robert's sqldump for latin1:
grep latin1 clean.sql
) ENGINE=InnoDB AUTO_INCREMENT=1166 DEFAULT CHARSET=latin1;
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
) ENGINE=InnoDB AUTO_INCREMENT=1166 DEFAULT CHARSET=latin1;
and utf8
grep utf8 clean.sql
/*!40101 SET NAMES utf8 */;
/*!40101 SET character_set_client = utf8 */;
/*!40101 SET character_set_client = utf8 */;
/*!40101 SET character_set_client = utf8 */;

I am not sure if these comments (character_set_client = utf8) above do anything.

[Robert Collins (lifeless)]

we didn't intentionally set latin1.

[Steve Baker (steve-stevebaker)]

Server was not set to utf8

[Clint Byrum (clint-fewbar)]

So this is still a bug, in that the error message did not lead to user having any idea what the actual problem was.

We can definitely inspect the database and predict this failure.

[OpenStack Infra (hudson-openstack)]

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

[Steve Baker (steve-stevebaker)]

Can this bug be closed?

[Clint Byrum (clint-fewbar)]

Looks like the review got abandoned. It is still a problem.. if somebody runs into the same weirdness we did, they will get the same totally unhelpful errors.

[Pádraig Brady (p-draigbrady)]

Added in oslo incubator now, to be copied back at some stage:
https://review.openstack.org/#/c/64764/

[Pádraig Brady (p-draigbrady)]

Actually this landed in:
https://review.openstack.org/#/c/67759/

which suqsequently triggered bug 1276880