Upgrade from MIAB 57a to 61.1 failing during run of sudo mailinabox after duplicity restore on nextcloud step

[tdahbura]

I tried a migration today from 57a to 61.1 (using a new ubuntu host) and after doing the restore from my backups off my 57a box using duplicity and then running a final sudo mailinabox I received the following output and a crash:

I backed up the 57a system, Created a new 61.1 system on a new host Copied the backup files to the new 61.1 system and did a restore.

I then ran a sudo mailinabox command on the new box after the restore completed and here is the output:

Mail-in-a-Box Version: v61.1

Updating system packages... Installing system packages... Initializing system random number generator... Firewall is active and enabled on system startup Synchronizing state of fail2ban.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install enable fail2ban Installing nsd (DNS server)... Installing Postfix (SMTP server)... Installing Dovecot (IMAP server)... Installing OpenDKIM/OpenDMARC... Installing SpamAssassin... bayes: synced databases from journal in 0 seconds: 2828 unique entries (3150 total entries) Installing Nginx (web server)... Installing Roundcube (webmail)... Updating database schema (2021081000)... [OK] Updating database schema (2021100300)... [OK] Installing Nextcloud (contacts/calendar)... Upgrading Nextcloud --- backing up existing installation, configuration, and database to directory to /home/user-data/owncloud-backup/2023-01-29-11:58:28...

Upgrading to Nextcloud version 21.0.7

An unhandled exception has been thrown: Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 11 database disk image is malformed in /usr/local/lib/owncloud/lib/private/DB/Connection.php:85 Stack trace:

0 /usr/local/lib/owncloud/3rdparty/doctrine/dbal/src/Connection.php(1519): OC\DB\Connection->connect()

https://github.com/mail-in-a-box/mailinabox/pull/1 /usr/local/lib/owncloud/3rdparty/doctrine/dbal/src/Connection.php(1041): Doctrine\DBAL\Connection->getWrappedConnection() https://github.com/mail-in-a-box/mailinabox/issues/2 /usr/local/lib/owncloud/lib/private/DB/Connection.php(231): Doctrine\DBAL\Connection->executeQuery() https://github.com/mail-in-a-box/mailinabox/issues/3 /usr/local/lib/owncloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(345): OC\DB\Connection->executeQuery() https://github.com/mail-in-a-box/mailinabox/issues/4 /usr/local/lib/owncloud/lib/private/DB/QueryBuilder/QueryBuilder.php(287): Doctrine\DBAL\Query\QueryBuilder->execute() https://github.com/mail-in-a-box/mailinabox/pull/5 /usr/local/lib/owncloud/lib/private/AppConfig.php(345): OC\DB\QueryBuilder\QueryBuilder->execute() https://github.com/mail-in-a-box/mailinabox/issues/6 /usr/local/lib/owncloud/lib/private/AppConfig.php(110): OC\AppConfig->loadConfigValues() https://github.com/mail-in-a-box/mailinabox/issues/7 /usr/local/lib/owncloud/lib/private/AppConfig.php(301): OC\AppConfig->getApps() https://github.com/mail-in-a-box/mailinabox/issues/8 /usr/local/lib/owncloud/lib/private/legacy/OC_App.php(975): OC\AppConfig->getValues() https://github.com/mail-in-a-box/mailinabox/issues/9 /usr/local/lib/owncloud/lib/private/Server.php(682): OC_App::getAppVersions() https://github.com/mail-in-a-box/mailinabox/issues/10 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(155): OC\Server->OC{closure}() https://github.com/mail-in-a-box/mailinabox/issues/11 /usr/local/lib/owncloud/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility{closure}() https://github.com/mail-in-a-box/mailinabox/pull/12 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(122): Pimple\Container->offsetGet() https://github.com/mail-in-a-box/mailinabox/issues/13 /usr/local/lib/owncloud/lib/private/ServerContainer.php(137): OC\AppFramework\Utility\SimpleContainer->query() https://github.com/mail-in-a-box/mailinabox/issues/14 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query() https://github.com/mail-in-a-box/mailinabox/issues/15 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(177): OC\AppFramework\Utility\SimpleContainer->get() https://github.com/mail-in-a-box/mailinabox/issues/16 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(155): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility{closure}() https://github.com/mail-in-a-box/mailinabox/issues/17 /usr/local/lib/owncloud/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility{closure}() https://github.com/mail-in-a-box/mailinabox/issues/18 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(122): Pimple\Container->offsetGet() https://github.com/mail-in-a-box/mailinabox/issues/19 /usr/local/lib/owncloud/lib/private/ServerContainer.php(137): OC\AppFramework\Utility\SimpleContainer->query() https://github.com/mail-in-a-box/mailinabox/issues/20 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query() https://github.com/mail-in-a-box/mailinabox/pull/21 /usr/local/lib/owncloud/lib/private/Server.php(1041): OC\AppFramework\Utility\SimpleContainer->get() https://github.com/mail-in-a-box/mailinabox/issues/22 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(155): OC\Server->OC{closure}() https://github.com/mail-in-a-box/mailinabox/issues/23 /usr/local/lib/owncloud/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility{closure}() https://github.com/mail-in-a-box/mailinabox/issues/24 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(122): Pimple\Container->offsetGet() https://github.com/mail-in-a-box/mailinabox/pull/25 /usr/local/lib/owncloud/lib/private/ServerContainer.php(137): OC\AppFramework\Utility\SimpleContainer->query() https://github.com/mail-in-a-box/mailinabox/issues/26 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query() https://github.com/mail-in-a-box/mailinabox/issues/27 /usr/local/lib/owncloud/lib/private/Server.php(2000): OC\AppFramework\Utility\SimpleContainer->get() https://github.com/mail-in-a-box/mailinabox/issues/28 /usr/local/lib/owncloud/lib/private/Files/View.php(119): OC\Server->getLockingProvider() https://github.com/mail-in-a-box/mailinabox/pull/29 /usr/local/lib/owncloud/lib/private/Server.php(430): OC\Files\View->__construct() https://github.com/mail-in-a-box/mailinabox/issues/30 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(155): OC\Server->OC{closure}() https://github.com/mail-in-a-box/mailinabox/issues/31 /usr/local/lib/owncloud/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility{closure}() https://github.com/mail-in-a-box/mailinabox/issues/32 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(122): Pimple\Container->offsetGet() https://github.com/mail-in-a-box/mailinabox/issues/33 /usr/local/lib/owncloud/lib/private/ServerContainer.php(137): OC\AppFramework\Utility\SimpleContainer->query() https://github.com/mail-in-a-box/mailinabox/pull/34 /usr/local/lib/owncloud/lib/private/AppFramework/Utility/SimpleContainer.php(56): OC\ServerContainer->query() https://github.com/mail-in-a-box/mailinabox/issues/35 /usr/local/lib/owncloud/lib/private/Server.php(1363): OC\AppFramework\Utility\SimpleContainer->get() https://github.com/mail-in-a-box/mailinabox/issues/36 /usr/local/lib/owncloud/lib/base.php(588): OC\Server->boot() https://github.com/mail-in-a-box/mailinabox/issues/37 /usr/local/lib/owncloud/lib/base.php(1076): OC::init() https://github.com/mail-in-a-box/mailinabox/pull/38 /usr/local/lib/owncloud/console.php(49): require_once('...') https://github.com/mail-in-a-box/mailinabox/issues/39 /usr/local/lib/owncloud/occ(11): require_once('...')

