DB error when running migration [...] collides with the constraint [...] #41253
Comments
Miyamoto72
added
0. Needs triage
|
Enter your mysql console -> use nextcloud; (or whatever your database is called) -> drop index 'ts_session' from 'oc_text_steps'; |
|
The code is already changed: nextcloud/text#4553 Apparently an existing index was not renamed: nextcloud/text#4958 |
joshtrichards
changed the title
Index name "ts_session" for table "oc_talk_sessions" collides with the constraint on table "oc_text_steps"
|
I have an similar issue, with the tables switched, updating from 27.1.3.2 to 28 beta3 I dropped the index from |
solracsf
changed the title
Index name "ts_session" for table "oc_talk_sessions" collides with the constraint on table "oc_text_steps"
|
Another report:
|
(Upgrade 27.7.4 to 28.0.0) |
|
After successfully upgrading from 27.7.3 to 27.7.4 I then proceeded to upgrade to 28.0.0 and also got this error, but also with another index:
I have no idea what to do now. I guess I'll see how to restore my site. |
|
Other seem to have been able to just delete the offending index. Unfortunately, this "nid" index is the AUTO_INCREMENT column. That index is mandatory. |
|
I also have the same issue with different index and table: |
|
I now have dropped the empty table "oc_oc_filecache_wkiceckq6l6vf" and was restarting the upgrade in the console with occ upgrade and the upgrade was running further. |
|
Related to this PR: #39506 for #39488 This new check is for postgres, but it runs against all dbs. That's fine because it should be fixed across the board. The original report's issue was a legit new bug fixed in nextcloud/text#4998 due to the beta (people upgrading from the beta may be impacted). But now this new check is catching a lot of ancient (e.g. unused/uninstalled/disabled) app db tables maybe? That would explain why these aren't already cleaned up. The reason I suspect this is because otherwise every one of these environments would have already been broken prior to this change. |
|
In my case on 28.0.0 release:
Using the above suggestion, this has helped: |
|
Alright been analyzing the reports so far and here's what I've got:
Resulting from beta NC28. Fixed previously and was the original issue this report was about. However out of that a new check was added to proactively prevent that problem in the future (which is what all these new follow-on reports are actually about now that NC28 is out with this check in place). In theory that should have been fine, but a lot of existing installations have tables left from no-longer-installed apps as well as transient tables created for various reasons over the years long forgotten about... and there's where the below come in:
Ditto. Educated guess is this person's environment has had both Office Online and Nextcloud Office installed, but currently only one - or neither - is in-use.
Ditto.
Same situation as the last one. Since we (in
and/or
Edit: There also might be some apps that don't support postgresql and thus ignore these constraints. So possibly a third block of people, but I'm not sure how many apps fall into that category these days. |
|
Thank you @joshtrichards, while I'm comfortable with a lot of IT systems, I wouldn't be comfortable with doing such an analysis. I took your research result regarding "oc_social_3" and just removed all of those tables. The upgrade then finished successfully. (Now I have funny "Zend OPcache can't be temporary enabled" messages, that are flooding the logs, but I'll research that separately.) |
|
I had the same issue for oc_social_3_* tables. And the upgrade went fine. At least, after dropping it, upgrade worked without issue. After looking in the code of the social app, I found no table name like that BUT this: Quite old. So @joshtrichards I think you are right on your analysis about these orphaned tables. Thank you for that |
@expressrussian This isn't a bug per se, but the messaging could probably be improved. These are stray tables that shouldn't exist. A lot of the situations posted here are people encountering this due to things like manual adjustments made to their databases outside of Nextcloud (often years ago so long forgotten), failed restore attempts of some sort (best guess based on the weird names of the tables), current or past Nextcloud installations sharing the same database but with different table prefixes configured (not recommended), etc. Most of these tables aren't associated with the live Nextcloud installation or any active apps so we can't write code that blindly just blows them away. A portion of them are also from previously installed and long forgotten apps (sometimes third-party ones) that are no longer installed. We also can't cleanly do much more than detect these tables are there. For example, in your case, it appears you have a table called The error messaging however could, at a minimum, probably be improved. I'm not actively working on this issue, but there may be other ideas for handling this situation in a more friendly manner. But the bottom line is these are local environment situations that have to be handled on a case-by-case basis. |
|
Oh no, I couldn't disagree more. Speak as a maintainer of a huge CMS platform that comes with update hooks as well, I strongly want to state, that this is a showstopper and a terrible bug - if not a bug, it's a badly designed update function. Yes, there are tables in the database that shouldn't be there. But this is not the fault of the Nextcloud user, nor is it under any circumstances the fault of the sysadmin. The cases that we experienced with our clients have always been old apps, that had been installed in the past and no longer exist or have been disabled or uninstalled. The fact, that this is possible, identifies a lack in the apps API of Nextcloud. It allows, that uninstalled apps leave rubbish behind them. So, where is the concept from the Nextcloud framework for a cleanup? Or do you want to tell me that this is not in Nextcloud's responsibility and we should all look after ourselves? OK then, please provide some documentation on how to go about that so that we can clean up but don't break anything. When it comes to the update function, and that is what's causing all of this mess, I would say that at the very least, this update function as part of the Nextcloud code is blindly doing something with all tables it can find. What? That function should be smarter, and only deal with data that it is made for. If that were the case, all this would never have happened. If this comments sound a bit bitter, then it's because I am. Have been following this thread for weeks, since I got hit by this while I installed the update during the announcement session. And Nextcloud doesn't seem to care, that they published a release that breaks customer's installations and now they are telling us, it's not their fault. I can't believe it. |
Neither nor. Drop the index |
|
OK, I do not remember, if i ever created oc_filecache_temp. There is a chance maybe. |
Some of them are.
A few have been, yes, but definitely not all[1].
First, I don't speak for Nextcloud. I'm just one voice. I also wasn't making a blind statement (I did an analysis earlier in this thread as well and an updated one is below). By my count the stats[1] so far are:
So at least half of these have zero connection to apps or anything under the control of
There are some cases of ancient apps that no longer exist or are no longer maintained by third-parties. We can't go back in time and fix those. I'm not saying there isn't room for improvement. I'm saying there are various scenarios that create this situation. [1] An updated analysis of every reported scenario in this issue is below. Here are all of the ones from this issue:
|
You don't. Just leave them, maybe throw warning messages about foreign (not used) tables. Propose the admin to delete them, if they are surely not used by nextcloud.
OK, i took some courage and dropped the "oc_filecache_temp" table. Well, what to do with these 75 errors? Just wait? |
My comment was about, what "we" experienced. And ours had all been from apps provided by the NC app store.
Update hooks need to be provided by maintainers, either of core or from apps, and they address database tables explicitly by their name (plus optional prefix, something we do have as well). While I haven't looked into the update script from NC, the fact that it runs into issues with unknown tables must come from the fact, that it does something with all tables of the current database. Don't see how that should ever help. Just update the tables explicitly and leave all others untouched. |
|
@expressrussian Glad to hear you got it working.
We're not triggering this check on every unused/unknown table. Only ones where there is already a conflict (see #39506). The messaging could use improvement as I already said. I agree. But the check is still going to be triggered where there are conflicts. Otherwise it just leads to later bug reports or help requests when things fail on their own in a different way. I'll leave the rest of your questions, which are unrelated to this Issue, for the Nextcloud Help Forum, but you can look up the syntax elsewhere in the manual or in the |
@jurgenhaas Unfortunately not an option with some databases. For example, in PostgreSQL index names have to be unique inside the same database, not only per table. Since we support multiple database types, we have to keep things consistent even if someone is using, say, MySQL/MariaDB (which doesn't care as long as they're different tables). EDIT: Oops forgot to follow-up on your other part:
Okay, fair enough. Do you recall what apps? That would be useful to know. |
|
Quick summary from my perspective: This Issue overall is tracking an error message that gets triggered by about 2-4 different root causes. Unfortunately, the correct response isn't the same for each of them and we can only handle some of them. At least half of the sample so far we can't do much about on our own. We can:
So for some this is a bug or, at the very least, an area of improvement here in |
As I proposed as an idea in #42753, I would add to that list that this check should not be performed in the middle of the update process, potentially causing unexpected long downtime plus a lot stress as proven in this conversation, but as a pre-check (e.g. in the "Verify integrity" step), before the update can be started at all. Then an admin could without any time pressure investigate how to resolve the (upcoming) conflict. I'm guessing many users are waiting for some minor updates to upgrade their instance to 28.x.x, so I would expect more complaints to come in if the process stays like it currently is. |
|
Hi together, I'm joining the discussion with another example of this migration failing in a (as i can see it) different way to the other problems. This time it is the mail app and the table Investigating this further I have found that it is maybe impossible to resolve this without just dropping all mail tables and reinstalling it? Since the database schema for that table includes the key/index and the foreign key constraint, but I cannot drop the key, and it makes no sense to drop the foreign key:
|
@joshtrichards it was social in our case. See #42173 |
|
Here it was also related to the social app. As we didn't use them (anymore) and the app was removed anyway, I decided to run in my MySQL/MariaDB container to remove any related (and not needed) tables of the app: Maybe it's a good idea to check if there is nothing related to anything beside the social app: |
|
Here is another error example, upgrade from 27.1.6 to 28.0.2. Exception: Database error when running migration 28000Date20230906104802 for app core Deleted the oc_officeonline tables, upgrade completed. Side note, the app using these table was removed previously. |
szaimen
added
2. developing
|
I also received this error, this time with Nextcloud News: I do not believe I ever manually created this table. More analysis is available at nextcloud/news#2582. EDIT: I now believe it is possible that I created the table, though I am still not sure. Anyway, deleting the table worked, and does not seem to have caused any other issues. |
Looked into this one since it's popped up a couple times here. It's a long ago fixed bug. The MS OO integration app was using Any active deployment today uses It should only be popping up in environments where an early version of OO app installed then removed during the first month or so if it's existence (and therefore never received the db migration updates that addressed this in nextcloud/officeonline#6 back in 2020). If OO isn't in use, just drop the |
|
New try with docker nextcloud:28-apache Can I fix this myself? |
|
Unfortunately #43357 is in v28.0.3 not v28.0.2. That is, the fix that makes this a soft fail during an upgrade. But either way you will have to fix this yourself, yes.
Is your The error itself suggests you have It looks a bit like you have two Nextcloud installations pointed at the same database? Or perhaps you did once in the past or something. Or maybe you changed your If you don't have multiple Nextcloud instances using the same database...
AGAIN: Backup your database before doing anything. See https://docs.nextcloud.com for database backup instructions. If you need help verifying your setup because you're not sure about the above - or want a deeper inspection or just need help doing the drop - I suggest taking your query to the Help Forum - https://help.nextcloud.com |
|
Wow, that was a tough road. Looking at your advice I saw almost all tables doubled with the prefix oc_ but without any data (except for oc_migrations with records dating back to 2017 (?)). dbtableprefix is empty. Never ever have I fiddled with the database before. So I have no idea where the oc_-tables come from. After a dump I dropped all of them, ran the upgrade without errors, followed by a repair and disabled maintenance mode. 28.0.2.5 (newer is still not available as docker image) is running fine - well I hope so 😅. Thank you for your help and giving me confidence to drop the tables in question. Peter PS: the upgrade tool did not always mention the oc_-table as conflicting. Sometimes the "real" table was mentioned as the one. So you must not go with the order in the message! |
and others
Bug description
Just found NC 28.0.0 beta available and started the update on the command line. Here's the output of the upgrade process:
Steps to reproduce
Expected behavior
Upgrade running smootly without errors
Installation method
None
Nextcloud Server version
27
Operating system
Debian/Ubuntu
PHP engine version
PHP 8.1
Web server
Nginx
Database engine version
MariaDB
Is this bug present after an update or on a fresh install?
Upgraded to a MAJOR version (ex. 22 to 23)
Are you using the Nextcloud Server Encryption module?
Encryption is Disabled
What user-backends are you using?
Configuration report
{ "system": { "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "example.org", "cloud.example.org", "192.168.1.*", "nextcloud.example.org" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "mysql", "version": "27.1.3.2", "overwrite.cli.url": "http:\/\/localhost", "dbname": "***REMOVED SENSITIVE VALUE***", "dbhost": "***REMOVED SENSITIVE VALUE***", "dbport": "", "dbtableprefix": "oc_", "mysql.utf8mb4": true, "dbuser": "***REMOVED SENSITIVE VALUE***", "dbpassword": "***REMOVED SENSITIVE VALUE***", "installed": true, "activity_expire_days": 14, "auth.bruteforce.protection.enabled": true, "blacklisted_files": [ ".htaccess", "Thumbs.db", "thumbs.db" ], "cron_log": true, "enable_previews": true, "enabledPreviewProviders": [ "OC\\Preview\\PNG", "OC\\Preview\\JPEG", "OC\\Preview\\GIF", "OC\\Preview\\BMP", "OC\\Preview\\XBitmap", "OC\\Preview\\Movie", "OC\\Preview\\PDF", "OC\\Preview\\MP3", "OC\\Preview\\TXT", "OC\\Preview\\MarkDown" ], "filesystem_check_changes": 1, "filelocking.enabled": "true", "htaccess.RewriteBase": "\/", "integrity.check.disabled": false, "knowledgebaseenabled": false, "logfile": "\/mnt\/STOR\/logs\/nextcloud.log", "loglevel": 0, "logtimezone": "Europe\/Berlin", "log_rotate_size": 104857600, "maintenance": false, "memcache.local": "\\OC\\Memcache\\APCu", "memcache.distributed": "\\OC\\Memcache\\Redis", "redis": { "host": "***REMOVED SENSITIVE VALUE***", "port": 0, "timeout": 0 }, "memcache.locking": "\\OC\\Memcache\\Redis", "overwriteprotocol": "https", "preview_max_x": 1024, "preview_max_y": 768, "preview_max_scale_factor": 1, "quota_include_external_storage": false, "share_folder": "\/Shares", "skeletondirectory": "", "theme": "", "trashbin_retention_obligation": "auto, 7", "updater.release.channel": "beta", "mail_smtpmode": "sendmail", "mail_sendmailmode": "pipe", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "mail_smtpauthtype": "LOGIN", "mail_smtpauth": 1, "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpport": "587", "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "mail_smtpsecure": "tls", "default_phone_region": "DE", "app_install_overwrite": [ "dicomviewer", "files_mindmap", "carnet", "onlyoffice", "richdocuments", "richdocumentscode", "documentserver_community", "files_photospheres", "telephoneprovider", "audioplayer", "audioplayer_editor", "extract", "tasks", "ocsms", "files_trackdownloads", "ocdownloader", "twofactor_u2f", "checksum", "event_update_notification", "mindmap_app", "contacts", "deck", "spreed", "twofactor_webauthn", "maps" ], "has_rebuilt_cache": true } }List of activated Apps
Nextcloud Signing status
No response
Nextcloud Logs
Additional info
No response
The text was updated successfully, but these errors were encountered: