Upgrade to NC28 yields "Exception: Database error when running migration…"

[nursoda]

Describe the bug Upgrading a fully patched NC27 instance to NC28 while the Social app is installed led to the instance being stuck in maintenance mode and required manual intervention in the database.

Created as issue here as requested in the forum by blizzz.

To Reproduce After an occ upgrade to the final NC28 release 28.0.0.11 I also got

Exception: Database error when running migration 28000Date20230906104802 for app core
Index name "aot" for table "oc_social_action" collides with the constraint on table "oc_social_3_action".
Update failed

So, using mariadb, I dropped that index, and retried. Each time, I got another index clash. In the end, I had to drop all of these:

drop index aot on oc_social_action;
drop index aoa on oc_social_follow;
drop index sa on oc_social_stream_act;
drop index ts on oc_social_stream_dest;
drop index sh on oc_social_stream_tag;
drop index attributed_to_prim on oc_social_stream;
drop index afoa on oc_social_follow;
drop index sat on oc_social_stream_dest;
drop index object_id_prim on oc_social_stream;
drop index in_reply_to_prim on oc_social_stream;
drop index ipoha on oc_social_stream;

Back in the cloud, these rebuild the indices acutally needed:

occ maintenance:mode --off
occ upgrade
occ db:add-missing-indices

Expected behavior Social and core devs should make sure migrations do work as expected?

Client details: not relevant as not involved

Server details: Social app version: 0.6.1 Operating system: Arch Linux Web server: NGINX Database: Mariadb PHP version: 8.2 Nextcloud version: 27.1.4.? (release) → 28.0.0.11 (release)

[rponline]

Thank you, @nursoda!

I had a similar problem upgrading on stable branch to 28.0.0 ...

Exception: Database error when running migration 28000Date20230906104802 for app core
Index name "afoa" for table "oc_social_follow" collides with the constraint on table "oc_social_3_follow".
Update failed

And your solution solved the problem :)

(PHP Version: 8.1.18)

[DivineDominion]

I've double checked the current table names and it looks like the table names are the ones without the 3:

https://github.com/nextcloud/social/blob/dc640af35f58484fcb2e206d92c2b58ec6fa3553/lib/Db/CoreRequestBuilder.php#L60-L74

So rather drop the old ones, aka oc_social_3_action instead of oc_social_action.

Arguably the old tables can be removed completely, too:

drop table oc_social_3_action; drop table oc_social_3_actor; drop table oc_social_3_cache_actor; drop table oc_social_3_cache_doc; drop table oc_social_3_client; drop table oc_social_3_follow; drop table oc_social_3_hashtag; drop table oc_social_3_instance; drop table oc_social_3_req_queue; drop table oc_social_3_stream; drop table oc_social_3_stream_act ; drop table oc_social_3_stream_dest; drop table oc_social_3_stream_queue; drop table oc_social_3_stream_tag;

Just in case, dump the tables as a backup first:

mysqldump -u nextcloud -p nextcloud oc_social_3_action oc_social_3_actor oc_social_3_cache_actor oc_social_3_cache_doc oc_social_3_client oc_social_3_follow oc_social_3_hashtag oc_social_3_instance oc_social_3_req_queue oc_social_3_stream oc_social_3_stream_act  oc_social_3_stream_dest oc_social_3_stream_queue oc_social_3_stream_tag\
  > oc_social_3.bak

[nursoda]

Probably true, yet after

occ upgrade
occ db:add-missing-indices

I ended up with the proper tables:

show tables;

oc_social_action
oc_social_actor
oc_social_cache_actor
oc_social_cache_doc
oc_social_client
oc_social_follow
oc_social_hashtag
oc_social_instance
oc_social_req_queue
oc_social_stream
oc_social_stream_act
oc_social_stream_dest
oc_social_stream_queue
oc_social_stream_tag

[benklaasen]

I've just attempted an upgrade from fully up-to-date Nextcloud 27 (patch version 5) to 28.0.1 and I encountered this error.

I'll attempt to apply the steps @nursoda used in https://github.com/nextcloud/social/issues/1871#issuecomment-1864148895.

I'll edit this as I progress.

Edit 0