$

[tdahbura]

in digging into this further the command

sudo -u www-data php8.0 /usr/local/lib/owncloud/occ upgrade

seems to be generating the error?

Doctrine\DBAL\Exception: Failed to connect to the database: An exception occurred while executing a query: SQLSTATE[HY000]: General error: 11 database disk image is malformed in /usr/local/lib/owncloud/lib/private/DB/Connection.php:85 Stack trace:

0 /usr/local/lib/owncloud/3rdparty/doctrine/dbal/src/Connection.php(1519): OC\DB\Connection->connect()

1 /usr/local/lib/owncloud/3rdparty/doctrine/dbal/src/Connection.php(1041): Doctrine\DBAL\Connection->getWrappedConnection()

2 /usr/local/lib/owncloud/lib/private/DB/Connection.php(231): Doctrine\DBAL\Connection->executeQuery()

3 /usr/local/lib/owncloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(345): OC\DB\Connection->executeQuery()

4 /usr/local/lib/owncloud/lib/private/DB/QueryBuilder/QueryBuilder.php(287): Doctrine\DBAL\Query\QueryBuilder->execute()

5 /usr/local/lib/owncloud/lib/private/AppConfig.php(345): OC\DB\QueryBuilder\QueryBuilder->execute()

6 /usr/local/lib/owncloud/lib/private/AppConfig.php(110): OC\AppConfig->loadConfigValues()

[rfrush]

I completed this upgrade (57a to 61.1) this weekend as well, but did not run into the Owncloud upgrade issue you're seeing. This suggests the restored DB has an issue.

If you can run the same command (occ upgrade) on the 57a and see if the problem existed on the Source db?

[tdahbura]

if it does give an error what does that mean on my 57a machine which is running my production mail server?

I did do a sudo mailinabox on that machine and did not see any errors...

[tdahbura]

I ran a restore to a new 57a box and this error did not occur. In other words the backup from my production 57a server and a restore to a new 57a server resulted in no errors like the restore to 61.1 did.

[rfrush]

