I have tried numerous times to upgrade MIAB from v64 to v67. The only error I am seeing is
Error: stepping, UNIQUE constraint failed: oc_users_external.uid, oc_users_external.backend
Perusing the Forum indicates this can be ignored. So that leaves me with no obvious sources of problems.
I wouldn’t ignore this error message, as it could raise errors in the future.
If you run the following command:
sudo sqlite3 /home/user-data/owncloud/owncloud.db "SELECT * FROM oc_users_external;"
you’ll see there are duplicate users in the database.
user1@domain.tld|{127.0.0.1:993/imap/ssl/novalidate-cert}|
user2@domain.tld|{127.0.0.1:993/imap/ssl/novalidate-cert}|
user3@domain.tld|{127.0.0.1:993/imap/ssl/novalidate-cert}|
user4@domain.tld|{127.0.0.1:993/imap/ssl/novalidate-cert}|
user5@domain.tld|{127.0.0.1:993/imap/ssl/novalidate-cert}|
...
user1@domain.tld|127.0.0.1|
user2@domain.tld|127.0.0.1|
user3@domain.tld|127.0.0.1|
user4@domain.tld|127.0.0.1|
user5@domain.tld|127.0.0.1|
If you don’t see duplicates, then maybe the error is related to something else,
but if you do see duplicates then let’s fix this error, since the solution is simple.
sudo cp /home/user-data/owncloud/owncloud.db /home/user-data/owncloud/owncloud.db.backup
sudo sqlite3 /home/user-data/owncloud/owncloud.db "DELETE FROM oc_users_external WHERE EXISTS (SELECT 1 FROM oc_users_external p2 WHERE oc_users_external.uid = p2.uid AND oc_users_external.rowid > p2.rowid);"
sudo sqlite3 /home/user-data/owncloud/owncloud.db "UPDATE oc_users_external SET backend='127.0.0.1';"
sudo sqlite3 /home/user-data/owncloud/owncloud.db "SELECT * FROM oc_users_external;"
sudo systemctl restart php8.0-fpm.service
If all goes well, you’ll now end up with a clean database with no duplicate entries that could raise errors in the future.
Mail-in-a-Box Version: v64
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)…
wal
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.
Please log in to the control panel for further instructions at:
âś“ All system services are running.
âś“ SSH disallows password-based login.
âś“ System software is up to date.
A new version of Mail-in-a-Box is available. You are running version v64. The latest version is v67. For upgrade instructions, see https://mailinabox.email.
Does your entries look something like this:
user1@domain.tld|{127.0.0.1:993/imap/ssl/novalidate-cert}|
If so, you should only do step 3 and 5.
Step 3 fails:
Error: stepping, UNIQUE constraint failed: oc_users_external.uid, oc_users_external.backend (19)
Do you get an output from the following command:
sudo sqlite3 /home/user-data/owncloud/owncloud.db "SELECT * FROM oc_users_external WHERE EXISTS (SELECT 1 FROM oc_users_external p2 WHERE oc_users_external.uid = p2.uid AND oc_users_external.rowid > p2.rowid);"
chris@xxxxxxxxx.com|127.0.0.1|
That means that this is your duplicate entry in the database.
This command searched for duplicate entries in the database and output one of them to the console.
You can follow the steps above, make sure to backup your database before you proceed.
Sorry for being so slow on the uptake. Can I blame insufficient coffee?
The MAIB upgrade runs without that error now. Unfortunately it isn’t upgrading to 67.
âś“ All system services are running.
âś“ SSH disallows password-based login.
âś“ System software is up to date.
A new version of Mail-in-a-Box is available. You are running version v64. The latest version is v67. For upgrade instructions, see https://mailinabox.email.
✓ System administrator address exists as a mail alias. [administrator@box.xxxxxx.com ↦ yyyyy@xxxxxxxx.com]
âś“ The disk has 34.95 GB space remaining.
âś“ System memory is 59% free.
Did you use this command to upgrade:
curl -s https://mailinabox.email/setup.sh | sudo bash
I did use that command, however I think I may have narrowed down the “error”. After the earlier fix went in, and the results I pasted above, running it again shows this:
Primary Hostname: box.yyyyyyyyy.com
Public IP Address: xxx.xxx.xxx.xxx
Mail-in-a-Box Version: v67
Yet this is the output of the status:
$ sudo ~/mailinabox/management/status_checks.py
âś“ All system services are running.
âś“ SSH disallows password-based login.
âś“ System software is up to date.
A new version of Mail-in-a-Box is available. You are running version v64. The latest version is v67. For upgrade instructions, see https://mailinabox.email.
Hmmm.
I suspect that there is more than one Mail-in-a-Box directory on your server.
Turns out I also have more than one folder (oops).
One in the “user” home directory (/home/user) and one in the “root” home directory (/root).
When I execute the status_checks command as follow:
sudo ~/mailinabox/management/status_checks.py
I got this output:
System
======
âś“ All system services are running.
âś“ SSH disallows password-based login.
âś“ System software is up to date.
âś– A new version of Mail-in-a-Box is available. You are running version v57. The latest version is v67. For upgrade instructions, see https://mailinabox.email.
As you can see, my MiaB is not up to date (v57).
But when I execute the status_checks command as follow:
sudo -i -u root bash -c '~/mailinabox/management/status_checks.py'
I got this output:
System
======
âś“ All system services are running.
âś“ SSH disallows password-based login.
âś“ System software is up to date.
âś“ Mail-in-a-Box is up to date. You are running version v67.
My MiaB is up to date (v67).
That’s weird, I suspect it has something to do with git
I ran the following command to find all directories by the name mailinabox that contains a sub-directory named .git:
sudo find / -path "*/mailinabox/.git"
And got this output:
/home/user/mailinabox/.git
/root/mailinabox/.git
There lies my “problem”, it appears that in my case, I once executed the command:
curl -s https://mailinabox.email/setup.sh | sudo bash
logged in as “user”, which means that home directory is /home/user.
On another occasion I executed this command logged in as “root” which means that home directory is /root
If we’ll look at the code of status_checks.py we’ll see that the function checking the local MiaB git version looks like this:
897 def what_version_is_this(env):
898 # This function runs `git describe --abbrev=0` on the Mail-in-a-Box installation directory.
899 # Git may not be installed and Mail-in-a-Box may not have been cloned from github,
900 # so this function may raise all sorts of exceptions.
901 miab_dir = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
902 tag = shell("check_output", ["/usr/bin/git", "describe", "--abbrev=0"], env={"GIT_DIR": os.path.join(miab_dir, '.git')}).strip()
903 return tag
If we’re logged in as “user”, miab_dir variable will contain: /home/user/mailinabox
and we’ll get the tag value from the command:
GIT_DIR=/home/user/mailinabox/.git bash -c '/usr/bin/git describe --abbrev=0'
If we’re logged in as “root”, miab_dir variable will contain: /root/mailinabox
and we’ll get the tag value from the command:
GIT_DIR=/root/mailinabox/.git bash -c '/usr/bin/git describe --abbrev=0'
Let see from where we got those git directories.
If we’ll look at the code of “setup.sh” we’ll see that it uses the $HOME variable as the base location to download its git repository.
53 # Clone the Mail-in-a-Box repository if it doesn't exist.
54 if [ ! -d $HOME/mailinabox ]; then
55 if [ ! -f /usr/bin/git ]; then
56 echo Installing git . . .
57 apt-get -q -q update
58 DEBIAN_FRONTEND=noninteractive apt-get -q -q install -y git < /dev/null
59 echo
60 fi
So when we run the setup command:
curl -s https://mailinabox.email/setup.sh | sudo bash
It will clone the mailinabox git repository based on the $HOME location of the currently logged in user.
Therefore we need to stick to one user when installing and upgrading MiaB and make sure to only use this user.
I think it would be safe to delete the unnecessary MiaB git directory
Just to add:
If you received an incorrect version in the web interface (admin control panel), like this:
This is because the start program (located at: /usr/local/lib/mailinabox/start) is configured to use the wrong MiaB Git repository.
As you can see here, the PYTHONPATH is set to use the user’s home directory as the path for MiaB.
In my case it will give me the wrong version in admin control panel.
12 source /usr/local/lib/mailinabox/env/bin/activate
13 export PYTHONPATH=/home/user/mailinabox/management
14 exec gunicorn -b localhost:10222 -w 1 --timeout 630 wsgi:appTo fix this, you can edit the PYTHONPATH line of code to point to the correct path, once you’ve checked and made sure you found the correct path.
Open the “start” program with nano:
sudo nano /usr/local/lib/mailinabox/start
Edit the line export PYTHONPATH= (replace: “/the/right/path/to” with your path)
export PYTHONPATH=/the/right/path/to/mailinabox/management
Save and exit nano:
Ctrl+x
y
Enter
Restart MiaB service:
sudo systemctl restart mailinabox.service
Refresh the admin page: https://box.some-domain.tld/admin
F5
Now you should see:
Thank you @solomon for the very comprehensive information. I had a long weekend, so just got to this.
I do, indeed, have an installation that is v64 in my home/user version, and another that is v67 in the root account.
I wish to keep home/user version, preferably. What is the best way to delete the version I do not want and not break anything? I really don’t want to end up with a deleted version and an inoperable version.
TIA.
Edited as irrelevant
First, to avoid the possibility of breaking your MaiB server, you can do the following:
1. Rename /root/mailinabox to /root/not_in_use_mailinabox, to make sure no script tries to “accidentally” run from that location
bash mv /root/mailinabox /root/not_in_use_mailinabox
2. Edit the start file as explained above and restart mailinabox.service
3. Check the admin control panel in your browser
If “System check” is correct then you can delete /root/not_in_use_mailinabox, in case something went wrong, just “reverse” the process.
Second, to make sure it doesn’t happen again, you can use this command line in the terminal to upgrade MiaB instead of the short version (it will print the $HOME value in green).
If you type yes it will run the “curl -s https://mailinabox.email/setup.sh | sudo bash” command:
while true; do read -p $'\nIs this the \e[1;37mright home folder\e[0m? >> \e[1;32m'"$HOME"$'\e[0m [yes/no]: ' yn; case $yn in "no") echo "Bye"; break;; "yes") curl -s https://mailinabox.email/setup.sh | sudo bash; break;; *) echo "Enter yes or no";; esac; done
The output of this command looks like this:
Maybe it’s worth opening a PR to add this line at the beginning of the installation script in order to avoid installing to the wrong folder.
All done, all working as expected. Thank you!!
OK, I was premature. This upgrade is not running as expected. I went to delete the root version (renamed to not_in_use_mailinabox) and found that there was a new mailinabox folder created there. I checked the file:
/usr/local/lib/mailinabox/start
and found that it was reset to root.
To ensure I wasn’t in a trance yesterday, I started from scratch again and renamed the root version, then I changed the start file to point it back to the /home/correctUser location. Then I ran the upgrade, from that /home/correctUser folder, using the line you gave above. It confirmed that the location would be /home/correctUser and upgraded.
It upgraded it to root folder, and it changed the start file back to root!
My mistake,
I didn’t notice that when running the script using sudo everything within the script will run in the root environment and not in the user environment, unlike running the command directly in the terminal, it doesn’t refer to $HOME as it appears when using the echo command.
If you’ll run the following command:
sudo printenv HOME
You’ll see that it gives the root folder as $HOME, therefore the script should always install to the root folder.
You can see here from the mailing list at “www.sudo.ws” they discuss the problem that:
“in Ubuntu, sudo retains the calling user’s $HOME”
I think it’s been patched since then.
The bottom line is that the setup.sh script will always install in “/root”.
And again, I apologize for this mistake.
Thank you, again. Much appreciated. If it is meant to go in root, then I am totally OK with that.