• Operating System: Linux 5.15.0-91-generic x86_64
• Web server: nginx 1.25.1
• Database: mysql 10.6.12
• PHP: 8.1.2
• Nextcloud 27.n.5 -> 28.0.1

Edit 1

I had to delete the following indexes to be able to proceed with the installation. I tried to do an occ update from the command line after each drop index.

MariaDB [nextcloud]\> drop index sh on oc_social_stream_tag;
MariaDB [nextcloud]\> drop index afoa on oc_social_follow;
MariaDB [nextcloud]\> drop index aoa on oc_social_follow;
MariaDB [nextcloud]\> drop index object_id_prim on oc_social_3_stream;
MariaDB [nextcloud]\> drop index attributed_to_prim on oc_social_3_stream;
MariaDB [nextcloud]\> drop index in_reply_to_prim on oc_social_3_stream;
MariaDB [nextcloud]\> drop index ipoha on oc_social_3_stream;
MariaDB [nextcloud]\> drop index nid on oc_social_3_cache_actor;

On attempting to drop that last one, I got the error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

I had no idea what to do with that; a search for part of the string on this forum didn't return any hits, so I went for the big stick, as outlined by @DivineDominion in https://github.com/nextcloud/social/issues/1871#issuecomment-1862876705

MariaDB [nextcloud]\> drop table oc_social_3_action; drop table oc_social_3_actor; drop table oc_social_3_cache_actor; drop table oc_social_3_cache_doc; drop table oc_social_3_client; drop table oc_social_3_follow; drop table oc_social_3_hashtag; drop table oc_social_3_instance; drop table oc_social_3_req_queue; drop table oc_social_3_stream; drop table oc_social_3_stream_act ; drop table oc_social_3_stream_dest; drop table oc_social_3_stream_queue; drop table oc_social_3_stream_tag;

From there, I was able to complete the upgrade successfully. I disabled maintenance mode and logged in successfully (in Firefox; that's the sole browser I use with Nextcloud). The admin status page indicated accumulating errors in the log. I investigated; these were related to the unsupported app EmlViewer, so I disabled that. (I've saved the complete stack trace, if anyone's interested.)

Now, however, the "Activity" page doesn't render (apart from the background and app icon header). Nor does it render in the Admin settings "Activity" view. It renders fine on the Android Nextcloud app, and is fully interactive. I removed all cached cookies and files in Firefox and logged in again, but the issue remains.

In the "Files" app, "All Files" works as it should. "Recent" displays a correct list, but on clicking any of the files I get the following error: The clue might be in the cookie trail: I don't expect to see the directory "files" in there.

• "Shares" works correctly.
• "Tags" works correctly.
• "External storage" isn't enabled. (I'll tell you sometime about my attempts to use B2 with the S3 connector. brrrrrrr.)

When I open the "Files" app from the header icon, the URL looks like this: https://next.fluidlogic.io/apps/files/ The page content is rendered correctly.

When I click on "All Files", the URL turns into this: https://next.fluidlogic.io/apps/files/files I see the same content as in the link above.

Opening a file from either view lands me on a URL like this: https://next.fluidlogic.io/apps/files/files/1368447?dir=/Books%2C%20Articles%20and%20Papers&openfile=true

When my backup has completed overnight, I'll pull down the latest database snapshot and compare it with the previous one and see if file paths are mangled somewhere. Curious to hear from folks who've also encountered this.

I've successfully upgraded this instance all the way from Nextcloud 13, addressing errors as they arose. Up until now it's been as flawless as Nextcloud gets - all apps work correctly within the expected constraints and the foundation is solid.

I know this is a bit rambling; it's more of an FYI, but I suspect it does point to some deeper issue with the upgrade from 27 to 28.

[Skalli]

I'm running into the same issue. Update failed from 28.0.0 to 28.0.1 as well. Exception: Database error when running migration 28000Date20230906104802 for app core Index name "afoa" for table "oc_social_3_follow" collides with the constraint on table "oc_social_follow". I'd prefer not to fiddle with the database and corrupting it further, but I can't access the instance either as it awaits the occ upgrade command which fails.

[Daedalus3]

Same issue here.