[Solved] Database corruption

itserv

Hi *!

I wonder if this is wanted, intended and normal: I have objects in my lob database table which are not referenced by the singleinstances.

To me, this looks as if attachments have been removed, but the data blocks are not purged.

Prove:

mysql> select count(*) from lob where instanceid not in ( select instanceid from singleinstances) ;
+----------+
| count(*) |
+----------+
|       17 |
+----------+
1 row in set (2 min 4.39 sec)

And even worse: I believe I have quite a number of records that refer to attachments, but they are missing:

mysql> select count(*) from hierarchy where type=7 and id not in (select hierarchyid from singleinstances);
+----------+
| count(*) |
+----------+
|      782 |
+----------+
1 row in set (0.10 sec)

Question: do I understand the database model correct? Should there be a singleinstance entry if there is a hierarchy entry of type 7 (attachment)?

And is there a better database structure description than the one at https://github.com/Kopano-dev/kopano-core/blob/master/doc/Database_structure.md ?

itserv

Answer to myself

having records in lob which are NOT referenced by singleinstance.instanceid is NORMAL

select count(*) from lob where instanceid not in (select instanceid from singleinstances);

having records in singleinstances which are not referenced by hierarchy is NORMAL

select count(*) from singleinstances where hierarchyid not in (select id from hierarchy);

To fix this, periodically execute

/usr/share/doc/kopano/perl -w db-remove-orphaned-attachments /etc/kopano/server.cfg

Once a week seems to be more than sufficiant.

But …

having attachments in singleinstances, but no data blocks in lob, is a problem.

select count(*) from singleinstances where instanceid not in (select instanceid from lob);

The latter problem is obvisiously big enogh to stop Kopano from working with the error message

Thu Aug 30 08:24:04 2018: [error  ] SerializeObject failed with error code 0x80000004 for object 697569
Thu Aug 30 08:24:04 2018: [debug  ] gSOAP error on processing request: SOAP_FATAL_ERROR

itserv

To resolve this, it seems to be safe to go through the following procedure.

** THIS PROCEDURE IS FOR INSTALLATIONS WITH THE ATTACHMENTS HELD IN THE DATABASE ***

You have two possibilities:
A) leave the MAPI object believe they have an attachement, but when you try to open such, you will get an error message
B) entirely discard the reference to such attachments.

To fix the above error message, method A is sufficient

I would recommend version A, because in this case you get a feedback from Kopano that the attachement is broken. This seems to be better than not knowing at all there used to be an attachment. Additionally, method B is probably incomplete.

PROCEED AT YOUR OWN RISK. I’M NOT A KOPANO SUPPORTER, OR RELATED TO KOPANO OTHER THAN I’M A USER. I CAN NOT BE HELD LIABLE FOR ANY DATA LOSS IF YOU FOLLOW MY INSTRUCTIONS*

MAKE A BACKUP OF YOUR PRODUCTION DATABASE AND FEED IT INTO A TEST SYSTEM. TRY THE PROCEDURE ON THE TEST SYSTEM BEFORE FIXING YOUR PRODUCTION*

do the basic cleanup of the attachments by running

/usr/share/doc/kopano/perl -w db-remove-orphaned-attachments /etc/kopano/server.cfg

Stop kopano server
if you choose method A, delete all records from singleinstances that do not have any records in lob:

delete from singleinstances where instanceid not in (select instanceid from lob);

if you choose method B, delete the records in hierarchy that claim to have an attachement, but the attachment is not in the database:

delete from hierarchy where type=7 and id not in (select hierarchyid from singleinstances);

restart kopano-server

NOTICE: following method B probably leaves other stubs in the databse. It seems to work, but is far from doing a surgery on your data. It’s more like the sledgehammer.