Migration of `default collection is local` during update leads to longer downtime

[SimJoSt]

We just updated the app to version 25.0.1 which included the fix default collection is local https://github.com/nextcloud/fulltextsearch/pull/735 We initially upgrade the app via the web interface and when it seemed to not finish, we switched to the command line. We didn't know that there was a database table migration process still running, so we ran into locking timeouts when running occ upgrade, which it required us to perform.

$ occ upgrade
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug
Updating database schema
Updated database
Updating <fulltextsearch> ...
Exception: Database error when running migration 2401Date202301170001 for app fulltextsearch
An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
Update failed

Restarting the whole server (and mostly the database server) allowed us to start the upgrade process via the command line properly without any issues. The whole process ran for close to 23 minutes. Most of it was the migration (> 15 minutes), some part of it was the code integrity check.

The issue was the size of our index database, with nearly 4 million entries. I suspect our instance is not the only one with this setup and issue.

There is no specific bug I want to report, no improvement I can recommend, however I wanted to raise awareness of this issue. Nextcloud sometimes posts notices in the admin overview in to let admins know, that there are database migrations that would run for a longer time. I don't know if something similar is possible for apps as well. At least a notice in the changelog for admins would be greatly appreciated.

[jfriedrichs85]

We encountered the same problem. But we have ~71 Million entries. This leads not only to a lock wait... we also get "General error: 1105 Maximum write set size exceeded". We can not increase the WS size because of galera. So basically ... this app is now dead for us now.

The UPDATE query should be more tuned and not only be thrown against a huge table...