Exception during upgrade to 28.0.2 from 27.1.6.2: Index colision calendar_rooms_email

ℹ️ Support
, , , , ,
1

Nextcloud version (eg, 20.0.5): 27.1.6.2
Operating system and version (eg, Ubuntu 20.04): Debian GNU/Linux 12 (bookworm)
Apache or nginx version (eg, Apache 2.4.25): Apache/2.4.57 (Debian)
PHP version (eg, 7.4): PHP 8.2.7

The issue you are facing:

Exception during upgrade to 28.0.2 from 27.1.6.2 .

Exception: Database error when running migration 28000Date20230906104802 for app core Index name "calendar_rooms_email" for table "calendar_rooms" collides with the constraint on table "oc_calendar_rooms".

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

Steps to replicate it:

I used the web interface to upgrade from 27.1.6.2 to 28.0.2 .

The output of your Nextcloud log in Admin > Logging: Not accessible.

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

<?php
$CONFIG = array (
  'enable_previews' => false,
  'default_phone_region' => 'FR',
  'instanceid' => 'ID',
  'passwordsalt' => 'SALT',
  'datadirectory' => '/var/www/nextcloud-data',
  'dbtype' => 'mysql',
  'version' => '27.1.6.2',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => '',
  'mysql.utf8mb4' => true,
  'dbuser' => 'nextcloud',
  'dbpassword' => 'pwd',
  'installed' => true,
  'loglevel' => 0,
  'theme' => '',
  'maintenance' => false,
  'preview_libreoffice_path' => '/usr/bin/libreoffice',
  'trusted_proxies' =>
  array (
    0 => 'IP1',
    1 => 'ÏP2',
    2 => '192.168.0.254',
    3 => '192.168.0.112',
    4 => '127.0.0.1',
    5 => '::1',
  ),
  'trusted_domains' =>
  array (
    0 => 'cloud.example.com',
    1 => 'cloud0.example.com',
    2 => 'cloud1.example.com',
    3 => 'cloud2.example.com',
  ),
  'mail_smtpmode' => 'smtp',
  'secret' => 'SECRET',
  'forcessl' => true,
  'trashbin_retention_obligation' => 'auto',
  'memcache.local' => '\\OC\\Memcache\\Redis',
  'overwrite.cli.url' => 'https://cloud.example.com',
  'mail_smtpsecure' => 'ssl',
  'mail_sendmailmode' => 'smtp',
  'mail_from_address' => 'cloud',
  'mail_domain' => 'example.com',
  'mail_smtpauth' => 1,
  'mail_smtpauthtype' => 'LOGIN',
  'mail_smtphost' => 'mail.example.com',
  'mail_smtpport' => '465',
  'mail_smtpname' => 'mail@example.com',
  'mail_smtppassword' => 'password',
  'simpleSignUpLink.shown' => false,
  'app_install_overwrite' =>
  array (
    0 => 'ocsms',
    1 => 'joplin',
  ),
  'redis' =>
  array (
    'host' => 'localhost',
    'port' => 6379,
  ),
  'filelocking.enabled' => true,
  'memcache.locking' => '\\OC\\Memcache\\Redis',
  'updater.secret' => 'secret',
);

The output of your Apache/nginx/system log in /var/log/____: nothing special

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.

→ Nothing that seems useful

2

I am under the impression that some kind of change from tables without the oc_ prefix to tables with the oc_ prefix is underway.
I am delinting a log of prefixes …

image

3

[…] oc_calendar_rooms

I am under the impression that some kind of change from tables without the oc_ prefix to tables with the oc_ prefix is underway.

Not by Nextcloud, but your config and the error suggests it was changed at some point in your environment. Your default prefix has been removed from your config (which is fine), but it also appears you had an active installation prior to changing the prefix… And those tables are still around.

Based on the message and your provided config, if your Nextcloud is working just fine right now, you can probably safely drop all the oc_ tables since at least that Nextcloud (the one you provided the config from) isn’t using any oc_* tables with way you have things configured.

If you’re wondering why this error popped up now, it’s because it’s a new check in v28 that proactively detects this database problem. So it’s quite possible you made this change a long time ago.

P.S. Backup your database beforehand of course.

4

My configuration indicates an empty prefix, so that is ok - I guess that the code now interprets an empty prefix as the default prefix (i.e. ‘’ is interpreted as ‘oc_’).

What I did:

  1. Get a backup of the DB.
  2. Drop the tables starting with ‘oc_’
  3. Get the commands to rename (‘nextcloud’ is the name of the db in table_schema):
    SELECT Concat('ALTER TABLE `', TABLE_NAME, '` RENAME TO `oc_', TABLE_NAME, '`;')
  FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_SCHEMA = 'nextcloud';
  4. Execute those commands
  5. Update the configuration to set the dbtableprefix to ‘oc_’
  6. occ upgrade
  7. occ maintenance:mode --off
  8. occ db:add-missing-indices

Up and running again.

1 Like
5

This is an installation that is quite old, updated from owncloud and I guess I never had a prefix, so the prefix was empty for a good reason.

(and, thank you for your fast feedback, which I read after fixing it already).

6

It’s definitely noteworthy if an empty value started being interpreted differently in v28. I’ll poke around a bit.

I was focusing on the value not being the default, and not thinking about it empty (rather than just something else) in your case. That’s… Weird that you just experienced this. The behavior should not have changed.

7

I did not find how the empty prefix converts to ‘oc_’ for my mariadb, but we have this in Sqlite.php:

                $this->tablePrefix = empty($config['dbtableprefix'])
                        ? ConnectionFactory::DEFAULT_DBTABLEPREFIX
                        : $config['dbtableprefix'];

Some refactoring is needed - we find both the hardcoded ‘oc_’ and DEFAULT_DBTABLEPREFIX .

8

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