gromox-mbck does not repair mailbox properly, and gromox-mbop fails to purge it

ozy

So I run gromox-cleaner task on my grommunio server and it fails without any good error message:

journalctl -u gromox-cleaner

avril 21 00:02:25 email gromox-mbop[26308]: purge_softdel / failed
avril 21 00:02:25 email systemd[1]: gromox-cleaner.service: Main process exited, code=exited, status=1/FAILURE
avril 21 00:02:25 email systemd[1]: gromox-cleaner.service: Failed with result 'exit-code'.
avril 21 00:02:25 email systemd[1]: Failed to start Gromox mailbox cleaner.

Digging down the error, I found it just stops at a specific mailbox

/usr/sbin/gromox-mbop -d /var/lib/gromox/user/redacted_domain/redacted_user purge-softdelete -r / -t 30d20h
purge_softdel / failed

Checking that specific mailbox resulted in a couple of errors resolved with gromox-mbck -p .... After this, checking the mailbox would work okay:

cd /var/lib/user/{redacted_domain}/{redacted_user}
gromox-mbck ./exmdb/exchange.sqlite3
== ./exmdb/exchange.sqlite3 ==
ck_allocated_eids: [0 issues]
ck_indices_present:

Still, running /usr/sbin/gromox-mbop -d /var/lib/gromox/user/{redacted_domain}/{redacted_user} purge-softdelete -r / -t 30d20h fails with the same error purge_softdel / failed

I also ran a pragma integrity_check; on the exchange.sqlite3 file:

SQLite version 3.47.2 2024-12-07 20:39:59
Enter ".help" for usage hints.
sqlite> pragma integrity_check;
*** in database main ***
Page 443087: never used
wrong # of entries in index rid_properties_index5
wrong # of entries in index proptag_propval_index4
row 2417537 missing from index proptag_propval_index4
row 2417690 missing from index proptag_propval_index4
row 2417765 missing from index proptag_propval_index4
row 2417840 missing from index proptag_propval_index4
row 2718922 missing from index proptag_propval_index4
row 2719063 missing from index proptag_propval_index4
row 2719205 missing from index proptag_propval_index4
row 2719275 missing from index proptag_propval_index4
row 2719419 missing from index proptag_propval_index4
row 2719572 missing from index proptag_propval_index4
row 2719716 missing from index proptag_propval_index4
row 2719786 missing from index proptag_propval_index4
row 5366069 missing from index proptag_propval_index4
row 5366101 missing from index proptag_propval_index4
row 5366190 missing from index proptag_propval_index4
row 5366336 missing from index proptag_propval_index4
row 5366368 missing from index proptag_propval_index4
row 5367089 missing from index proptag_propval_index4
row 7209725 missing from index proptag_propval_index4
row 7209801 missing from index proptag_propval_index4
row 7209830 missing from index proptag_propval_index4
row 7209887 missing from index proptag_propval_index4
row 7209947 missing from index proptag_propval_index4
row 7210004 missing from index proptag_propval_index4
row 7215623 missing from index proptag_propval_index4
row 7215632 missing from index proptag_propval_index4
row 7215635 missing from index proptag_propval_index4
row 7215650 missing from index proptag_propval_index4
row 7215651 missing from index proptag_propval_index4
row 7215656 missing from index proptag_propval_index4
row 7215658 missing from index proptag_propval_index4

How can I repair that mailbox ?
I know that I can dump / import to a new file with sqlite3, eg:

sqlite3 exchange.sqlite3 .dump | sqlite3 exchange.sqlite3.repaired

But this failed with:

sqlite3 exchange.sqlite3.copy .dump | sqlite3 exchange.sqlite3.recovery
Runtime error near line 22044902: UNIQUE constraint failed: message_properties.message_id, message_properties.proptag (19)

And perhaps this should be reported to the Grommunio team ?
I only use community edition (small user) so I can't create ticktes.

[EDIT]Running gromox-2.45.0.gb785b87-lp156.2.1.x86_64 on OpenSuse 15.6[/EDIT]

ozy

Checking journalctl, I also found the following error messages that came foreach purge action I tried:

