Upgrade to v60.4 fails

[leancode]

Today I tried to upgrade from 56 (I think - the last one before the 60 series) and the upgrade failed with this

After chasing this around I found the problem to be in setup/mail-users.sh. The problem is that the script was trying to add the quota column to the users table when it already existed and this was not properly caught by the script. The elif line did not return correctly that the column already existed:

elif sqlite3 $db_path ".schema users" | grep --invert-match quota; then
    echo "ALTER TABLE users ADD COLUMN quota TEXT NOT NULL DEFAULT '0';" | sqlite3 $db_path;
fi

The quick fix was to comment the line out and re-run the install and I am back up and running, but this is a bug.

Trying to nail this down and come up with a solution, I found that:

sqlite3 /home/user-data/mail/users.sqlite ".schema users"

when run in the terminal works and gives the table structure with the quota field as follows:

CREATE TABLE IF NOT EXISTS "users" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "email" TEXT NOT NULL,
  "password" TEXT NOT NULL,
  "extra",
  "privileges" TEXT NOT NULL DEFAULT '',
  "quota" TEXT NOT NULL DEFAULT '0',
  "can_sent_as_any" INTEGER,
  UNIQUE ("email" ASC)
);

But notice how this is in multiple lines! So the inverse grep only filters out the quota line and still returns truthy.

I played around with sqllite3 --line option but could not get it to work. Ultimately it worked replacing:

elif sqlite3 $db_path ".schema users" | grep --invert-match quota; then
    echo "ALTER TABLE users ADD COLUMN quota TEXT NOT NULL DEFAULT '0';" | sqlite3 $db_path;
fi

with

else
    sql=$(sqlite3 $db_path "SELECT sql FROM sqlite_schema WHERE name = 'users'");
    if echo $sql | grep --invert-match quota; then
        echo "ALTER TABLE users ADD COLUMN quota TEXT NOT NULL DEFAULT '0';" | sqlite3 $db_path;
    fi
fi

There might be more elegant versions. I am really not a bash expert, but for all it worth I've put that into a pull request here:

97

[ddavness]

Interesting. What distribution/version of sqlite do you happen to be running? Likely because MIAB assumes that .schema users would return everything in one line:

[leancode]

debian 11 sqlite3 --version 3.34.1 2021-01-20 14:10:07 10e20c0b43500cfb9bbc0eaa061c57514f715d87238f4d835880cd846b9ealt1

[ddavness]

I see, I can kinda reproduce the problem when running .schema users --indent, so I am positive this could be the result of some interaction with some configuration file; What are the contents of /root/.sqliterc?

sudo cat /root/.sqliterc

(It's possible that this is caused by a configuration setting somewhere, however MIAB should still just work around this kind of stuff)

[leancode]

cat: /root/.sqliterc: No such file or directory

[leancode]

There is nothing in /etc/ either that I can see. This machine's sole purpose is to run the mail server. It was originally a fresh install with only the setup.sh run. There is nothing else on there.

[ddavness]

Alright, let's chalk it up to the mysteries of life (or something like that)

[leancode]

Yep, something like that. I have really no idea. I am just happy that I was able to debug it and get up and running again fast. Thanks for your fast responses and your great work!

[ddavness]

Fixed on v60.5