Incubator-superset: Cannot create table with same name from a different datasource

[frankfarrell]

Make sure these boxes are checked before submitting your issue - thank you!

• [ ] I have checked the superset logs for python stacktraces and included it here as text if any
• [ ] I have reproduced the issue with at least the latest released version of superset
• [ ] I have checked the issue tracker for the same issue and I haven't found one similar

Superset version

0.19.1

Expected results

That you could add the same table from a differnet datasource

Actual results

(raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: tables.table_name [SQL: 'INSERT INTO tables (created_on, changed_on, description, default_endpoint, is_featured, filter_select_enabled, "offset", cache_timeout, params, perm, table_name, main_dttm_col, database_id, fetch_values_predicate, user_id, schema, sql, created_by_fk, changed_by_fk) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'] [parameters: ('2017-11-10 16:36:45.635385', '2017-11-10 16:36:45.635417', None, None, 0, 0, 0, None, None, None, '', None, 2, None, None, '', None, None, None)]

Steps to reproduce

We have a few datasources using the same schema. We would like to configure query tables from each.

It seems an easy fix would be just to change this line: https://github.com/apache/incubator-
superset/blob/master/superset/migrations/versions/4e6a06bad7a8_init.py#L84
to sa.UniqueConstraint('table_name', 'database_id'). This line does have such a constraint ( with schema too), https://github.com/apache/incubator-superset/blob/1ea4521d0c116d96b51749613347279669529dd0/superset/connectors/sqla/models.py#L188

Its similar to https://github.com/apache/incubator-superset/pull/3583/files, so perhaps a fix could be included in that PR too?

Thanks, great project by the way!

[xrmx]

Feel free to open a PR

[frankfarrell]

Will do

[mistercrunch]

I think this open PR solves this: https://github.com/apache/incubator-superset/pull/3583

[frankfarrell]

This is the error. Its very similar to #3583 but not the same.
For slices it may be necessary to show / rather than just table name?

[frankfarrell]

https://github.com/apache/incubator-superset/compare/master...frankfarrell:tables-constraints?expand=1
It just needs the alembic upgrade/downgrade functiosn to match now

[frankfarrell]

Root cause here is that the alembic migrations scripts first creates a unique constraint on table name:
https://github.com/apache/incubator-superset/blob/master/superset/migrations/versions/4e6a06bad7a8_init.py#L84
Later revisions try to create the correct unique constraint on table)name, schema and database:
https://github.com/apache/incubator-superset/blob/master/superset/migrations/versions/b4456560d4f3_change_table_unique_constraint.py#L21
and
https://github.com/apache/incubator-superset/blob/master/superset/migrations/versions/3b626e2a6783_sync_db_with_models.py#L65

However, sqlite doesn't allow dropping unnamed unique constraints:
https://stackoverflow.com/questions/42013265/remove-unique-constraint-on-a-column-in-sqlite-database

Solution would be to rename table, create new table and copy over data in a new migration. Or use some other db :)

[mistercrunch]

We could also alter the creation of the constraint in the first place. Won't help existing installations but it will help future ones.

[frankfarrell]

@mistercrunch That's also an option, I wasn't sure if you wanted to keep the migration scripts immutable or not. Chances are that nobody will upgrade an sqlite db in place.
In any case I have the correct migration done in the PR: #3885, which fixes the issue

[mistercrunch]

In general we want to keep the migrations static but in cases like this I think it's ok to alter them, as long as downstream migration account for both branches in history.

I'll let @john-bodley review the PR since he's done something very similar on the Druid side recently.

[john-bodley]

@frankfarrell are you certain you weren't able to delete an unnamed uniqueness constraint for SQLite? There's a section on the Alembic documentation about dealing with constraints in SQLite.

I was able to leverage this logic for dropping uniqueness constraints here

[frankfarrell]

@john-bodley I am not certain no. I can try that tomorrow, thanks for the info!

These 2 following constraint modifications didn't work :
https://github.com/apache/incubator-superset/blob/15b67b2c6c3c2982f6620fce5d30bd05951458f7/superset/migrations/versions/b4456560d4f3_change_table_unique_constraint.py#L19
https://github.com/apache/incubator-superset/blob/15b67b2c6c3c2982f6620fce5d30bd05951458f7/superset/migrations/versions/3b626e2a6783_sync_db_with_models.py#L63

[jerowe]

I'm having the same issue. Is this still being worked on?

[frankfarrell]

I created this PR https://github.com/apache/incubator-superset/pull/3885 which solves the issue. Its uses plain SQL, I haven't updated it to use alembic as described by @john-bodley above. Should I do that or does it look ok to merge as is?

[asyd]

Any news by chance ?

[asyd]

Oh, just tested 0.24.0 and seem it's working, so maybe someone should close this issue?

[SanjayJosh]

I am using 0.25.6 . This still seems to be an issue.

I tried adding another nyc_data_external from a different database customer_data, which also happens to have nyc_data_external table. The error was still thrown, @mistercrunch

[SanjayJosh]

I was thinking, since users generally don't migrate once the app is initialised, how about we add the script to re configure the tables table for sqlite3 as a part of the code when superset init runs.
It could reside well as utils function.

[Preston4tw]

@SanjayJosh it might be worth opening a new issue, this may be a regression.

[raof01]

I am using 0.25.6 . This still seems to be an issue.

I tried adding another nyc_data_external from a different database customer_data, which also happens to have nyc_data_external table. The error was still thrown, @mistercrunch

@SanjayJosh It seems there's no new issue about the regression? Or did you just found it's gone?

[FredericLatour]

I just can'( believe it's not possible to have two databases with tables having the same name??!!
I must be missing something ... am I ?

[natbusa]

This problem might be sqlite specific because of the very peculiar way of working with alembic.
Switch to postgres for metadata will most likely solve the problem.

[andreas-eberle]

@mistercrunch: Can this be reopend? It still isn't fixed. I also experience this issue.
@natbusa: Have you been able to test your suggestion?

[natbusa]

Yes it works in combination with modifing this line in SqlaTable class
__table_args__ = ( UniqueConstraint("database_id", "schema", "table_name"),)

[snower]

I found that this problem still exists when using mysql, because the "table_name" unique index created in the "tables" table only contains "table_name" but not "database_id", and after adding "database_id" to the unique index, the table can be created normally.

[rob05c]

I just hit the same issue, with the default sqlite metadata source. Fixed it in sqlite3 with

$ sqlite3 ~/.superset/superset.db

CREATE TABLE "tables2" (
⋮ 
        UNIQUE (table_name, database_id),
⋮ 
;
insert into tables2 select * from tables;
drop table tables;
alter table tables2 rename to tables;

Seems like it isn't fixed in the latest stable version, which just needs UNIQUE (table_name), changed to UNIQUE (table_name, database_id),.

$ superset version
Superset 0.37.2