subject missing

[Peter Bernsmann]

The subject field from some emails is blank in the search-result. If I select such an email, the subject is displayed correct in detailed view.

I found that in mysql view v_messages the subject is empty. The email-Header looks like this: * Subject: =?utf-8?B?TMO2c3VuZyB6dSBUaWNrZXQgMTYwNzc0OTcgKDMtbm9ybWFsLFdMMDE0KSBX?= =?utf-8?B?YXJ0dW5nc2FyYmVpdGVuOiBXYXJ0aXVuZ3NhcmJlaXRlbiBhbSBTdGFuZG9y?= =?utf-8?Q?t_0680-Havixbeck?= Thread-Topic: =?utf-8?B?TMO2c3VuZyB6dSBUaWNrZXQgMTYwNzc0OTcgKDMtbm9ybWFsLFdMMDE0KSBX?= =?utf-8?B?YXJ0dW5nc2FyYmVpdGVuOiBXYXJ0aXVuZ3NhcmJlaXRlbiBhbSBTdGFuZG9y?= =?utf-8?Q?t_0680-Havixbeck?= Thread-Index: AQHReTPz62RT/7xAHkOixPjI8weNnQ==*

Maybe, the importer had problems decoding the utf-8 strings? How can I fix it? The mails have been importet with pilerimport from an imap-mailbox.

[Janos SUTO]

Run pilertest against the message, and see if there's anything for a subject.

[Peter Bernsmann]

This is the output of pilertest:

locale: de_DE.UTF-8

build: 913

parsing...

post parsing...

message-id: [e-mail] / 4b4a4c2b9735ddb812ad18d259df0a7279a348d98356fabf5c5f5bda11b8b252

from: sr[...]

to: bernsmann peter [...]

reference: **

subject: ▒▒:▒j▒m▒$

body: *Sehr geehrter Herr Bernsmann vielen Dank [...] *

sent: 1457439218, delivered-date: 0

hdr len: 2062

body digest: 91864381702c76c3f180ff6dcfce04f4318f63c2878b580d4108f820fe6cc91d

rules check: (null)

retention period: 1781593688

attachments:

direction: 0

spam: 0

[Janos SUTO]

Provided that the subject you just pasted makes sense, try the following sql query:

update metadata set subject='....' where message_id='[e-mail]';

Then check with select if it has been actually updated properly. Be sure to replace "..." with the actual subject from pilerget.

[Peter Bernsmann]

I exported the subject with pilerget. After updating the subject in the metadata table with the exported string its shown undecoded in the gui:

[Janos SUTO]

You set subject='...' the raw, encoded header. You should have set the string pilerget showed to you.

[Peter Bernsmann]

Yes, so I did.. Pilerget only shows the encoded header.

[Janos SUTO]

Hmm, it seems that the GUI displays the subject properly, as it gets the value from the message itself, however the subject value is queried from the metadata table. So if it really holds the decoded subject (will you check it with the select query above?), then I'm not sure why you see it wrong.

[Peter Bernsmann]

I think, the GUI is ok. The problem is that subjects decoded like this example are not correctly imported in metadata table. In this case the subject field is empty. And then, of course, the GUI shows an empty subject in the list view in the upper half.

[eXtremeSHOK]

Whats the database encoding ? Whats the character set of the table ?

[Peter Bernsmann]

character_set_database=utf8

Collation of 'metadata' is utf8_general_ci

[Janos SUTO]

Ehh, I couldn't express myself. So one more time. The lower pane (message view) decodes the subject line got from the message itself. The upper pane (search results) simply queries the metadata table.

You claimed that you had fixed it with the update query (be sure to check it with the select query I mentioned), so I don't understand why you still see the undecoded subject.

[Peter Bernsmann]

Hmm.. I claimed that the GUI isn´t broken.

Let´s try it again:

• In the archive there are many emails like this example. The subject is filled in the message view, but it´s empty in the upper pane.
• If I update the subject manually in the metadata-table, the updated value ist shown in the upper pane.
• 'pilertest 919847.eml' shows a messed subject like this: ▒▒:▒j▒m▒$
• pilerget shows only an encoded subject, that begins with "=?utf-8?B?TMO2c3VuZyB6dSBU"
• maybe pilerimport cannot decode the subject in some cases and then it writes an empty subject-string in the metadata table.

[Janos SUTO]

OK, I see it now. Pilerget doesn't perform any decoding, it retrieves the email as it is from the store directory. However checking the subject myself I has turned out there's a bug in the parser, that's why the bogus subject reported by pilertest. I'll be working on it, and come back when I managed to return the same result you can see in the preview pane.

[Janos SUTO]

OK, I've found and fixed the problem. Please either apply the diff (see https://bitbucket.org/jsuto/piler/commits/b9a3f8d7c0f4ff2543e259830826b86a50b0ba09) or download parser.c (https://bitbucket.org/jsuto/piler/raw/b9a3f8d7c0f4ff2543e259830826b86a50b0ba09/src/parser.c), and recompile piler. After the recompilation it should properly display the subject just as it's in the preview pane.

[Peter Bernsmann]

Thanx for resolving it! After updating pilertest displays the currect subject. :) Is there an offical way to update the false-blank-subjects with correct values? In my metadata tabe there are about 45000 entries with a blank subject.

[Janos SUTO]

No official way. I'll try to hack a contrib utility allowing you to update the subject field. I'll let you know when it's ready to battle test.

[eXtremeSHOK]

Wouldn't it be easier to just dump those emails and reimport ?

[Janos SUTO]

I've just fabricated a utility, and added to the master branch. It reads the given message, then finds the matching serial based on the message_id, and finally updates the subject column. Try it on a few messages, check the result, and let me know how it goes.

[Peter Bernsmann]

It´s a litte bit tricky, but it worked! Thanks!

• mysql: get list of piler_id´s where subject is blank

• pilerexport: Export mails do eml-files

• fixmetadata: fix database with exported files

• reindex: update index

[Janos SUTO]

Great! I'll add it to the FAQ section :-)