REALLY old system upgrade help needed 14.04/v0.27 to 22.04/current

piks · July 23, 2024, 3:56am

Thanks for the info!

Something seems fragile in this though. All was working on the new box. I followed my process above and stopped dovecot, postfix, and nginx, rsync’d the user-data/mail dir to bring in any mail from the last few days on the old box, and restarted the daemons. I expected to turn off the old box and change the MX record and do a quick test.

Instead, the admin page no longer allows me in. I can get into roundcube, and the mail is up to latest, but the admin page is broken. I’ve diff’d user-data dirs between the two and only the files: version and settings.yaml are different (one says true, one says false, tried it both ways and no joy - what is this for?). The rsync of user-data/mail was the only change!

I set $TAG to 68 and tried a reinstall. I tried setup again. Rebooted. Nothing works. I’ve been discussing it over at ‘admin-panel-broken-after-restore-upgrade’ topic but since this has been such a big hit to this topic, I’d thought of mentioning it here. Most of the other topic fixes there may be v69 related.

To verify, the version file shows ‘10’ on the old system and ‘14’ on the new (?) and the changelog on the new system ends at v68, so I’m assuming it’s still v68.

Again, only 4-5 emails got moved into mail on the last transfer and it broke admin…

Thoughts?

I think I might want to document what should be where (with perms) and when to use each utility, but I’m nowhere near knowing enough yet…

KiekerJan · July 23, 2024, 6:41am

I don’t have time for a deep dive now, but are there any errors when you run the mailinabox setup? Since you have the version file at 10 and I think I saw in the other topic you’re missing a table, it seems as if setup has not run completely.

piks · July 23, 2024, 8:06am

Well the thing is, I didn’t run setup again. I only stopped the daemons, copied a half dozen mail files over (the mail dir) and restarted. It was specifically limited to the mail dir.

Running setup.sh didn’t identify any errors… See below for a suggestion on where it should have.

After pounding on it for a long time, I decided to rebuild it all. I agree about the table, but I looked in the users.sqlite on both machines and they matched - and the table complained about didn’t seem to be there. However, I could get into mail fine, so the users were there. BUT, I might have a bunch of clues for you!!

Was the MFA table added between v27 and v68?

I moved everything out of user-data, copied over ONLY the mail and www dirs (I had some things in www), then re-ran setup.sh. Interesting results that
maybe useful for some fixes

First time, it didn’t see a version file and flashed a message before coloring the screen. What was it? I cancelled it and looked - it didn’t see the version file and was going to install as new.

- What would that do to the data I copied? Would install as new have picked up the mail dirs/files/users?

I copied the version file over and re-ran. I got:

Running migration to Mail-in-a-Box #11…

Running migration to Mail-in-a-Box #12…

Running migration to Mail-in-a-Box #13…

Running migration to Mail-in-a-Box #14…

BTW, what’s with these versions vs 27-69?

It then built as usual, created the DH and self-signed files. BUT, starting nginx failed and the script stopped. Looking at the logs, the nginx local.conf file (didn’t touch the ssl.conf file - future problem perhaps?) still referenced the old certs (and didn’t like it).

I got this error though. Is it critical?

