• Info
  • MIDB what are these databases for and how to regenerate the MIDB

The Message Index Data Base (MIDB) midb.sqlite3 accelerates IMAP and POP3 access to grommunio.
Additionally there are flat mail files which also increase the speed. The MIDB references these flat mail files.

  • The MIDB is located in: /var/lib/gromox/user/X/Y/exmdb/midb.sqlite3
  • The flat mail files are stored in: /var/lib/gromox/user/X/Y/eml/*.midb
  • Each mailbox has its own MIDB and associated flat mail files
  • The primary mail database, the exchange.sqlite3 is located in /var/lib/gromox/user/X/Y/exmdb/exchange.sqlite3

Please note, the MIDB can be rebuilt without any issues, but not the exchange.sqlite3, if the exchange.sqlite3 is damaged or deleted, the mailbox is most likely lost. A recovery procedure for a damaged but not deleted Domain or mail database (exchange.sqlite3) is documented in the KB: https://docs.grommunio.com/kb/sqlite.html, use it on your own risk.

The MIDB is automatically synchronized with exchange.sqlite3 by the system. But sometimes it is necessary to resynchronize all MIDBs, for example after large updates, like May 2023 update.

The rebuild/resynchronization procedure is described here

Hint: if you want to resyncronize only one MIDB, read Annex A.

1. preparation

  • Important: disable IMAP/POP3 access for all users who do not use IMAP/POP3 to conserve disk space.
  • Open a SSH session like Putty to the grommunio server
  • You can copy and paste the commands into the SSH session
  • To follow the regeneration of the MIDBs, you may open a 2nd SSH session for logging
  • At Annex C. of this tutorial there is a Python script, please create this script at the grommunio server as /tmp/trigger_midb.py and make it executable:
    chmod +x /tmp/trigger_midb.py, the script is needed at step 5.
    If you created the /tmp/trigger_midb.py file with an Windows editor, convert the file to Unix line feeds:
    zypper in dos2unix
    dos2unix /tmp/trigger_midb.py
  • Please note, regenerating the MIDBs generates huge load on the server and interrupts POP3 and IMAP access
  • Hint: if you want to clean up the system: shrink the MIDBs and delete all flat mail files, then exit at point 3.

2. recreate empty MIDBs

To do this we have to stop the MIDB daemon and then recreate the MIDBs, this interrupts IMAP and POP3 access.
systemctl stop gromox-midb
Recreate the MIDBs
grommunio-admin user query username | grep "@" | sort | while read line; do echo -e "\nRecreate MIDB for: $line"; gromox-mkmidb -vf $line; done

Start the MIDB daemon and empty the IMAP and POP3 cache.
systemctl restart gromox-midb gromox-imap gromox-pop3

3. run the cleaner

If the MIDB was deleted, all flat mail files referenced by the MIDB are orphaned and must be deleted to free the storage.
for i in /var/lib/gromox/user/*/*; do /usr/libexec/gromox/cleaner -d "$i"; done
As crpb stated, with gromox 2.10 cleaner was replaced by gromox-mbop, so we need a modified command to remove the user files:
grommunio-admin user query username maildir | grep "/var/lib/" | awk '{ print $1 }' | xargs -I {} /usr/sbin/gromox-mbop -u {} purge-datafiles

Hint: at this point all MIDBs are empty and the flat mail files are deleted. If you only want to clean up the system, you can exit at this point.

4. create the mail directory list to regenerate the MIDBs

The file /tmp/mail_dirs.txt lists every mailbox of this system, each line contains a path to a mailbox.
Only users/mailboxes where IMAP/POP3 is enabled, are included in the list file.
The long version of this command:
grommunio-admin user query pop3_imap maildir | grep "/" | grep True | sed -r 's/True//g' | sed -r 's/\s+//g'| sort | while read line; do echo "$line"; done > /tmp/mail_dirs.txt
The short version of this command:
grommunio-admin user query maildir --filter pop3_imap=True | grep "/" | sort > /tmp/mail_dirs.txt
Thanks to crpb for the short version.

5. rebuild the MIDBs and create the flat mail files

To do this, we use the Python script we created earlier. This process is CPU and I/O intensive, so it is best to run it at a time when there is little or no load on the system.
Optionally we may open a 2nd SSH session on the server and watch in the log how the MIDBs are built. Launch journalctl -fu gromox-midb in the 2nd SSH session.
We will see messages like:
May 12 22:20:31 server midb[nnnnn]: Running sync_contents for /var/lib/gromox/user/X/Y, folder nnnnnnn

To start the rebuild process, launch the Python script:
/tmp/trigger_midb.py
The script reads the file /tmp/mail_dirs.txt line by line and passes the commands to port 5555 which is the MIDB daemon. When the Python script is finished, the file /tmp/mail_dirs.txt is deleted.
Please note, rebuilding of the MIDBs takes several minutes per mailbox, depending on the mailbox size.

6. now all MIDBs are regenerated

IMAP and POP3 should work again and faster than before.
Remove the script /tmp/trigger_midb.py, it is no longer used.
rm /tmp/trigger_midb.py

