Database migration exception at update 27.1.6 -> 28.0.something

flat_eric1 · February 14, 2024, 7:46pm

Nextcloud version (eg, 20.0.5): 27.1.6
Operating system and version (eg, Ubuntu 20.04): Debian stable 12.5
Apache or nginx version (eg, Apache 2.4.25): Apache 2.4.57-2
PHP version (eg, 7.4): 8.2.7

The issue you are facing:

tl;dr-description: Update fails with database migration error for update from 27.1.6 to 28.0.0, 28.0.1 or 28.0.2.

Detailed description below.

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

Steps to replicate it:

Update the system using Web updater or CLI based update from 27.1.6 to one of the available 28 versions

The output of your Nextcloud log in Admin > Logging:

Cannot get that output from the nextcloud web interface, as the nextcloud instance is fully dysfunctional after failed update.

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

<?php
$CONFIG = array (
  'instanceid' => 'oczc4f7lucfe',
  'passwordsalt' => '<redacted>',
  'secret' => '<redacted>',
  'trusted_domains' => 
  array (
    0 => '<redacted>',
    1 => '<redacted>',
  ),
  'datadirectory' => '/srv/www/htdocs/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '27.1.6.2',
  'overwrite.cli.url' => 'https://<redacted>',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbtableprefix' => 'oc_',
  'dbuser' => '<redacted>',
  'dbpassword' => '<redacted>',
  'logtimezone' => 'UTC',
  'installed' => true,
  'updater.release.channel' => 'stable',
  'tempdirectory' => '/srv/www/htdocs/nextcloud/data/tmp',
  'loglevel' => '2',
  'maintenance' => false,
  'mysql.utf8mb4' => true,
  'enable_previews' => true,
  'app_install_overwrite' => 
  array (
    0 => 'calendar',
  ),
  'mail_smtpmode' => 'sendmail',
  'mail_smtpsecure' => 'tls',
  'mail_sendmailmode' => 'smtp',
  'mail_from_address' => 'nextcloud',
  'mail_domain' => '<redacted>',
  'default_phone_region' => 'DE',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'memcache.distributed' => '\\OC\\Memcache\\Redis',
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'redis' => 
  array (
    'host' => '/var/run/redis/redis-server.sock',
    'port' => 0,
    'dbindex' => 0,
    'password' => '<redacted>',
    'timeout' => 1.5,
  ),
  'theme' => '',
  'updater.secret' => '<redacted>',
);

The output of your Apache/nginx/system log in /var/log/apache2/access-nextcloud:

[...]
0.0.0.0 - - - [14/Feb/2024:20:04:26 +0100] "GET /ocs/v2.php/apps/notifications/api/v2/notifications HTTP/1.1" 304 5797 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122
.0"
0.0.0.0 - - - [14/Feb/2024:20:04:56 +0100] "GET /ocs/v2.php/apps/notifications/api/v2/notifications HTTP/1.1" 304 5797 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:11 +0100] "GET /index.php/apps/updatenotification/credentials HTTP/1.1" 200 5891 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:12 +0100] "POST /updater/ HTTP/1.1" 200 6206 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:12 +0100] "GET /favicon.ico HTTP/1.1" 404 13426 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:19 +0100] "POST /updater/index.php HTTP/1.1" 200 5488 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:19 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:19 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:25 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:31 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:31 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:34 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:34 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:34 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:36 +0100] "POST /updater/index.php HTTP/1.1" 200 417 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:48 +0100] "POST /updater/index.php HTTP/1.1" 200 5488 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:48 +0100] "POST /updater/index.php HTTP/1.1" 200 554 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET / HTTP/1.1" 200 7426 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /core/css/guest.css?v=ed997c37-12 HTTP/1.1" 200 5214 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /core/js/update.js?v=ed997c37-12 HTTP/1.1" 200 2297 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /core/img/logo/logo.svg HTTP/1.1" 200 6338 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /apps/theming/img/background/kamil-porembinski-clouds.jpg HTTP/1.1" 200 106709 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /dist/core-main.js?v=ed997c37-12 HTTP/1.1" 200 285315 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /dist/core-common.js?v=ed997c37-12 HTTP/1.1" 200 887148 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /core/img/favicon-touch.png HTTP/1.1" 200 3139 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:49 +0100] "GET /core/img/favicon.ico HTTP/1.1" 200 3863 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:53 +0100] "GET /core/img/actions/caret-white.svg?v=1 HTTP/1.1" 200 5678 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:53 +0100] "GET /core/ajax/update.php?requesttoken=OSoD6pqglCbO%2BtRvY8znQiS1SxqvFW3H35BvQjxprLw%3D%3AEmBBpuDK%2BHamm6cbW7WWMG7WIl%2BcfxuLttNXL3k95Os%3D HTTP/1.1" 200 6947 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:10:53 +0100] "GET /core/img/actions/error-white.svg?v=1 HTTP/1.1" 200 823 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
0.0.0.0 - - - [14/Feb/2024:20:22:50 +0100] "GET /index.php/csrftoken HTTP/1.1" 200 12361 "-" "Mozilla/5.0 (Windows NT 10.0; rv:122.0) Gecko/20100101 Firefox/122.0"
[...]

