MsSql: Upgrade db failed - pyodbc - column length discrepancy

[ludzzz]

Platform: Windows 7
Python Library connector: pyodbc

When running: caravel db upgrade
Got the error below:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Column 'datasources.datasource_name' is not
the same length or scale as referencing column 'columns.column_name' in foreign key 'FK__columns__column___5DCAEF64'. Columns participating in a foreign key relationship must b
e defined with the same length and scale. (1753) (SQLExecDirectW); [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not create constraint. See previous error
s. (1750)") [SQL: u'\nCREATE TABLE columns (\n\tcreated_on DATETIME NOT NULL, \n\tchanged_on DATETIME NOT NULL, \n\tid INTEGER NOT NULL IDENTITY(1,1), \n\tdatasource_name VARCH
AR(250) NULL, \n\tcolumn_name VARCHAR(256) NULL, \n\tis_active BIT NULL, \n\ttype VARCHAR(32) NULL, \n\tgroupby BIT NULL, \n\tcount_distinct BIT NULL, \n\tsum BIT NULL, \n\tmax
 BIT NULL, \n\tmin BIT NULL, \n\tfilterable BIT NULL, \n\tdescription VARCHAR(max) NULL, \n\tcreated_by_fk INTEGER NULL, \n\tchanged_by_fk INTEGER NULL, \n\tPRIMARY KEY (id), \
n\tFOREIGN KEY(column_name) REFERENCES datasources (datasource_name), \n\tCHECK (is_active IN (0, 1)), \n\tCHECK (groupby IN (0, 1)), \n\tCHECK (count_distinct IN (0, 1)), \n\t
CHECK (sum IN (0, 1)), \n\tCHECK (max IN (0, 1)), \n\tCHECK (min IN (0, 1)), \n\tCHECK (filterable IN (0, 1)), \n\tFOREIGN KEY(created_by_fk) REFERENCES ab_user (id), \n\tFOREI
GN KEY(changed_by_fk) REFERENCES ab_user (id)\n)\n\n'] ```
In the migration script _4e6a06bad7a8_init.py_ there is a foreign key between **datasources.datasource_name** and **columns.column_name** incompatible with Sql Server because of a discrepancy on the length of the definition of the column.

In the migration script 4e6a06bad7a8_init.py we have a foreign key between datasources.datasource_name and columns.column_name incompatible with Sql Server because of a discrepancy on the length of the field.

61. sa.Column('datasource_name', sa.String(length=250), nullable=True),
...
91. sa.Column('column_name', sa.String(length=256), sa.ForeignKey("datasources.datasource_name"), nullable=True),

To fix it , I changed the line 61 of 4e6a06bad7a8_init.py:
sa.Column('datasource_name', sa.String(length=250), nullable=True),
to
sa.Column('datasource_name', sa.String(length=256), nullable=True),

Might be worth to change it in the repo. The impact is quite low.

[mistercrunch]

Seems specific to Sql Server, but I'd approve that PR, I think you'd have to touch the model as well

[ludzzz]

It's definitely, Sql server specific.
The PR I suggested, increase the size of one column shouldn't break anything.
But I don't know the project in detail yet, and the possible side effects.

[IljaKroonen]

@gyzmau, did you manage to make caravel work with your fix? After pulling the fixed version, I got

sqlalchemy.exc.OperationalError: (pymssql.OperationalError) (1753, "Column 'datasources.datasource_name' is not the same length or scale as referencing column 'metrics.datasource_name' in foreign key 'FK__metrics__datasou__00AA174D'. Columns participating in a foreign key relationship must be defined with the same length and scale.DB-Lib error message 1753, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 1750, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: '\nCREATE TABLE metrics (\n\tid INTEGER NOT NULL IDENTITY(1,1), \n\tmetric_name VARCHAR(512) NULL, \n\tverbose_name VARCHAR(1024) NULL, \n\tmetric_type VARCHAR(32) NULL, \n\tdatasource_name VARCHAR(250) NULL, \n\tjson VARCHAR(max) NULL, \n\tdescription VARCHAR(max) NULL, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(datasource_name) REFERENCES datasources (datasource_name), \n\tFOREIGN KEY(datasource_name) REFERENCES datasources (datasource_name)\n)\n\n']

This seemed very similar to the FK length discrepency you described in this issue, which is why I attempted to fix it like this

diff --git a/caravel/migrations/versions/4e6a06bad7a8_init.py b/caravel/migrations/versions/4e6a06bad7a8_init.py
index d448aa7..f083616 100644
--- a/caravel/migrations/versions/4e6a06bad7a8_init.py
+++ b/caravel/migrations/versions/4e6a06bad7a8_init.py
@@ -107,7 +107,7 @@ def upgrade():
     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('datasource_name', sa.String(length=256), 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'], ),

Now i get

sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (208, "Invalid object name 'alembic_version'.DB-Lib error message 208, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: "UPDATE alembic_version SET version_num='b4456560d4f3' WHERE alembic_version.version_num = 'bb51420eaf83'"]

Before trying further edits I would like to know if in your case everything is working properly with SQL server.

[aljones]

@IljaKroonen

I think that is a problem with the b4456560d4f3_change_table_unique_constraint.py migration.
That it would result in none of the tables being created and that particular error surprises me though.

If you remove the try clause from b4456560d4f3 then it gives an error that the constraint it is trying to drop does not exist. I searched the project for the constraint name tables_table_name_key but didn't find where it is created. Maybe the name is wrong?

What I did for now is run fabmanager create-db --app caravel to create the db from the model definitions and python caravel/bin/caravel db stamp head so that the migration scripts know the db is up to date.

[sebastianbk]

@mistercrunch: This problem continues to exist when using an Azure SQL Database as a backing database for Superset.

Here is the setting in superset_config.py that I used:

SQLALCHEMY_DATABASE_URI = 'mssql+pymssql://sensomind@sensomindsuperset:${SUPERSET_METADATA_PASSWORD}@sensomindsuperset.database.windows.net:1433/superset'

Is there any workaround for this error?