7. about backup

We deleted and recreated all flat mail file, this is mostly a huge amount of date. Most differential backups have issues when so many files are new or deleted. If you use a differential backup like rdiff backup, consider to nuke the backup and recreate the backup now.

Enjoy grommunio

Annex

A. Rebuild/resyncronize only one MIDB

If you want to rebuild only one MIDB, the process is outlined here.

A.1. recreate empty MIDB

Replace MAIL@DOMAIN.TLD with the mailbox/user name you want or resyncronize.
To do this we have to stop the MIDP daemon and recreate the MIDB, this interrupts IMAP and POP3 access for all users.
systemctl stop gromox-midb
Recreate the one MIDB
gromox-mkmidb -vf <MAIL@DOMAIN.TLD>
Start the MIDB daemon and empty the IMAP and POP3 cache
systemctl restart gromox-midb gromox-imap gromox-pop3

A.2. run the cleaner

If the MIDB was deleted, all flat mail files referenced by the MIDB are orphaned and must be deleted to free the storage.
for i in /var/lib/gromox/user/*/*; do /usr/libexec/gromox/cleaner -d "$i"; done
As crpb stated, with gromox 2.10 cleaner was replaced by gromox-mbop, so we need a modified command to remove the user files:
grommunio-admin user query username maildir | grep "/var/lib/" | awk '{ print $1 }' | xargs -I {} /usr/sbin/gromox-mbop -u {} purge-datafiles

Hint: at this point the MIDB is empty and the flat mail files are deleted. If you only want to clean up the system, you can exit at this point.

A.3. rebuild the MIDB and create the flat mail files

Find the mail directory for this mailbox
grommunio-admin user query ID username maildir | grep <MAIL@DOMAIN.TLD>
The mail directory like /var/lib/gromox/user/X/Y/ we need for the resync command
Open a Telnet session to the MIDB daemon
telnet ::1 5555
Wait for OK to appear, than feed the rebuild command including the mailbox path to the MIDP daemon
X-RSYM /var/lib/gromox/user/X/Y
Exit Telnet when done - TRUE 2 is success
quit

B. Changes:

  • moved the command generation into the Python script
  • minor enhancements
  • added information about backup
  • Python script minor enhancements
  • added Rebuild only one MIDB

C. The Python script:

#!/usr/bin/python3
#
import socket, time, telnetlib, os
#
DIR_FILE = '/tmp/mail_dirs.txt'
def main():
    if not os.path.isfile(DIR_FILE):
        print("Error: {} file not found".format(DIR_FILE))
        quit()

    # Staring a TCP socket.
    telnet_client = telnetlib.Telnet('::1', 5555)
    # Opening and reading the mail directories list
    file = open(DIR_FILE, 'r')
    LINES = file.readlines()
    wcount = 0
    mcount = 0
    telnet_client.read_until(b"OK", timeout=10) # wait for OK
    # Count mailboxes / MIDB commands
    for line in LINES:
        mcount += 1
    print("{} mail directories for MIDB synchronization found.".format(mcount))

    # Sending the commands to MIDB
    for line in LINES:
        time.sleep(1)
        wcount += 1
        print("Mailbox {:>3}: {}".format(wcount, line.strip()))
        # Build the synchronization command
        byte_obj = ( "X-RSYM " + line.strip() + "\r\n").encode("ascii")
        telnet_client.write(byte_obj)
        telnet_client.read_until(b"TRUE ", timeout=300) # wait max. 5 minutes for TRUE

    print(f"[Closing connection]")
    file.close()
    # Closing the connection from MIDB daemon
    telnet_client.write(b'exit')
    # Remove the mail directories file
    os.remove(DIR_FILE)

if __name__ == "__main__":
    main()
#
    5 days later

    Hey Walter,

    as always, thank you for this thorough description of everything.

    I just tried it with my mailbox and have a few notes. Well, one for now..

    WalterH 4. create the mail directory list to regenerate the MIDBs

    This should result in the same list expect that it isn't sorted but i don't think that is in any way necessary and you don't have to jump through all those piping-hoops.

    grommunio-admin user query maildir --filter pop3_imap=True > /tmp/mail_dirs.txt

    More to come when i get back to it.

      Wouldn't it be advantageous to store another menu item for such a process in the CUI, which triggers the process if necessary?

      • crpb replied to this.

        Andy Wouldn't it be advantageous to store another menu item for such a process in the CUI, which triggers the process if necessary?

        IMO definitely NO!
        These fixes shouldn't be done out of the blue by clicking on a button in the webgui.

        This should be a one-time task if you encounter problems with IMAP and i hope after all those changes in the last few weeks/months these problems are a thing of the past.

        I will maybe get to it to extend Walters Script so one could just use it instead of doing all the steps manually(mostly for personal use) but other than that i don't expect that the Grommunio-Developers would integrate such a task in the Admin-Web.

        crpb

        grommunio-admin user query maildir --filter pop3_imap=True | sort > /tmp/mail_dirs.txt
        generates a sorted list.
        I need to test this and if its OK, I will change the step 4.
        Thank you.

        • crpb replied to this.

          WalterH

          grommunio-admin user query maildir --filter pop3_imap=True --sort maildir |grep -v '^$'

            crpb |grep -v '^$'

            why you added this command, please elaborate?

            This is just getting rid of any empty lines if there are any.

            • -v - --invert-match
            • ^ - Start of a line
            • $ - End of a line

              crpb there is a filter: --filter pop3_imap=True. Any object that fits this filter, can't have an empty mail directory - it is a valide mailbox or did I overlook something?

              • crpb replied to this.

                WalterH
                In a perfect world i guess you are correct with that assumption.
                I did an grommunio-admin user modify ldapgroup@domain.tld --pop3-imap 1 which worked and then the result might look like this

                root@grom-deb:~# grommunio-admin user query maildir --filter pop3_imap=True --sort maildir
                
                /var/lib/gromox/user/0/1
                /var/lib/gromox/user/0/2
                ...

                So you might run into that "empty line" after all due to misconfiguration by the admin :-).

                I then deleted the group and did an downsync with Enabled POP3/IMAP by default and it didn't enable it for the Group so it shouldn't happen in normal cases. The switch to that backend is relatively new anyhow so i guess @jschroeder πŸ‘‹ will maybe fix that?

                And with all that in mind: just forget the |grep -v... part πŸ™ˆ πŸ™Š πŸ™‰

                5 days later

                Nicht schΓΆn aber selten...

                • midb_repair.py

                  • Take a look at what would be executed
                    python3 midb_repair.py --all --verbose
                  • execute for real!
                    python3 midb_repair.py --all --verbose --no-dry-run

                For all intents and purposes: UNTESTED!!!

                EDIT: not python3.6 compatible (OpenSuse 15.4 default)
                FIX: 15.4 zypper install -y python39 / 15.5 zypper install -y python311 (if missing)

                2023.06.07: Fixed a few typos and cleaned up a bit.
                First test-runs on a shared mailbox look good so far.

                root@grom-deb:~/scripts/tools# ./midb_repair.py --verbose --user sharedmbx@domain.tld --no-dry-run
                Namespace(all=True, user='sharedmbx@domain.tld', backup=False, recreate=True, dry_run=False, verbose=True)
                Users
                /var/lib/gromox/user/4/0        sharedmbx@domain.tld
                recreate midbs
                ['systemctl', 'stop', 'gromox-midb', 'gromox-imap', 'gromox-pop3']
                ['gromox-mkmidb', '-vf', 'sharedmbx@domain.tld']
                dbop_sqlite: Creating table "configurations"
                dbop_sqlite: Creating table "folders"
                dbop_sqlite: Creating table "messages"
                dbop_sqlite: Creating table "mapping"
                ['systemctl', 'restart', 'gromox-midb', 'gromox-imap', 'gromox-pop3']
                ['/usr/libexec/gromox/cleaner', '-d', '/var/lib/gromox/user/4/0']
                Cleaner: sharedmbx@domain.tld
                Processing /var/lib/gromox/user/4/0/cid...
                Purged 0 files (0B) from /var/lib/gromox/user/4/0/cid
                Processing /var/lib/gromox/user/4/0/eml...
                Purged 9250 files (1956MB) from /var/lib/gromox/user/4/0/eml
                Processing /var/lib/gromox/user/4/0/ext...
                Purged 9250 files (13MB) from /var/lib/gromox/user/4/0/ext
                Running Trigger
                1 mail directories for MIDB synchronization found.
                Mailbox   1: /var/lib/gromox/user/4/0
                [Closing connection]
                9 days later

                After some testing with different cache on client and whatnot...

                My first tests were on Debian with the Community-Repo (2.9.95.38687c9-1+43.1) which looked pretty promising. To be certain i did an downgrade to 2.9.18.538f503-1+3.1 (because the actual Server runs gromox-2.9.18.538f503-lp155.3.1.x86_64), yet again purged the mailbox-data, copied it over, set permissions and restarted and reconnected.
                Then i ran the script again and it didn't work quite as good.
                There were actually many changes between those releases as far as i could make out in the git log.

                All i'm trying to say is:

                If you experience problems which might require to do this task then i recommend waiting for the "stable" release of Gromox >= 2.9.95 to yield the best results.

                With the Community-Release you are already set - 2023.06.08

                PS: I'm currently not a 100% sure if those problems are now gone for good.. @jengelh ?

                For those who do IMAP-Based Backups of individual Mailboxes:
                I tried this task already a while ago on only my personal Mailbox and it should be noted that Mailstore did archive quite a lot of mails a second time as the Headers changed.

                a month later
                4 months later

                Hey,
                since some days I have problems with IMAP access to one adress. I already tried to regenerate midb like described above. But it not helped.
                I get in the log of gromox-midb some of this:
                E-2403: mail_engine: timed out obtaining a reference on /var/lib/gromox/user/0/1

                Any ideas?

                5 days later

                Β© 2020-2024 grommunio GmbH. All rights reserved. | https://grommunio.com | Data Protection | Legal notice