Downgrading then upgrading redash schemas doesn't work

[wlach]

Issue Summary

After updating to latest main (1672cd9) and then downgrading to a previous version, upgrading again doesn't work unless you do the following:

alter table queries alter schedule drop default;
alter table queries alter options drop default;
alter table events alter additional_properties drop default;
alter table organizations alter settings drop default;
alter table alerts alter options drop default;
alter table dashboards alter options drop default;
alter table dashboards alter layout drop default;
alter table changes alter change drop default;
alter table visualizations alter options drop default;
alter table widgets alter options drop default;

The problem seems to be that this migration sets a default on downgrade, where none existed before. When you try to add a json schema to that, redash aborts.

Additional gotcha:

The "schedule" column gets casted to the "null" string on downgrade. You need to do a manual:

update queries set schedule=null where schedule='null';

Looks like the migrations modified in the following PRs are the culprits:

#6687
#6707
#6713

/cc @AndrewChubatiuk

[AndrewChubatiuk]

@wlach thanks for a finding
could you please submit PR with a fix?

[wlach]

@wlach thanks for a finding could you please submit PR with a fix?

Will see what I can do.

[wlach]

Found an additional gotcha (schedule for queries can get set to the string "null" on downgrade), updated description.

[justinclift]

Oh, this all doesn't sound good.

Breaking the schema when downgrading is something we'd better fix.

The rough plan is to enable automatic schema upgrades when Redash starts (rather than a manual process). So being able to downgrade stuff when needed for some reason is probably pretty important.

[wlach]

Yeah, it's a bit unfortunate -- I assume the alembic db migrations were automatically generated, and were just wrong in this case. I think we need to verify (via pg_dump or similar) that the schema is as you'd expect after an arbitrary number of upgrades and downgrades.

FWIW my testing setup is to do this to downgrade:

./manage.py db downgrade fd4fc850d7ea

And this to upgrade:

./manage.py db upgrade

[wlach]

@wlach thanks for a finding could you please submit PR with a fix?

Will see what I can do.

Update: I haven't had time to look at this and am not sure when I will.

[justinclift]

@wlach This should be fixed in #7068, thanks to @ezraodio1. 😄