Upgrade from 27.1.6 stable to 28.0.2 stable fails! Database errors in occ upgrade

Upgrade from 27.1.6 stable to 28.0.2 stable fails hard. Nextcloud is broken.

Nextcloud version 27.1.6 upgrading to 28.0.2
Operating system and version Ubuntu 22.04
Apache/2.4.52 (Ubuntu)
PHP 8.1.2-1ubuntu2.14
Server version: 10.6.16-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04
The issue you are facing: Update failed, nextcloud is broken.

Should the "occ upgrade" command be executed? [Y/n] y
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
Repair step: Repair MySQL collation
Repair info: All tables already have the correct collation -> nothing to do
Repair step: Copy data from accounts table when migrating from ownCloud
Repair step: Drop account terms table when migrating from ownCloud
Updating database schema
Exception: Database error when running migration 28000Date20230906104802 for app core
Index name "fs_mtime" for table "oc_filecache" collides with the constraint on table "oc_filecache_temp".
Update failed
Maintenance mode is kept active
Resetting log level

Is this the first time you’ve seen this error? Yes

Steps to replicate it:

  1. Upgrade from 27.1.6 to 28.0.2

The output of your Nextcloud log in Admin > Logging:

Nextcloud is NOT operational!

Why are you releasing such a buggy code?
If it does not like something in the database, why it does not fix it? Or live with it? Why break working instance? I am not a DBA, nor the vast majority of your users!

Advice for all: do NOT upgrade without a FULL backup.

Been told by a developer that this table “oc_filecache_temp” does not belong to Nextcloud, and having a fresh full backup, i have deleted (dropped) this table from nextcloud database.
Then i was able to continue the upgrade:
sudo -u www-data php /var/www/nextcloud/occ upgrade
It went almost well. Some apps were disabled, some indices were missing, you have to manually configure the maintenance window time in config.php.
It looks like working for now.
But these types of upgrade fails should be dealt with or documented way better. Because in the field you have no choice except the roll-back.

I experienced the very same problem when running the update today. For everyone who has the same problem and is unaware of how to do the rollback, these are the steps I used to get the instance running again. DISCLAIMER: FOLLOWING BELOW STEPS IS AT YOUR OWN RISK. MAKE SURE ALL DATA IS BACKED UP BEFORE ATTEMPTING RECOVERY!

  1. Don’t panic, everything you need for the roll-back is there.
  2. Make a backup of the contents of your installation folder (typically nextcloud/) excluding the data/ directory. The data directory is where all the user data lives and therefore it is going to be very large.
  3. Delete all files in the installation folder except for .htaccess
  4. navigate to /data/updater-xxxxxxxx/backups/ and choose the newest backup, called nextcloud-27.1.6xxxxxxxxxx. Copy all files in the backup folder to the installation folder. Now your Nextcloud should be running again once you refresh the webpage. If it doesn’t, make sure maintenance mode is disabled.
  5. Run php occ maintenance:repair to purge the traces of the failed upgrade. Otherwise, when your next run an update, the cloud will think it already finished since the database still has the final state of the failed upgrade stored and the update will fail without notice.