Mails disappearing - how to rebuild database?

stefan_o

Hello,
I've got quite an important issue: A lot of mails seem to be gone, no matter if Outlook, Thunderbird or the Web-Interface (in IMAP/Thunderbird even more mails seem to be missing). There seem to be holes in the database, like some months are completely missing. What could be the reason for that?
Is this just some indexing issue that have to force a rebuild of the database? But what I found on rebuilding the database index is related to PO3/IMAP, but the issue affects all types of clients.
I have a lot of backups of gromox/grommunio database, if gromox for some reason deleted or broke the mails is it possible to restore all mails from the old database without deleting new mails (like merging all databases into a new one without creating duplicates)?
Best regards
Stefan

crpb

Start here for manual file by file import :>.
https://docs.grommunio.com/man/gromox.7.html#mail-import-export-and-conversion

But anyhow, it shouldn't just loose stuff from any reason.

For checking the integrity there are a couple ways and before i look it up i know who just made up a script for such tasks where you can get the references
https://github.com/netinvent/gromox/pull/1

stefan_o

crpb
Can I just download the script and run it, because it's not in the grommunio repository yet, or are there any other dependencies that I need to install a completely different branch?

And yes, of course it shouldn't loose any mails, are there any log files that may help I only find an IMAP log file and the nginx from the web interface

ozy

@stefan_o The script has no other dependencies than gromox, it should be safe to run (have run it on a couple of systems so far).

You could also have a look in the eml folder of your user if there are mails that fit in the "holes" of missing mails.

stefan_o

I ran the script and it found lot's of problems:
Gromox mbox repair tool v2025050204 ./gromox-mbox-repair-tool.sh invoked on Mi 7. Mai 16:43:08 CEST 2025 Running mailbox checks Checking maildir /var/lib/gromox/user/1/0 ERROR: Check failed for /var/lib/gromox/user/1/0 Repairing maildir with gromox-mbck == /var/lib/gromox/user/1/0/exmdb/exchange.sqlite3 == ck_allocated_eids: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27 f28 f29 f257 f258 m291 m292 m293 m294 m366 m367 m368 m369 m370 m371 m372 m373 m374 m375 m376 m377 m378 m379 m380 m381 m382 m383 m385 m387 m396 m397 m398 m399 m403 m406 m410 m412 m414 m417 m420 m428 m429 m430 m433 m436 m440 m469 m470 m471 m472 m473 m474 m475 m476 m477 m478 m479 m480 m487 m488 m491 m495 m497 m499 m503 m506 m507 m518 m522 m524 m533 m534 m535 m536 m538 m541 m543 m546 [104 issues] [fixed] ck_indices_present: /var/lib/gromox/user/1/0/exmdb/exchange.sqlite3: 104 problems total Checking maildir /var/lib/gromox/user/1/1 ERROR: Check failed for /var/lib/gromox/user/1/1 Repairing maildir with gromox-mbck == /var/lib/gromox/user/1/1/exmdb/exchange.sqlite3 == ck_allocated_eids: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27 f28 f29 f257 f258 [31 issues] [fixed] ck_indices_present: /var/lib/gromox/user/1/1/exmdb/exchange.sqlite3: 31 problems total Checking maildir /var/lib/gromox/user/0/2 ERROR: Check failed for /var/lib/gromox/user/0/2 Repairing maildir with gromox-mbck == /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3 == ck_allocated_eids: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27 f28 f29 f257 f258 [31 issues] [fixed] ck_indices_present: /var/lib/gromox/user/0/2/exmdb/exchange.sqlite3: 31 problems total ERROR: Operation failed after 1 seconds
But it did not restore any missing mail.

I have a lot of backups of the /var/lib/gromox folder. Is there a way to merge all mails from all backups without creating duplicate mails?

WalterH

If you see errors like: "ck_allocated_eids: f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 ..." the tool will repair this errors and no mails are lost.

stefan_o

Maybe the mail loss was caused by something else.
I checked the folder /var/lib/gromox/user/1/0/eml: There are over 25000 files in there. If every file contains a single email (that is what it looks like) that would mean this user has over 25000 mails stored? I've had a look in the Mail webinterface, combining the mail count of all folders it's not even 10000.
What I'm wondering: The file names seem to be two schemes: Around 10000 end with the domain, 15000 end with .midb. Is that normal?
I checked the exchange.sqlite3, in the messages table there a little less than 24000 entries. Is it possible that gromox somehow forgot that these mails exist? Or maybe they are not assigned to any directory?

ozy

Have you tried to repair the exchange sql database with the script ?
It makes a copy of the prior base, so the risk is minimal.

Also, if you know the beginning and ending dates of the "holes", you could check whether that there are mails in the eml folder that fit in the actual creation date with find /var/lib/gromox/user/X/Y/eml -ctime +n -ctime -m where n would be file creation date older than n days and m would be newer than m days.

stefan_o

ozy
Yes, I did that as well, I also run the script to repair the SQL database. And I confirmed that there are mails in the time of that holes, I also checked the dates of the files and there are no obvious holes in the dates there, so nothing seems to be missing in the files.

