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 |
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! |
|
While updating from nextcloud 28 to 29 I do get the following error: As a database I use mariadb. In my database both tables 'duyN_duyN_appconfig_mlvqw57nn8420' and 'duyN_duyN_appconfig_bsstsbru9p9r6' do not exist. Are these intermediate tables for the upgrading process?
|
|
I was able to resolve the issue by myself. |
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: