old emails missing attachments

[Jan Krupka]

Hi Janos,

The Gui shows older emails with the attachment sign in the table, but the link for download ist missing. If i use pilerget, the following is happen:

root@mailpiler:~# pilerget 400000005ad230991d9d5a2c004aa4fa15d1
...
--=_JY26bNmsm7pr4nh2CFvZcYm
Content-Type: application/vnd.oasis.opendocument.spreadsheet;
 name=4714016232_EUR_20180414_184348.ods
Content-Disposition: attachment; filename=4714016232_EUR_20180414_184348.ods
Content-Transfer-Encoding: base64

ATTACHMENT_POINTER_400000005ad230991d9d5a2c004aa4fa15d1.a1_XXX_PILER--=_JY26bNmsm7pr4nh2CFvZcYm--
root@mailpiler:~# pilerget 400000005ad230991d9d5a2c004aa4fa15d1 1
zpipe: invalid or incomplete deflate data
root@mailpiler:~# ls -la /var/piler/store/00/5ad/15/d1
total 28
drwxr-x---  2 piler piler  4096 Apr 14  2018 .
drwxr-x--- 72 piler piler  4096 Apr 24  2018 ..
-rw-r-----  1 piler piler 15960 Apr 14  2018 400000005ad230991d9d5a2c004aa4fa15d1.a1
-rw-r-----  1 piler piler  2032 Apr 14  2018 400000005ad230991d9d5a2c004aa4fa15d1.m
root@mailpiler:~# file /var/piler/store/00/5ad/15/d1/400000005ad230991d9d5a2c004aa4fa15d1.a1 
/var/piler/store/00/5ad/15/d1/400000005ad230991d9d5a2c004aa4fa15d1.a1: data
root@mailpiler:~# file /var/piler/store/00/5ad/15/d1/400000005ad230991d9d5a2c004aa4fa15d1.m
/var/piler/store/00/5ad/15/d1/400000005ad230991d9d5a2c004aa4fa15d1.m: data
root@mailpiler:~# piler -v
1.3.8 build 998

Do you have any idea what the problem may be?

thx

[Janos SUTO]

Try pileraget on that attachment. Can it retrieve it?

[Jan Krupka]

yeah, looks good

root@mailpiler:~# pileraget 400000005ad230991d9d5a2c004aa4fa15d1 1
UEsDBBQAAAgAAL2FjkyFbDmKLgAAAC4AAAAIAAAAbWltZXR5cGVhcHBsaWNhdGlvbi92bmQub2Fz
...

thx

what’s to do for the GUI?

[Jan Krupka]

‌

root@mailpiler:/var/piler/www# grep pilera config.php 
$config['DECRYPT_ATTACHMENT_BINARY'] = '/usr/local/bin/pileraget';

hmm, upgrade instructions was followed

‌

[Janos SUTO]

Has anything changed before it started to behave weird with older messages?

[Jan Krupka]

Yes, it’s a new VM. The Archive was copied from the old VM. Exactly 12 months ago. And exactly before this date all attachments dissapeared in the archive.

I will ask the guy, who has migrated the archive, if he remember the process…., tomorrow …

[Jan Krupka]

Ok, we found it.

The table attachment missing all entries before the update date. In metadata all emails are there. If i search the piler_id, i found it in metadata but not in attachment.

The question is now, is it possible to reconstruct attachment?

[Janos SUTO]

Well, not really. Your best bet is the backup of the attachment table. If that’s not an option, then you may scan the older /var/piler/store/00 subdirs, and look for files ending with a1, a2, a3, … These are the attachments. Now the task is to create an entry for each in the attachment table. This should fix most emails. Note that the attachment table also stores the attachment names, sizes and types. For the recovery process just fake some values for these fields. Later you may update these columns by checking the mime fields for the attachments.

However, this simple process can’t figure out what attachments were deduplicated, so expect some emails to miss some attachments.

[Jan Krupka]

Ok, thanks for your support.

The deduplication works with hardlinks?

‌

[Janos SUTO]

Not sure. Piler doesn’t create any hardlinks.

[Jan Krupka]

Hmm, are there any Documentation, how deduplication works?

[Janos SUTO]

Well, not really, but it’s quite simple. Check the attachment table structure first.

The piler_id is self explaining. Then you have the attachment_id, it start from 1. If an email has 3 attachments, then it has 3 entries in the attachment table. name, type and size are also self explaining. Next you have the sig column, it’s a sha256 hash value of the attachment. Then you have the ptr column, by default it’s 0.

The deduplication works as follows. When the parser splits the message and its attachments, it computes the sha256 value for each attachments. Then it queries the attachment table if the given sha256 value is found in the attachment table? If no, then it simply inserts a new row, with ptr=0.

The other option is that we have found the given sha256 hash value at eg. id=1234. Then it also inserts a new row, however the ptr value is 1234 in this example.

And finally when you want to retrieve a message the piler checks the ptr value from the attachment table. If it’s 0, then it simply gets the matching attachment. If it’s >0, then it takes another query to see where’s the original attachment.

If you know language C, then check src/config.h for the sql queries, and src/attachment.c for the logic.

[Jan Krupka]

Janos, thank you very much. Maybe i can share a script for this here in future.

Regards

Jan

[Janos SUTO]

Not sure there's anything left to add. In that case the case is closed.