Database migration fails for new schemes

[Hartmnt]

Description

When trying to increase the ServerDatabase::DB_SCHEME_VERSION without changing anything else, migration of an existing v10 database fails with:

[ERROR]: Exiting due to unhandled exception: error preparing statement: index server_logs_message_date_index already exists while preparing "CREATE INDEX "server_logs_message_date_index" ON "server_logs" ("message_date")".

The tested database backend is sqlite. Others were not tested.

I have to remove all existing indices and triggers manually, to end up with:

[ERROR]: Exiting due to unhandled exception: Failed to delete 1 old tables after migration

after that.

This is not a user facing issue, this is a developer facing issue. Migration from v9 to v10 seems to work fine as far as I can tell.

Steps to reproduce

1. Have an existing v10 Database
2. Rebuild mumble-server with a version change to v11 and no scheme changes
3. Observe mumble-server being unable to migrate

Mumble version

1.6.x

Mumble component

Server

OS

Linux

Reproducible?

Yes

Additional information

No response

Relevant log output

Screenshots

No response

[Krzmbrzl]

I'm reasonably certain that old migrations work as those databases don't use the new names for indices etc. However, since v10 the new names are used and hence during the creation of new tables in the migration process, we get a clash of these names (which appear to be global).

As I see it, we have the following options:

• Remove indices of old tables before creating new ones. In order for this to work reliably in the future as well, this requires auto-detection of all existing indices, triggers, etc.
• Create data tables that contain the data of the old tables, without the verification machinery. Then delete old tables before creating new ones
• Postpone creation of indices etc. in new tables to after old tables are deleted. This has a distinct disadvantage that data validation would be disabled during migration of data from old to new.

I'll have to think of this some more…