Installing nsd (DNS server)…
Generating DNSSEC signing keys…
Installing Postfix (SMTP server)…
mv: cannot stat ‘/var/lib/postgrey/*’: No such file or directory
Installing Dovecot (IMAP server)…
Installing OpenDKIM/OpenDMARC…
Installing SpamAssassin…
expired old bayes database entries in 10 seconds
159528 entries kept, 272110 deleted

I deleted local.conf and re-ran.

Nginx started and things continued.
This happened:

Installing Roundcube (webmail)…
Updating database schema (2018021600)… [OK]
Updating database schema (2018122300)… [OK]
Updating database schema (2019092900)… [OK]
Updating database schema (2020020100)… [OK]
Updating database schema (2020020101)… [OK]
Updating database schema (2020091000)… [OK]
Updating database schema (2020122900)… [OK]
Updating database schema (2021081000)… [OK]
Updating database schema (2021100300)… [OK]
Updating database schema (2022081200)… [OK]

Could it be the case that a table (MFA) was added somewhere along the line and it’s used for admin but not for roundcube? I could still get into mail, just not admin…

If so, does that sound like an explanation? Copying the mail folder also copied the older sqlite file, and thus it missed that table.

Admin then allowed login with an IP address. I then hit the Provision button in admin/TLS and it all happened according to plan. It’s now up and at the point I left it before. Yeah!

Now, that modifies the ideas and assumptions we’ve postulated above for moving to a new machine. For instance:

Some /etc/nginx files need to be removed if MiaB has ever been on the box.
The mail dir can’t be rsync’d over entirely if the user db structure has changed (maybe make those changes very clear in any changelog). The question is, in future, if just the mail files are sync’d, will there be a problem? ie, does the sqlite file track mail files?
Since owncloud couldn’t come over, I didn’t copy it at any time. Last install created a user for me in the new owncloud. The re-install also started with no owncloud folders but didn’t create me as a user this time. Neither was ever used…
Are there other configs or files that should be rebuilt but aren’t in this migration case, like nginx/conf.d/local.conf? I didn’t dig deep into /usr/local/lib/mailinabox or /etc or any other place.
Is it possible for setup.sh to look at the db and update the format if it isn’t the right one for the version? It isn’t triggered now if not identified by overall version level it seems. Perhaps check the db and update the schema as needed each tie setup is run.
In future, if the mail dir gets refreshed from the old box (as in another day goes by here), how can I trigger the db to be updated but not touch the ssl certs?

Question: setup.sh didn’t want to touch the cert lines in local.conf. If using the proposed method to get new certs - delete the ssl dir and run setup.sh - local.conf has to be deleted it seems. Once the certs are gone but still referenced in local.conf, admin page access may be broken and thus can’t reach the Provision button for new ones, unless setup can be written to remove the entries if they don’t actually exist.
Would

‘delete local.conf’=>‘run setup.sh’=>‘Provision button in Admin’

be a complete process then?

Does the theory about the missing table fit? Is it complete?
Is anything else affected in the ‘get-all-new-certs-by-deleting’ process?
Are there any other files affected in a migration between machines that should be removed first?

Thanks. This should be becoming a pretty good reference for these things…

JoshData · July 23, 2024, 10:45am

That’s right. The file /home/user-data/mailinabox.version tracks the needed migrations for /home/user-data/mail/users.sqlite (as well as some possibly older migrations in other parts of user-data). If you copy both files together there shouldn’t be an issue.

piks · July 23, 2024, 8:43pm

Thanks.
Are there any problems duplicating all the migration steps again, just to get it to update users.sqlite? Using the version file will trigger all migrations even though it’s only the db that needs to be updated…

JoshData · July 23, 2024, 9:08pm

You may just want to run the migration 13 and 14 by hand then:

github.com

mail-in-a-box/mailinabox/blob/main/setup/migrate.py#L183


      
                      conn.close()
          
                      # Delete all sessions, requiring users to login again to recreate carddav_*
                      # databases
                      conn = sqlite3.connect(os.path.join(env["STORAGE_ROOT"], "mail/roundcube/roundcube.sqlite"))
                      c = conn.cursor()
                      c.execute("delete from session;")
                      conn.commit()
                      conn.close()
          
          def migration_13(env):
          	# Add the "mfa" table for configuring MFA for login to the control panel.
          	db = os.path.join(env["STORAGE_ROOT"], 'mail/users.sqlite')
          	shell("check_call", ["sqlite3", db, "CREATE TABLE mfa (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, type TEXT NOT NULL, secret TEXT NOT NULL, mru_token TEXT, label TEXT, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE);"])
          
          def migration_14(env):
          	# Add the "auto_aliases" table.
          	db = os.path.join(env["STORAGE_ROOT"], 'mail/users.sqlite')
          	shell("check_call", ["sqlite3", db, "CREATE TABLE auto_aliases (id INTEGER PRIMARY KEY AUTOINCREMENT, source TEXT NOT NULL UNIQUE, destination TEXT NOT NULL, permitted_senders TEXT);"])
          
          ###########################################################

piks · July 23, 2024, 10:24pm

Thanks.

IF I had to go back to the old box, would there be any problem going back the other way and using the upgraded db on the old system? I’m not a db guy, but I might assume that just an added table would just go unused - correct?

And related, what would happen if an upgraded db went through the upgrade again. Would it just give an error and be left unchanged - implying upgrading an upgraded db is not a problem…

JoshData · July 24, 2024, 1:31am

That’s true.

what would happen if an upgraded db went through the upgrade again

It’s possible the whole installation will stop at the error. Or maybe the migration step will change the database unexpectedly - it depends on what the migrations did (I don’t remember them all).

piks · July 24, 2024, 9:21am

In coming up with a shortlist of files and dirs to keep refreshed from old to new until the switch is pulled, I see there are TWO sqlite files - users.sqlite and roundcube.sqlite in the mail dir. There are a few other dirs in mail too:

dkim
mailboxes
postgrey
roundcube
sieve
spamassassin

Has the roundcube.sqlite db gone through any schema changes, such that a copy from old to new box would then need a version re-update? (I hope not)

Are there dirs besides the mailboxes’ mail content such that copying old to new and overwriting these on the new box doesn’t matter? (so it can be safely overwritten on the new box)

Is it safe to ONLY copy mailboxes and NOT roundcube/roundcube.sqlite? (my last copy did that and I hope I didn’t lose something) A cursory look showed most of the tables had no content and the rest could be transient content. Yes?

What would be the list of dirs here that would be right/proper/needed to copy over (from old to new without running setup again) to merely ‘refresh’ mail that has come in on the old system while work is done on the new? (I had been assuming either mailboxes/ or mailboxes/mail)

Now, this refresh/copy-mailboxes method seemed to work, but I would really like to know what is right here…
Taking it one step further, if ‘mailboxes’ & a few other files are all that’s needed to refresh or even rebuild a system, I’m thinking of using inotify utilities to copy on changes to these dirs into a backup location, so a live current shadow exists.

Then, if it goes sideways, I have a core backup (data only) that’s only minutes stale and I could do a reinstall and pick up the data again.

Using the same method, I could shadow the minimal data to another machine so that the data is ready and up-to-date should I need a switchover (almost no downtime) - and really, these are the same methods as going from old to new boxes and doing periodic refreshes as work progresses.

I’m trying to sort through what files are ‘data’, what are machine specific and not data dependent, and what might be data-dependent but not machine dependent…

Thanks again for all the help.

system · July 31, 2024, 9:21am

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