'caravel db upgrade' hangs with Postgres RDS for metadata DB #238

ptierney · 2016-04-02T00:53:48Z

My caravel_config.py file sets the database to an AWS Postgres RDS, though when I try running 'caravel db upgrade', the script hangs at 'Running upgrade...' See image:

I've successfully set up Caravel on my machine by using a sqlite3 database for the metadata database, and can successfully use the RDS as a database source on the website.

Doing a bit of digging, I found that the PG server is locking on CREATE TABLE clusters. This persists even after I restart the database server, and no other users are connecting.

greens231 · 2016-04-02T12:37:39Z

+1

adamflanagan · 2016-04-02T14:36:35Z

I'm seeing this with a local Postgres instance as well. Brand new database with no connections except Caravel and it's locking on the CREATE TABLE clusters statement.

greens231 · 2016-04-02T14:50:31Z

changing the query from
create table clusters (created_by_fk integer, changed_by_fk integer, foreign key(changed_by_fk) references ab_user (id), foreign key (created_by_fk) references ab_user (id));
to
create table clusters (created_by_fk integer references ab_user(id), changed_by_fk integer references ab_user(id));
solves the problem

the query is being generated here (https://github.com/airbnb/caravel/blob/1b4e750b2aa111445703dca07fe929f7ffde38c9/caravel/migrations/versions/4e6a06bad7a8_init.py) by alembic

adamflanagan · 2016-04-03T10:03:35Z

A bit more info after investigating further:

When the migrations hang there are 2 open transactions from caravel: the create table clusters ... statement and another query:

SELECT ab_permission_view.id AS ab_permission_view_id, ab_permission_view.permission_id AS ab_permission_view_permission_id, ab_permission_view.view_menu_id AS ab_permission_view_view_menu_id 
FROM ab_permission_view 
WHERE 27 = ab_permission_view.view_menu_id AND 13 = ab_permission_view.permission_id 
LIMIT 1;

This second query is in a idle in transaction state and would appear to be holding a lock which blocks the other query.

It looks like this is coming from some part of Flask-AppBuilder but I'm not familiar enough with it to say exactly where from.

I was able to run the migrations successfully by terminating this other transaction. Once that was terminated the rest of the migrations ran successfully.

knoguchi · 2016-04-04T06:21:17Z

Following @adamflanagan suggestion here is what I did:

run caravel db upgrade
open another terminal, run psql as postgres user.
select * from pg_stat_activity where datname ='mydb' Find the PID of the select statement
select pg_terminate_backend(PID)

The migration finished without error.

greens231 · 2016-04-04T12:49:04Z

i edited the upgrade method in https://github.com/airbnb/caravel/blob/master/caravel/migrations/versions/4e6a06bad7a8_init.py and everything worked fine

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('clusters',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('cluster_name', sa.String(length=250), nullable=True),
    sa.Column('coordinator_host', sa.String(length=256), nullable=True),
    sa.Column('coordinator_port', sa.Integer(), nullable=True),
    sa.Column('coordinator_endpoint', sa.String(length=256), nullable=True),
    sa.Column('broker_host', sa.String(length=256), nullable=True),
    sa.Column('broker_port', sa.Integer(), nullable=True),
    sa.Column('broker_endpoint', sa.String(length=256), nullable=True),
    sa.Column('metadata_last_refreshed', sa.DateTime(), nullable=True),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('cluster_name')
    )
    op.create_table('dashboards',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('dashboard_title', sa.String(length=500), nullable=True),
    sa.Column('position_json', sa.Text(), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('dbs',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('database_name', sa.String(length=250), nullable=True),
    sa.Column('sqlalchemy_uri', sa.String(length=1024), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('database_name')
    )
    op.create_table('datasources',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('datasource_name', sa.String(length=250), nullable=True),
    sa.Column('is_featured', sa.Boolean(), nullable=True),
    sa.Column('is_hidden', sa.Boolean(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('default_endpoint', sa.Text(), nullable=True),
    sa.Column('user_id', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('cluster_name', sa.String(length=250), sa.ForeignKey("clusters.cluster_name"), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=False),
    # sa.Column('created_by_fk', sa.Integer(), nullable=False),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['cluster_name'], ['clusters.cluster_name'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['user_id'], ['ab_user.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('datasource_name')
    )
    op.create_table('tables',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('table_name', sa.String(length=250), nullable=True),
    sa.Column('main_dttm_col', sa.String(length=250), nullable=True),
    sa.Column('default_endpoint', sa.Text(), nullable=True),
    sa.Column('database_id', sa.Integer(), sa.ForeignKey("dbs.id"), nullable=False),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.ForeignKeyConstraint(['database_id'], ['dbs.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('table_name')
    )
    op.create_table('columns',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('datasource_name', sa.String(length=250), nullable=True),
    sa.Column('column_name', sa.String(length=256), sa.ForeignKey("datasources.datasource_name"), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=True),
    sa.Column('type', sa.String(length=32), nullable=True),
    sa.Column('groupby', sa.Boolean(), nullable=True),
    sa.Column('count_distinct', sa.Boolean(), nullable=True),
    sa.Column('sum', sa.Boolean(), nullable=True),
    sa.Column('max', sa.Boolean(), nullable=True),
    sa.Column('min', sa.Boolean(), nullable=True),
    sa.Column('filterable', sa.Boolean(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.ForeignKeyConstraint(['datasource_name'], ['datasources.datasource_name'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('metrics',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('metric_name', sa.String(length=512), nullable=True),
    sa.Column('verbose_name', sa.String(length=1024), nullable=True),
    sa.Column('metric_type', sa.String(length=32), nullable=True),
    sa.Column('datasource_name', sa.String(length=250), sa.ForeignKey("datasources.datasource_name"), nullable=True),
    sa.Column('json', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['datasource_name'], ['datasources.datasource_name'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('slices',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('slice_name', sa.String(length=250), nullable=True),
    sa.Column('druid_datasource_id', sa.Integer(), sa.ForeignKey("datasources.id"), nullable=True),
    sa.Column('table_id', sa.Integer(), sa.ForeignKey("tables.id"), nullable=True),
    sa.Column('datasource_type', sa.String(length=200), nullable=True),
    sa.Column('datasource_name', sa.String(length=2000), nullable=True),
    sa.Column('viz_type', sa.String(length=250), nullable=True),
    sa.Column('params', sa.Text(), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.ForeignKeyConstraint(['druid_datasource_id'], ['datasources.id'], ),
    # sa.ForeignKeyConstraint(['table_id'], ['tables.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('sql_metrics',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('metric_name', sa.String(length=512), nullable=True),
    sa.Column('verbose_name', sa.String(length=1024), nullable=True),
    sa.Column('metric_type', sa.String(length=32), nullable=True),
    sa.Column('table_id', sa.Integer(), sa.ForeignKey("tables.id"), nullable=True),
    sa.Column('expression', sa.Text(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['table_id'], ['tables.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('table_columns',
    sa.Column('created_on', sa.DateTime(), nullable=False),
    sa.Column('changed_on', sa.DateTime(), nullable=False),
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('table_id', sa.Integer(), sa.ForeignKey("tables.id"), nullable=True),
    sa.Column('column_name', sa.String(length=256), nullable=True),
    sa.Column('is_dttm', sa.Boolean(), nullable=True),
    sa.Column('is_active', sa.Boolean(), nullable=True),
    sa.Column('type', sa.String(length=32), nullable=True),
    sa.Column('groupby', sa.Boolean(), nullable=True),
    sa.Column('count_distinct', sa.Boolean(), nullable=True),
    sa.Column('sum', sa.Boolean(), nullable=True),
    sa.Column('max', sa.Boolean(), nullable=True),
    sa.Column('min', sa.Boolean(), nullable=True),
    sa.Column('filterable', sa.Boolean(), nullable=True),
    sa.Column('description', sa.Text(), nullable=True),
    sa.Column('created_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    sa.Column('changed_by_fk', sa.Integer(), sa.ForeignKey("ab_user.id"), nullable=True),
    # sa.Column('created_by_fk', sa.Integer(), nullable=True),
    # sa.Column('changed_by_fk', sa.Integer(), nullable=True),
    # sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id'], ),
    # sa.ForeignKeyConstraint(['table_id'], ['tables.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('dashboard_slices',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('dashboard_id', sa.Integer(), sa.ForeignKey("dashboards.id"), nullable=True),
    sa.Column('slice_id', sa.Integer(), sa.ForeignKey("slices.id"), nullable=True),
    # sa.ForeignKeyConstraint(['dashboard_id'], ['dashboards.id'], ),
    # sa.ForeignKeyConstraint(['slice_id'], ['slices.id'], ),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

knoguchi · 2016-04-04T14:28:50Z

@greens231 would you create a pull request? It's hard to see the diff.

rohancs · 2016-06-11T21:32:42Z

how can i benefit from this fix? pip install caravel brings down the bug with it....any advice on how to build and deploy from the source code?

dugjason · 2016-10-10T11:52:15Z

@rohancs, instructions for making your own build are available in the "Making your own build
" section of http://airbnb.io/caravel/installation.html:

For more advanced users, you may want to build Caravel from sources. That would be the case if you fork the project to add features specific to your environment.:
# assuming $CARAVEL_HOME as the root of the repo
cd $CARAVEL_HOME/caravel/assets
npm install
npm run prod
cd $CARAVEL_HOME
python setup.py install

Bumps [@vx/axis](https://github.com/hshoff/vx) from 0.0.191 to 0.0.192. - [Release notes](https://github.com/hshoff/vx/releases) - [Changelog](https://github.com/hshoff/vx/blob/master/CHANGELOG.md) - [Commits](airbnb/visx@v0.0.191...v0.0.192) Signed-off-by: dependabot-preview[bot] <support@dependabot.com>

greens231 mentioned this issue Apr 4, 2016

Update 4e6a06bad7a8_init.py #256

Merged

mistercrunch closed this as completed in #256 Apr 6, 2016

andrewhn mentioned this issue May 2, 2016

Upgrading to 0.8.9 with postgres metadata db hangs at 4fa8 #422

Closed

agfunder mentioned this issue Oct 23, 2016

Migrations for 0.11 hangs with posgresql #1260

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

'caravel db upgrade' hangs with Postgres RDS for metadata DB #238

'caravel db upgrade' hangs with Postgres RDS for metadata DB #238

ptierney commented Apr 2, 2016

greens231 commented Apr 2, 2016

adamflanagan commented Apr 2, 2016

greens231 commented Apr 2, 2016

adamflanagan commented Apr 3, 2016

knoguchi commented Apr 4, 2016

greens231 commented Apr 4, 2016 •

edited by mistercrunch

knoguchi commented Apr 4, 2016

rohancs commented Jun 11, 2016

dugjason commented Oct 10, 2016

'caravel db upgrade' hangs with Postgres RDS for metadata DB #238

'caravel db upgrade' hangs with Postgres RDS for metadata DB #238

Comments

ptierney commented Apr 2, 2016

greens231 commented Apr 2, 2016

adamflanagan commented Apr 2, 2016

greens231 commented Apr 2, 2016

adamflanagan commented Apr 3, 2016

knoguchi commented Apr 4, 2016

greens231 commented Apr 4, 2016 • edited by mistercrunch

knoguchi commented Apr 4, 2016

rohancs commented Jun 11, 2016

dugjason commented Oct 10, 2016

greens231 commented Apr 4, 2016 •

edited by mistercrunch