Have you tried re-running your backup/upgrade/restore process? Is it possible you had either a bad backup or restore that borked the upgrade? If it continues to fail after doing the complete process again, then it stinks of some bad data in the DB, and we'd need input from someone with deeper experience to figure out how to rescue from this. If you're not using calendar/contacts/files from OwnCloud, there may be a way to reset the SQL database to factory and move on with your life.

[nomandera]

Could someone test a backup by spinning up a throw away virtual machine locally and importing a backup?

As far as I cant think there should be no issue with doing this as a test since nothing is published by default outbound and no one would be trying to connect to it as server.

If so this might be a good practice for anyone wanting to make doubly sure backup and restore will work given it would be a very rare occurrence.

Also useful for anyone coming in late to the party long after most people have migrated.

[tdahbura]

It is interesting after installing v61 prior to the backup restore how it says Nextcloud is the latest version, after the restore and re-run of sudo mailinabox it tries to upgrade Nextcloud again.

Both v57a and v61 seem to already be running the latest versions of Nextcloud. It seems like the restore operation is causing the sudo mailinabox command after the restore to think the Nextcloud version is old?

I do not use calendar/contacts/files from Nextcloud so is there a clean way to get this error corrected and hopefully allow the migrated 57a to run without issues?

[tdahbura]

If you're not using calendar/contacts/files from OwnCloud, there may be a way to reset the SQL database to factory and move on with your life.

If so I would like to know how to do this, as it is blocking my ability to migrate to 61 from 57a. Any help would be great and much appreciated!

[extric99]

Maybe this can help

https://discourse.mailinabox.email/t/contacts-not-loading-anymore/2475

[tdahbura]

Some further updates on migration state:

1. Backed up my production MIAB 57a instance again..using the management/backup.py command
2. Moved all the backup files to a new Ubuntu 18.04 instance and installed MIAB 57a on it - let's call this host2.
3. Restored the backup to this box and restore went without errors and no complaints. All data seemed to be in a good place.
4. Performed a backup of this new instance using the management/backup.py command
5. Setup a new Ubuntu 22.04 host and installed MIAB 61.1 on this host - let's call this host3
6. Took the backups from the 57a host2 and moved them to host3
7. Performed a restore on this host of host2's backup
8. Performed a final sudo mailinabox on host3 after the restore and all operations seemed to go well including a series of ugprades of Nextcloud with a couple of messages about incompatible app: calendar and contacts and user_external

At the bottom of the Nextcloud run I see the following: All tables already up to date! Nextcloud is already latest version Error: stepping, UNIQUE constraint failed: oc_users_external.uid, oc_users_external.backend (19) Installing Z-Push (Exchange/ActiveSync server)...

I am not sure what to do-my backups work fine restoring to a new 57a, it is just the 57a to 61.1 that is not working?

Attached the entire output of the sudo mailinabox after the restore as a file if it helps? Really would like to migrate to 61.1...

sudo_mailinabox_output.txt

[tdahbura]

A repeated run of sudo mailinabox on host3 gives just the following output:

Primary Hostname: box.fullmoonmanor.net Public IP Address: xx.xx.xx.xx Private IP Address: xxx.xxx.xxx.xxx Mail-in-a-Box Version: v61.1

Updating system packages... Installing system packages... Initializing system random number generator... Firewall is active and enabled on system startup Synchronizing state of fail2ban.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install enable fail2ban Installing nsd (DNS server)... Installing Postfix (SMTP server)... Installing Dovecot (IMAP server)... Installing OpenDKIM/OpenDMARC... Installing SpamAssassin... Installing Nginx (web server)... Installing Roundcube (webmail)... Installing Nextcloud (contacts/calendar)... Nextcloud is already latest version Error: stepping, UNIQUE constraint failed: oc_users_external.uid, oc_users_external.backend (19) Installing Z-Push (Exchange/ActiveSync server)... Installing Mail-in-a-Box system management daemon... Installing Munin (system monitoring)... updated DNS: OpenDKIM configuration

---

Your Mail-in-a-Box is running.

[storm255]

I just battled through something very similar yesterday before I saw this thread.

My main problem initially was that my 57a version backup was behind a long way when I restored to the new Ubuntu 22.04LTS and had php 8.0 and 8.2 on it. The version of Nextcloud I had would only just run with 8.0 till I upgraded. I eventually worked around by installing php7.4 and the required libraries, then manually running the upgrades with occ from the commandline.

This was mostly related to my using pdo_mysql extension in php and not the default installs fault I guess. Once I had occ working again I just ran the upgrade manually with the phar updater until I got to a compatible version and let the normal process do its thing.

Sorry I didn't capture the actual commands, but I was trying to get it back up and running as quick as possible.

[kiekerjan]

Instructions to battle the Error: stepping, UNIQUE constraint failed: oc_users_external.uid, oc_users_external.backend error can be found here.