avril 21 10:27:12 email http[2491]: sqlite3_exec(/var/lib/gromox/user/redacted_domain/redacted_user/exmdb/exchange.sqlite3) "DELETE FROM messages WHERE message_id IN (SELECT m.message_id FROM messages AS m INNER JOIN message_properties AS mp ON m.message_id=mp.message_id AND m.is_deleted=1 AND m.parent_fid=11 AND mp.proptag=805830720 AND mp.propval<=133870336130000000)": database disk image is malformed (11) at [<libgromox_common.so.0(_ZN6gromox11gx_sql_execEP7sqlite3PKcj+0xa0) [0x7f8eaab8922d]><libgxs_exmdb_provider.so.0(+0x8ec88) [0x7f8eaa849c88]><libgxs_exmdb_provider.so.0(+0x8ee90) [0x7f8eaa849e90]><libgxs_exmdb_provider.so.0(+0x8ee90) [0x7f8eaa849e90]><libgxs_exmdb_provider.so.0(+0x912ec) [0x7f8eaa84c2ec]><libgxs_exmdb_provider.so.0(+0x4bfd3) [0x7f8eaa806fd3]><libgxs_exmdb_provider.so.0(+0x4cc94) [0x7f8eaa807c94]><libgxs_exmdb_provider.so.0(+0x4cf31) [0x7f8eaa807f31]><libgxs_exmdb_provider.so.0(+0x4db75) [0x7f8eaa808b75]><libc.so.6(+0xa6f6c) [0x7f8ea96a6f6c]><libc.so.6(+0x12e338) [0x7f8ea972e338]>]
avril 21 10:19:54 email http[2491]: sqlite3_exec(/var/lib/gromox/user/redacted_domain/redacted_user/exmdb/exchange.sqlite3) "DELETE FROM messages WHERE message_id IN (SELECT m.message_id FROM messages AS m INNER JOIN message_properties AS mp ON m.message_id=mp.message_id AND m.is_deleted=1 AND m.parent_fid=11 AND mp.proptag=805830720 AND mp.propval<=133870331720000000)": database disk image is malformed (11) at [<libgromox_common.so.0(_ZN6gromox11gx_sql_execEP7sqlite3PKcj+0xa0) [0x7f8eaab8922d]><libgxs_exmdb_provider.so.0(+0x8ec88) [0x7f8eaa849c88]><libgxs_exmdb_provider.so.0(+0x8ee90) [0x7f8eaa849e90]><libgxs_exmdb_provider.so.0(+0x8ee90) [0x7f8eaa849e90]><libgxs_exmdb_provider.so.0(+0x912ec) [0x7f8eaa84c2ec]><libgxs_exmdb_provider.so.0(+0x4bfd3) [0x7f8eaa806fd3]><libgxs_exmdb_provider.so.0(+0x4cc94) [0x7f8eaa807c94]><libgxs_exmdb_provider.so.0(+0x4cf31) [0x7f8eaa807f31]><libgxs_exmdb_provider.so.0(+0x4db75) [0x7f8eaa808b75]><libc.so.6(+0xa6f6c) [0x7f8ea96a6f6c]><libc.so.6(+0x12e338) [0x7f8ea972e338]>]
avril 21 10:18:35 email http[2491]: sqlite3_exec(/var/lib/gromox/user/redacted_domain/redacted_user/exmdb/exchange.sqlite3) "DELETE FROM messages WHERE message_id IN (SELECT m.message_id FROM messages AS m INNER JOIN message_properties AS mp ON m.message_id=mp.message_id AND m.is_deleted=1 AND m.parent_fid=11 AND mp.proptag=805830720 AND mp.propval<=133870330660000000)": database disk image is malformed (11) at [<libgromox_common.so.0(_ZN6gromox11gx_sql_execEP7sqlite3PKcj+0xa0) [0x7f8eaab8922d]><libgxs_exmdb_provider.so.0(+0x8ec88) [0x7f8eaa849c88]><libgxs_exmdb_provider.so.0(+0x8ee90) [0x7f8eaa849e90]><libgxs_exmdb_provider.so.0(+0x8ee90) [0x7f8eaa849e90]><libgxs_exmdb_provider.so.0(+0x912ec) [0x7f8eaa84c2ec]><libgxs_exmdb_provider.so.0(+0x4bfd3) [0x7f8eaa806fd3]><libgxs_exmdb_provider.so.0(+0x4cc94) [0x7f8eaa807c94]><libgxs_exmdb_provider.so.0(+0x4cf31) [0x7f8eaa807f31]><libgxs_exmdb_provider.so.0(+0x4db75) [0x7f8eaa808b75]><libc.so.6(+0xa6f6c) [0x7f8ea96a6f6c]><libc.so.6(+0x12e338) [0x7f8ea972e338]>]

I tried to run a .recover command from the SQLite3 file just for the sake of testing, and found out the following issue:

sqlite3 exchange.sqlite3.copy .recover | sqlite3 exchange.sqlite3.recovery
Parse error near line 8: object name reserved for internal use: sqlite_sequence

It looks like gromox uses a sqlite reserved name sqlite_sequence , used in grommunio/gromox/exch/exmdb/db_engine.cpp#L2513.
Of course I don't know if this is intended.

Can I safely stop gromunio services, recover the sqlite3 file and replace the original file with the recovered one ?
Should I take any special steps here (out of backups of course) ?

WalterH

You may use an outdated sqlite3 version. Try to reinstall sqlite3 from gromox repository.

ozy

@WalterH Looks like I already have the grommunio version

rqm -qa | grep sqlite
sqlite3-3.47.2-lp156.1.3.x86_64

Latest version in Grommunio community seems to be https://download.grommunio.com/community/openSUSE_Leap_15.6/sqlite3-3.47.2-lp156.1.3.src.rpm

ozy

Any ideas perhaps ?

WalterH

Did you try the DB recovery as described here: https://docs.grommunio.com/kb/sqlite.html

ozy

Thanks for pointing into the right direction. Will do.

ozy

@WalterH Thank you for your help, I was indeed able to solve my issues.

I've created a mailbox check and repair script that handles cleaning, repairing a mailbox and repairing the sql files.
RFC at https://github.com/netinvent/gromox/blob/repair-script/tools/gromox-mbox-repair-tool.sh

@crpb Would love if you could review my script too.

I've run this tool on my systems with success so far 🙂

crpb

I'm always happy when i can see other peoples scripts so OF COURSE! :p