Can't create table 'heat.stack_lock'

[Derek Higgins]

toci is currently failing for tripleo

the the controller node is first booted we run the command

/opt/stack/venvs/heat/bin/heat-manage db_sync

which outputs the error

Nov 13 09:57:02 localhost os-collect-config[1227]: 2013-11-13 09:57:02.883 5503 INFO migrate.versioning.api [-] 27 -> 28...
Nov 13 09:57:03 localhost os-collect-config[1227]: 2013-11-13 09:57:03.092 5503 INFO migrate.versioning.api [-] done
Nov 13 09:57:03 localhost os-collect-config[1227]: 2013-11-13 09:57:03.093 5503 INFO migrate.versioning.api [-] 28 -> 29...
Nov 13 09:57:03 localhost os-collect-config[1227]: 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(64), \n\tPRIMARY KEY (stack_id), \n\tFOREIGN KEY(stack_id) REFERENCES stack (id)\n)\n\n' ()

looks like migration 29 was added recently

https://review.openstack.org/#/c/49440/

[Clint Byrum (clint-fewbar)]

Looks like we broke db syncing. Please fix ASAP!

[Angus Salkeld (asalkeld)]

maybe separate the primary key and the foreign key? http://paste.openstack.org/show/52371/

[Angus Salkeld (asalkeld)]

     stack_lock = sqlalchemy.Table(
         'stack_lock', meta,
- sqlalchemy.Column('stack_id', sqlalchemy.String(length=36),
- sqlalchemy.ForeignKey('stack.id'),
+ sqlalchemy.Column('id',
+ sqlalchemy.Integer,
                           primary_key=True,
                           nullable=False),
         sqlalchemy.Column('created_at', sqlalchemy.DateTime),
         sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
- sqlalchemy.Column('engine_id', sqlalchemy.String(length=64))
+ sqlalchemy.Column('engine_id', sqlalchemy.String(length=64)),
+ sqlalchemy.Column('stack_id',
+ sqlalchemy.String(length=36),
+ sqlalchemy.ForeignKey('stack.id'),
+ nullable=False),
+ mysql_engine='InnoDB',
+ mysql_charset='utf8'
     )
     sqlalchemy.Table('stack', meta, autoload=True)
     stack_lock.create()

Also need the "mysql_" options too.

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/56199
Committed: http://github.com/openstack/heat/commit/4ed487bef474c2ae9e8d38192b8f712f155456e4
Submitter: Jenkins
Branch: master

commit 4ed487bef474c2ae9e8d38192b8f712f155456e4
Author: Angus Salkeld <email address hidden>
Date: Wed Nov 13 22:59:34 2013 +1100

    Revert "Implement stack-locking for multi-engine support"

    This is a short term backout until it gets fixed.

    This reverts commit c30530f12663302869a8f2edf979e8a8bd33ed2d.
    Closes-bug: #1250797

    Change-Id: Ide1dc4a9d1469dee033e8bc08d8ab4da96f79fc0

[Jason Dunsmore (jasondunsmore)]

Thanks for looking into this Angus. Adding the "mysql_" options fixed the problem without having to change the foreign key:

        mysql_engine='InnoDB',
        mysql_charset='utf8'

I will fix this in the next review (and will try to be more careful about testing these scripts).

[Steve Baker (steve-stevebaker)]

Why did this make it through the gate without failing?

[Clint Byrum (clint-fewbar)]

BTW this made it through the gate, I'm guessing, because the mysql server that devstack installs is different than the one that TOCI uses.

2013-11-13 03:15:49 2013-11-13 03:15:49.068 24997 DEBUG migrate.versioning.script.base [-] Loading script /opt/stack/new/heat/heat/db/sqlalchemy/migrate_repo/versions/029_stack_lock.py... __init__ /usr/lib/python2.7/dist-packages/migrate/versioning/script/base.py:27

This is why we do integration testing for the actual configurations we want I guess. :)

[Clint Byrum (clint-fewbar)]

Actually I wonder if we passed the gate because of https://bugs.launchpad.net/bugs/1241826