Migration error on upgrade when database is reset via manage.py

[emkll]

Description

If a database was reset with ./manage.py reset, when upgrading to SecureDrop 1.0.0, the postint task will fail during the SQLAlchemy migration with the following error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column: uuid
[SQL: ALTER TABLE sources_tmp ADD COLUMN uuid VARCHAR(36)]

Steps to Reproduce

• Install on SecureDrop 1.0.0 (or before)
• Reset the database ./manage.py reset
• Run the postint sudo dpkg-reconfigure securedrop-app-code
• observe the error message/failure above

Expected Behavior

• The database migration should be applied

Actual Behavior

• The database migration fails and the postinst action fails, the package
• Server error on Journalist and Source interfaces

Comments

Because manage.py is mostly dev-facing, this should not affect production instances.

[emkll]

In case this happens to someone in dev/testing, deleting (or moving) the database in /var/lib/securedrop/db.sqlite and re running sudo dpkg-reconfigure securedrop-app-code will reinitialize a new (empty) working database.

[eloquence]

A proper fix won't make it to 1.1.0 but proposed a string change in #4850 for risk mitigation.

[emkll]

If no one (in dev) uses this functionality, and the same functionality is achieved by simply deleting the database locally and reconfiguring the securedrop-app-code package, we should consider simply removing the code.

[zenmonkeykstop]

will dpkg-reconfigure nuke the submissions directory? (And I guess will either it or the reset command do so safely?) I do use this occasionally in testing.

[rocodes]

Update for our future selves that dkpg-reconfigure --force was required in my case, and I also ran into this due to some ./manage.py reset and other QA-related shenanigans.

[sssoleileraaa]

I ran into this issue today and #4839 (comment) worked for me