In /var/log/apache2/error-nextcloud.log:

[Wed Feb 14 20:03:25.138437 2024] [access_compat:error] [pid 1645] [client <my_ipv6_address_here>:50364] AH01797: client denied by server configuration: /srv/www/htdocs/nextcloud/data/.ocdata

Output errors in nextcloud.log in /var/www/ or as admin user in top right menu, filtering for errors. Use a pastebin service if necessary.

see https://185.135.137.124/nextcloud.log

Detailed description of my debugging adventures:

I am running 5 nextcloud instances, that are (pretty much) the same.
All were running on Nextcloud 27.1.6, all running on the same version of Debian linux.
Except very few apps like talk and mail, they all share the same apps being installed.

There are 2 test instances, 1 semi-productive and 2 production instances.

I was offered an udpate to Version 28.0.1, so I gave it a chance and updated the test instances first without any hassle, then I updated the semi-prod instance without any issues. One of the production instances updated as well, but when updating the second production instance, I get this error:

"
Exception: Database error when running migration 28000Date20230906104802 for app core Index name “appconfig_config_key_index” for table “oc_appconfig” collides with the constraint on table “oc_oc_appconfig_ikcj25dg478nf”.
Detailed logs

Preparing update

Set log level to debug

Turned on maintenance mode

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 “appconfig_config_key_index” for table “oc_appconfig” collides with the constraint on table “oc_oc_appconfig_ikcj25dg478nf”.

The update was unsuccessful. Please report this issue to the Nextcloud community
"

I did quite extensive debugging, but nothing helped. What I tried so far, all without success:

Restoring to different Database backups with point-in-time backups
running updates through command line instead of Web GUI
occ maintenance:repair
updated to 28.0.0
updated to 28.0.2
updated all apps to most recent version before updating to 28.0.1
set apps to exactly the same installation base as one of the successfully updated instances
examined all existing constraints on table oc_appconfig, but cannot find any
tried to cleanup the table “oc_appconfig” from suspicious database entries like those with special characters or other things that I could think of being problematic
tried to uninstall apps and cleanup table “oc_appconfig” from their database entries until it looked exactly the same as on other instances

Nothing helped, it always results in the same error message posted above.

After each failed update attempt I fully restored the nextcloud directory from a full backup (including data directory) and restored the DB from a full backup too, to get back to at least a working 27.1.6 instance.

What else can I do to make this error go away? How can I update to 28?

Any help will be appreciated. I can provide as many logs as needed, and luckily, this instance is only used by me and my wife. So I can get a downtime for testing purposes within minutes and I did get to full backup-and-restore cycles

jtr · February 14, 2024, 7:54pm

Judging by your provided configuration, all of your real database tables are oc_xxx, but it looks like you have some orphan ones that aren’t part of your real environment. These just need to be cleaned up. The one mentioned in this message is oc_oc_appconfig_ikcj25dg478nf which is almost certainly a bogus one (further confirmed since the one it has a collision with is the oc_appconfig which is the one I’d expect given your config).

Backup of course, but should be as simply as dropping the oc_oc_appconfig_ikcj25dg478nf table.

That table may not have been a recent arrival in your environment, but a recent check was added for this situation because it creates various problems. I mention that in case you’re surprised you’re hitting this now, since there’s a chance that table is like 5 years old and not been a problem until now.

Incidentally, the check has been made a little softer at upgrade time in future versions (when possible), but it still needs to get cleaned up so what I said above still stands.

flat_eric1 · February 14, 2024, 8:04pm

Hi!

Wow, never expected a reply THIS fast

Indeed - this table did exist. I must have missed that this table actually existed permanently and I have assumed that this is just a working or temporary table during the update procedure.
It is a bit embarassing to not having recognized this table before - i missed the obvious here for quite some days…

However, it works! You made my day - thank you so much!

Best regards,

Thomas

system · February 22, 2024, 8:04pm

This topic was automatically closed 8 days after the last reply. New replies are no longer allowed.