So how do I get the mails back into grommunio?

ozy

stefan_o Considering that you have all files on disk, that's good news.
I'm not a grommunio expert, and perhaps other people would be more qualified than me to respond, but I'd go the following route:

Clone your mail server (or back it up) in order to have a go-back path in case of problem
Run a find command to gather all mails that are in the holes, and use the grommunio import eml files utility to add them back into the mailbox

There's still a need to know what created the holes.
Did you make a journalctl -g error -S "2025-04-05 12:00:00" -U "2025-04-06 12:00:00" (replace the dates with your mail holes) to see what happened that day ?

stefan_o

ozy
The holes are just an observation in some folders. I can't tell for sure that's all what is missing.
Is the database structure documented somewhere, so I can check if file X is in the database and to what folder it belongs?
What I would like is something like this, as I have a lot of backups of the mailserver:

for email_file in directory:
    if email_file not in current_database:
        mail_added = False
        for prev_database in order_descending(prev_databases):
            if email_file in prev_database:
                add_mail(email_file, current_database, get_folder(prev_database, email_file))
                mail_added = True
                break
        if mail_added is False:
            if sender(email_file) == USER:
                add_mail(email_file, current_database, 'SENT')
            else:
                add_mail(email_file, current_database, 'INBOX')

This is of course just pseudo-code. To be able to implement a script like that I need to know how the database is structured. There is a messages table in there, but I don't know how it relates to the filenames.

I had a look in journalctl, I cannot find anything from grommunio, only occasional mariadb errors that a connection was lost and incorrect passwords in the webinterface.

I have an idea what could have caused the issue: For a long time I just updated the Debian server like how you update Debian: apt update / dist-upgrade. But grommunio does not fully integrate itself in the debian package management, it requires it's own update script to update it's database to new versions (why is this not part of the debian packages so it gets run automatically on updating the packages?). I didn't know that until at one point grommunio stopped working. I then run the update script and it worked again, but maybe the database was damaged at that point.

stefan_o

I had a look at the 3 databases that I could find, tables.sqlite3, exchange.sqlite3 and midb.sqlite3.

tables.sqlite3 did not contain anything I was able to understand (and it doesn't contain much at all).

midb.sqlite3 contains a messages table with almost 9000 entries, with the filename (mid_string), folder id, dates, flags etc., easy to understand. Every message has an id. I don't know how they are assigned, sometimes they are continuous, but there are also massive gaps. All mid_string values end with .midb, even though some mail filenames end with the domain rather than midb. But as far as I know midb.sqlite3 is only used for POP3/IMAP and is generated from another database?

That brings me to exchange.sqlite3: Also contains a messages table, with almost 24000 entries, but mid_string is NULL for almost all of them (only 400 with mid_string). The mid_string ends with the domain, but the files don't seem to exist. There is no other reference to the filename. There is also the message_properties table (long table format rather than wide table), but I couldn't identify any property that relates to the filename.

Or is the midb.sqlite3 the relevant table after all and not only for IMAP/POP3?

WalterH

stefan_o midb.sqlite3 is only used for POP3/IMAP and is generated from another database?

it is generated from exchange.sqlite3.

exchange.sqlite3 is the real and only message database.

stefan_o

WalterH
Ok, is the database layout described anywhere? How does it reference the files in the mail directory? Are the proptags for the long table format documented somewhere?

What I have understand so far allow me to find the folder id of a folder, get messages for that folder and then get a lot of meta stuff for that message. I have not figured out how to get the content of the mail or how the mail files are referenced

ozy

@stefan_o Again, I'm not a grommunio expert, but I'd do the following:

create a new mailbox
import all eml files into said box

The above would generate a new database. At the end of the day you could just archive the previous mailbox, delete it and rename the new one to the old name.

stefan_o

ozy
I would lose all folder structure (the user has created over 100 hundred folders). As I have old backups of the database I'm pretty optimistic I could restore most mails to the correct folder (see my pseudo-code above). Sorting 25000 mails into the correct folders would be a nightmare.

ozy

Fair enough. You'll need a grommunio coder here I guess.
Still wonder how you "lost" those mails.
I use grommunio since v2.16 and never lost anything (except for one time I did snapshot a VM and moved the storage without merging my snapshot first, but as you can imagine that's not grommunio's fault).

You really should investigate what happened...

stefan_o

ozy
I assume this was caused by an update that was performed by the debian packet manager rather than the update script. I have no other explanation

stefan_o

Any help? Can somebody maybe point to the source code, where the midb database is generated from the main database, that should make things a lot clearer (in the hope the code is readable and mostly self-contained)

crpb

i think thats all https://github.com/grommunio/gromox/blob/prime/lib/dbop_sqlite.cpp

stefan_o

crpb
As I understand that is the code to create the empty database and to upgrade it, but not the code that fills it.
For me to understand the main database some code would help that iterates over all messages in the main database and fill the midb with the data, as the midb is (almost) self-explaining