sqlalchemy.exc.OperationalError

[birdspring]

I've changed the directory of caravel.db to a local path.
When I restarted the machine and exeuted the "pip install caravel --upgrade",
input the "caravel db upgrade", the error is "sqlalchemy.exc.OperationalError: (pysqlite2.dbapi2.OperationalError) duplicate column name: verbose_name [SQL: u'ALTER TABLE table_columns ADD COLUMN verbose_name VARCHAR(1024)']"

then I input the "caravel init", and error is "sqlalchemy.exc.OperationalError: (pysqlite2.dbapi2.OperationalError) no such column: sql_metrics.is_restricted [SQL: u'SELECT sql_metrics.created_on AS sql_metrics_created_on, sql_metrics.changed_on AS sql_metrics_changed_on, sql_metrics.id AS sql_metrics_id, sql_metrics.metric_name AS sql_metrics_metric_name, sql_metrics.verbose_name AS sql_metrics_verbose_name, sql_metrics.metric_type AS sql_metrics_metric_type, sql_metrics.table_id AS sql_metrics_table_id, sql_metrics.expression AS sql_metrics_expression, sql_metrics.description AS sql_metrics_description, sql_metrics.is_restricted AS sql_metrics_is_restricted, sql_metrics.changed_by_fk AS sql_metrics_changed_by_fk, sql_metrics.created_by_fk AS sql_metrics_created_by_fk \nFROM sql_metrics']"

I tried many ways to solve this problem, but none of them worked.

Thanks for your solution,
best

[ljank]

I've encountered the same error right after pip install caravel --upgrade (0.9.0 -> 0.9.1) when running caravel db upgrade.

[mistercrunch]

I looked at the migrations and I can't figure out how this migration script would get called twice.

[birdspring]

I tried changing the target folder, and now it works again.
But I afraid the problem would happen again.

[ljank]

Here's the migration history for 0.9.1:

1226819ee0e3 -> d8bc074f7aad (head), Add new field 'is_restricted' to SqlMetric and DruidMetric
956a063c52b3 -> 1226819ee0e3, Fix wrong constraint on table columns
f0fbf6129e13 -> 956a063c52b3, adjusting key length
c3a8f8611885 -> f0fbf6129e13, Adding verbose_name to tablecolumn
4fa88fe24e94 -> c3a8f8611885, Materializing permission
b4456560d4f3 -> 4fa88fe24e94, owners_many_to_many
bb51420eaf83 -> b4456560d4f3, change_table_unique_constraint
867bf4f117f9 -> bb51420eaf83, add schema to table model
fee7b758c130 -> 867bf4f117f9, Adding extra field to Database model
1d2ddd543133, 763d4b211ec9 -> fee7b758c130 (mergepoint), empty message
d2424a248d63 -> 1d2ddd543133, log dt
d2424a248d63 -> 763d4b211ec9, fixing audit fk
a2d606a761d9, 836c0bf75904 -> d2424a248d63 (branchpoint) (mergepoint), empty message
18e88e1cc004 -> a2d606a761d9, adding favstar model
18e88e1cc004 -> 836c0bf75904, cache_timeouts
430039611635 -> 18e88e1cc004 (branchpoint), making audit nullable
d827694c7555 -> 430039611635, log more
43df8de3a5f4 -> d827694c7555, css templates
7dbf98566af7 -> 43df8de3a5f4, empty message
8e80a26a31db -> 7dbf98566af7, empty message
2591d77e9831 -> 8e80a26a31db, empty message
12d55656cbca -> 2591d77e9831, user_id
55179c7f25c7 -> 12d55656cbca, is_featured
315b3f4da9b0 -> 55179c7f25c7, sqla_descr
1a48a5411020 -> 315b3f4da9b0, adding log model
289ce07647b -> 1a48a5411020, adding slug to dash
2929af7925ed -> 289ce07647b, Add encrypted password field
1e2841a4128 -> 2929af7925ed, TZ offsets in data sources
5a7bad26f2a7 -> 1e2841a4128, empty message
4e6a06bad7a8 -> 5a7bad26f2a7, empty message
<base> -> 4e6a06bad7a8, Init

I've found that caravel db current was at c3a8f8611885, which means right before migration that's failing:

c3a8f8611885 -> f0fbf6129e13, Adding verbose_name to tablecolumn

and here's the table schema at that moment:

sqlite> .schema table_columns
CREATE TABLE table_columns (
        created_on DATETIME NOT NULL,
        changed_on DATETIME NOT NULL,
        id INTEGER NOT NULL,
        table_id INTEGER,
        column_name VARCHAR(256),
        is_dttm BOOLEAN,
        is_active BOOLEAN,
        type VARCHAR(32),
        groupby BOOLEAN,
        count_distinct BOOLEAN,
        sum BOOLEAN,
        max BOOLEAN,
        min BOOLEAN,
        filterable BOOLEAN,
        description TEXT,
        created_by_fk INTEGER,
        changed_by_fk INTEGER, 
        expression TEXT, 
        verbose_name VARCHAR(1024), -- !!!
        PRIMARY KEY (id),
        FOREIGN KEY(table_id) REFERENCES tables (id),
        CHECK (is_dttm IN (0, 1)),
        CHECK (is_active IN (0, 1)),
        CHECK (groupby IN (0, 1)),
        CHECK (count_distinct IN (0, 1)),
        CHECK (sum IN (0, 1)),
        CHECK (max IN (0, 1)),
        CHECK (min IN (0, 1)),
        CHECK (filterable IN (0, 1)),
        FOREIGN KEY(created_by_fk) REFERENCES ab_user (id),
        FOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)
);

it already has that column!

Seems like migration was partially done (column created), but not finished (not marked as done). Perhaps I've missed some error/warning when installing caravel for the first time :\

After manually dropping the column everything went just fine.

[x4base]

It happened to me once. Sometimes the migration script is executed successfully, but the version number in the table "alembic_version" just didn't get updated. However, I wasn't able to find the cause at that time.