Database error when upgrading from 27.1.6. to 28.0.2

hemelingertrinker · February 11, 2024, 11:05am

Support intro

Sorry to hear you’re facing problems

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can

Nextcloud version (eg, 20.0.5): 27.1.6
Operating system and version (eg, Ubuntu 20.04): Ubuntu 22.04.3
Apache or nginx version (eg, Apache 2.4.25): Apache/2.4.52 (Ubuntu)
PHP version (eg, 7.4): PHP 8.1.27

The issue you are facing: Database error when updating from 27.1.6 to 28.0.2

Is this the first time you’ve seen this error? (Y/N): Y

Steps to replicate it:

Run either sudo -u www-data php /var/www/nextcloud/updater/updater.phar --no-interaction or use the web-based updater

The output of your Nextcloud log in Admin > Logging: console output:

brmstr@nextcloud:/var/www/nextcloud$ sudo -u www-data php /var/www/nextcloud/updater/updater.phar --no-interaction
[sudo] password for brmstr: 
Nextcloud Updater - version: v27.1.0rc3-15-gfa7af53

Current version is 27.1.6.

Update to Nextcloud 28.0.2 available. (channel: "stable")
Following file will be downloaded automatically: https://download.nextcloud.com/server/releases/nextcloud-28.0.2.zip
Open changelog ↗

Updater run in non-interactive mode.

Start update

Info: Pressing Ctrl-C will finish the currently running step and then stops the updater.

[✔] Check for expected files
[✔] Check for write permissions
[✔] Create backup
[✔] Downloading
[✔] Verify integrity
[✔] Extracting
[✔] Enable maintenance mode
[✔] Replace entry points
[✔] Delete old files
[✔] Move new files in place
[✔] Done

Update of code successful.
Updater run in non-interactive mode - will start "occ upgrade" now.

Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Setting log level to debug
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 "user_id" for table "oc_mail_accounts" collides with the constraint on table "oc_mail_attachments".
Update failed
Maintenance mode is kept active
Resetting log level

Updater run in non-interactive mode - will disable maintenance mode now.
Nextcloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Maintenance mode disabled

Maintenance mode is disabled

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

<?php
$CONFIG = array (
  'instanceid' => 'ocXXX',
  'passwordsalt' => 'XXX',
  'secret' => 'XXX',
  'trusted_domains' =>
  array (
    0 => 'nc.XXX.org',
    1 => 'XXX.XXX.XXX.XXX',
    2 => 'localhost',
    3 => '192.168.0.101',
    4 => '192.168.3.11',
  ),
  'trusted_proxies' =>
  array (
    0 => '192.168.3.11',
  ),
  'datadirectory' => '/var/www/nextcloud/data',
  'dbtype' => 'mysql',
  'version' => '27.1.6.2',
  'overwrite.cli.url' => 'https://XXX.org',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'nextcloud',
  'dbpassword' => 'XXX',
  'installed' => true,
  'maintenance' => false,
  'theme' => '',
  'log_type' => 'file',
  'logfile' => '/var/log/nextcloud.log',
  'loglevel' => '2',
  'log_rotate_size' => 104857600,
  'overwritehost' => 'XXX',
  'overwriteprotocol' => 'https',
  'overwritecondaddr' => '^192\\.168\\.3\\.11$',
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'app_install_overwrite' =>
  array (
    0 => 'mindmap_app',
    1 => 'folderplayer',
  ),
  'mysql.utf8mb4' => true,
  'default_phone_region' => 'ISO 3166-2:NO',
  'twofactor_enforced' => 'true',
  'twofactor_enforced_groups' =>
  array (
  ),
  'twofactor_enforced_excluded_groups' =>
  array (
  ),
);

Any help is appreciated. I saw that other users have been facing database errors as well, but none of the solutions worked for me.

jtr · February 11, 2024, 4:55pm

Any chance you used to have the mail app installed but uninstalled or disabled it long ago?

If so an ancient (and no longer compliant and orphaned) set of database tables for it may be left around?

I suspect this because an up-to-date mail app installation I don’t believe will trigger this check.

Your answer will determine how to proceed.

hemelingertrinker · February 12, 2024, 4:19am

It is possible that the mail app was installed once, but it isn’t now.

hemelingertrinker · February 13, 2024, 1:09pm

I was finally able to solve this issue.

I have checked the database: There was one dataset in oc_mail_accounts and no dataset in oc_mail_attachments. The field user_id in both tables was defined to be:

Type: varchar(64)
Null: NO
Key: MUL
Default: NULL

I tried to (re-)install the mail app; the installation failed: “There is no column with name ‘signature’ on table ‘oc_mail_accounts’” - which is true.

Obviously, as @jtr assumed, the left behinds of an earlier mail app installation are the problem. I came across this documentation: https://nextcloud-mail.readthedocs.io/en/latest/developer/#resetting-the-app

Since I don’t use the mail app, I deleted all related tables:

DELETE FROM oc_appconfig WHERE appid = 'mail';
DELETE FROM oc_migrations WHERE app = 'mail';
DROP TABLE oc_mail_accounts;
DROP TABLE oc_mail_aliases;
DROP TABLE oc_mail_coll_addresses;
DROP TABLE oc_mail_attachments;
DROP TABLE oc_mail_mailboxes;
DROP TABLE oc_mail_messages;
DROP TABLE oc_mail_recipients;
DROP TABLE oc_mail_classifiers;
DROP TABLE oc_mail_trusted_senders;
DROP TABLE oc_mail_tags;
DROP TABLE oc_mail_message_tags;
DROP TABLE oc_mail_provisionings;
DROP TABLE oc_mail_local_messages;

After that I was able to upgrade to 28.0.2.

system · February 21, 2024, 1:09pm

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