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:

social/lib/Db/CoreRequestBuilder.php

Lines 60 to 74 in dc640af

	public const TABLE_ACTIONS = 'social_action';
	public const TABLE_ACTORS = 'social_actor';
	public const TABLE_CACHE_ACTORS = 'social_cache_actor';
	public const TABLE_CACHE_DOCUMENTS = 'social_cache_doc';
	public const TABLE_CLIENT = 'social_client';
	public const TABLE_FOLLOWS = 'social_follow';
	public const TABLE_HASHTAGS = 'social_hashtag';
	public const TABLE_INSTANCE = 'social_instance';
	public const TABLE_NOTIFICATION = 'social_notif';
	public const TABLE_REQUEST_QUEUE = 'social_req_queue';
	public const TABLE_STREAM = 'social_stream';
	public const TABLE_STREAM_ACTIONS = 'social_stream_act';
	public const TABLE_STREAM_DEST = 'social_stream_dest';
	public const TABLE_STREAM_QUEUE = 'social_stream_queue';
	public const TABLE_STREAM_TAGS = 'social_stream_tag';

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 #1871 (comment).

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 #1871 (comment)

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.

[Githopp192]

run into the same issue, when ### updating nc from 28.0.8 to nc 29.0.4:

Update app deck from App Store
Update app end_to_end_encryption from App Store
Update app files_automatedtagging from App Store
Update app files_photospheres from App Store
Update app flow_notifications from App Store
Update app groupfolders from App Store
Update app imageconverter from App Store
Update app impersonate from App Store

InvalidArgumentException: Index name "afoa" for table "oc_social_3_follow" collides with the constraint on table "oc_social_follow".

Update failed
Maintenance mode is kept active
Resetting log level

[bcutter]

No solution yet? Same when updating from NC v28.0.9.1 to v28.0.10.1.

What is safe to delete after the app has been uninstalled? Would be great so see a list of tables and indices.

[Githopp192]

had to run to solve:

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;