Bug #1820
Database schema upgrade may fail, leaving the database in a non working state (wrap the entire upgrade in a transaction and do away with the multi-step upgrade)
0%
Description
Right now, the schema upgrade (AbstractSqlStorage::upgradeDb) does not wrap the upgrade in a transaction, but uses a multi-step upgrade process that saves the step number into the database.
There are problems with this multi-step upgrade process:
- if the core process crashes or is killed mid-upgrade, the query for the current step may have finished executing, but the query that increases the step number in the database may have not, so the next time the core is started, it will re-execute the same query and potentially fail depending on the query.
- the user is not prompted before the upgrade is started (already opened bug https://bugs.quassel-irc.org/issues/1507)
- the upgrade is not wrapped in a transaction, which means that if it fails, the database is unusable without manual intervention (not even with the old core version)
I propose that the process is changed so that, if the database supports it (both SQLite and PostgreSQL have for a long time) the entire upgrade is done in a single transaction. The multi-step process is unnecessary if the upgrade is done in a single transaction, as any checkpoint will be lost when the transaction is rolled back.