1. Janos SUTO Avatar Janos SUTO
  2. Piler
  3. piler
  4. Issues

Wrong Date on GUI after import

Issue #359 resolved
Cassio Simões created an issue

Hi, I'm having a problem importing .EML emails from mailarchiva export, most of the dates on the GUI become today, but the dates within the emails looks good.

I ran the pilertest utility on one of those emails, and got this result:

root@piler:/tmp# pilertest 4000000053d9868224a3d45400986b7869ca.eml locale: en_US.UTF-8 build: 871 parsing... post parsing... message-id: 20140609112931.62EF381A363@webmail.cloudsupport.com.br / d05906ab5966f42f151678654455c07330688d484b0aac768729a4240bcc63a8 from: MAILER-DAEMON () to: info@smartipbx.com.br info smartipbx com br (smartipbx.com.br ) reference: subject: Undelivered Mail Returned to Sender body: This is a multi-part message in MIME format This is an automatically generated message from SendGrid I m sorry to have to tell you that your message was not able to be delivered to one of its intended recipients If you require assistance with this please contact SendGrid support cassioac 262483 fabianoXpocketmidia.com Unable to resolve MX host pocketmidia.com noerror in alookup X-SendGrid-QueueID 354142516 X-SendGrid-Sender infoXsmartipbx.com.br Arrival-Date 2014-06-09 11-31-07 Final-Recipient rfc822 fabianoXpocketmidia.com Original-Recipient rfc822 fabianoXpocketmidia.com Action expired Status Diagnostic-Code Unable to resolve MX host pocketmidia.com noerror Este e-mail não está sendo exibido corretamente Visualizar online __URL__dmanalytics1Xcom e fabiano 40pocketmidia.com a f t 1   Image SMARTiPBX __URL__wwwXsmartipbxXcomXbr …diferente inteligente     Gateways e iPBX GSM VoIP com consulta a operadora integrada   As centrais gateways SMARTiPBX são para quem busca uma solução de utilizar chips de operadoras diferentes com consulta ao banco de dados da portabilidade para escolher automaticamente a rota com menor custo em seu PABX Com a consulta a operadora você diminui os custos de telefonia em até 90 utilizando pacotes ON-NET dentro da mesma rede das operadoras de celular   Image SMART-Lite __URL__wwwXsmartipbxXcomXbr com content view article id 30 Itemid 232   Gateway SMART-Lite   O gateway SMART-Lite é um gateway GSM VoIP para quem já possui um PABX IP ex Asterisk que utilize a tecnologia SIP ou IAX e deseja utilizar chips de operadoras diferentes fazendo a consulta ao banco de dados da portabilidade para escolher automaticamente a rota com plano ilimitado dentro da própria operadora Saiba mais __URL__wwwXsmartipbxXcomXbr com content view article id 30 Itemid 232     Image SMART-ONE __URL__wwwXsmartipbxXcomXbr com content view article id 31 Itemid 233   iPBX SMART-One   A central SMART-One traz a vantagem da consulta a portabilidade para qualquer PABX já existente em sua empresa Mas também é uma solução completa de iPBX podendo ser utilizada como PABX prinipal conectando-se a qualquer tipo de linha analógica digital ou VoIP além de suportar até 300 canais GSM Saiba Mais __URL__wwwXsmartipbxXcomXbr com content view article id 31 Itemid 233   Image GSM Card   GSM Card   Nossos produtos já acompanham 4 GSM Cards para serem utilizados com 4 chips GSM porém suportam até 300 canais bastando adicionar um GSM card extra por canal       © 2014 SMARTiBX é uma marca registrada do grupo Workaround  Central de Atendimento 11-3506-4506   Image Compartilhar este e-mail no Facebook __URL__dmanalytics1Xcom http 3A 2F 2Fdmanalytics1.com 2Fcampaign 3Fd 3DVWX06851-W814- 4064-9V39-3V8V969U1W2V 26t 3D1 d s Gateways 20e 20iPBX 20GSM 2FVoIP 20com 20consulta 20a 20operadora 20inte grado Image Compartilhar este e-mail no Twitter __URL__dmanalytics1Xcom http 3A 2F 2Fdmanalytics1.com 2Fcampaign 3Fd 3DVWX06851 26t 3D1 d 2V s Gateways 20e 20iPBX 20GSM 2FVoIP 20com 20consulta 20a 20operadora 20 integrado Image Encaminhar este e-mail para um amigo __URL__dmanalytics1Xcom e fabiano 40pocketmidia.com f   Atualizar endereço de e-mail __URL__dmanalytics1Xcom e fabiano 40pocketmidia.com a f   Cancelar assinatura instantaneamente __URL__dmanalytics1Xcom e fabiano 40pocketmidia.com a 46X02ZXX-1VY8-4UZ 4-8W3V-9U0V23016U72 f Gateways e iPBX GSM VoIP com consulta a operadora integrado   Este e-mail não está sendo exibido corretamente Visualizar online   …diferente inteligente     Gateways e iPBX GSM VoIP com consulta a operadora integrada   As centrais gateways SMARTiPBX são para quem busca uma solução de utilizar chips de operadoras diferentes com consulta ao banco de dados da portabilidade para escolher automaticamente a rota com menor custo em seu PABX Com a consulta a operadora você diminui os custos de telefonia em até 90 utilizando pacotes ON-NET dentro da mesma rede das operadoras de celular     Gateway SMART-Lite   O gateway SMART-Lite é um gateway GSM VoIP para quem já possui um PABX IP ex Asterisk que utilize a tecnologia SIP ou IAX e deseja utilizar chips de operadoras diferentes fazendo a consulta ao banco de dados da portabilidade para escolher automaticamente a rota com plano ilimitado dentro da própria operadora Saiba mais       iPBX SMART-One   A central SMART-One traz a vantagem da consulta a portabilidade para qualquer PABX já existente em sua empresa Mas também é uma solução completa de iPBX podendo ser utilizada como PABX prinipal conectando-se a qualquer tipo de linha analógica digital ou VoIP além de suportar até 300 canais GSM Saiba Mais     GSM Card   Nossos produtos já acompanham 4 GSM Cards para serem utilizados com 4 chips GSM porém suportam até 300 canais bastando adicionar um GSM card extra por canal       © 2014 SMARTiBX é uma marca registrada do grupo Workaround  Central de Atendimento 11-3506-4506     Atualizar endereço de e-mail   Cancelar assinatura instantaneamente   * sent: 1406767380, delivered-date: 0 hdr len: 515 body digest: f5159db2c008acc2717af2ef3a8834ffcb84c925b871fc13b1aefd2186f8495f rules check: (null) retention period: 1627692180 i:1, name=354142516, type: multipart/alternative*, size: 0, int.name: 4000000053d9868224a3d45400986b7869ca.eml.a1, digest: e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855 attachments:other, direction: 0 spam: 0

Please find attached a screenshot of the GUI

BTW, I'm using the OVA image upgraded to piler-0.1.25-rc2.tar.gz (got it here: https://bbuseruploads.s3.amazonaws.com/jsuto/piler/downloads/piler-0.1.25-rc2.tar.gz)

Thanks

Cassio

Comments (25)

  5. Janos SUTO repo owner

    Please connect to the piler database, and show me the output of the following query:

    select sent from metadata where piler_id='4000000053d9868224a3d45400986b7869ca';

    Also I recommend you to upgrade to the master branch. It has some fixes since rc2. Make sure to check the database upgrade script, and determine if there's anything to update. Btw. have you ever run the reindex tool?

  7. Cassio Simões reporter

    I've dropped the database since my post, so I'll have to resample a new email, which follows:

    mysql> select sent from metadata where piler_id='4000000053db48aa24b24f5c00846081a8bd'; +------------+ | sent | +------------+ | 1406879904 | +------------+ 1 row in set (0.00 sec)

    Please also find attached the .EML file for this email.

  10. Janos SUTO repo owner

    Did you send these emails via smtp to the piler daemon, or used the pilerimport utility to import them?

  13. Cassio Simões reporter

    Yes I did upgrade to master branch, and I still have thousands of emails with wrong date (I've imported after the upgrade this time).

  14. daCaPo

    Hi, same problem here. I imported severalt thousand mails via IMAP. In the metadate table, they show the correct sent date, but the arrived date has been set to the import timestamp.

  15. Janos SUTO repo owner

    It was a bug in pilerimport / reindex, but for quite some time, it should be OK by now. Can you try the latest master branch? And have you ever run the reindex utility?

  16. daCaPo

    Hi,

    I've downloaded the current master, deleted all messages / recreated db according to FAQ Q/A9 and run pilerimport against IMAP again - same result. The arrived-field still gets the current timestamp, not the one from the IMAP mail.

  17. Janos SUTO repo owner

    OK. I've just imported a week old message, and indeed the arrived field has the current timestamp, since the idea of that field is to keep track when a message is actually archived. Can you describe what problem does it pose?

  18. daCaPo

    Till now, we've used a very rudimentary approach for e-mail archiving: Every mail was simply BCCed to an "archive" account and was there accessible via IMAP.

    Now, I want to migrate this IMAP mailbox to piler, and maintaining the original arrived date is a mandatory requirement in this scenario.

  19. Janos SUTO repo owner

    I think the 'arrived' column won't cause trouble, since there's another one (sent) which is calculated based on the original Date: header of the email. And both the preview pane, and the date1,date2 parameters in the search query are based on the "sent" column. So I suggest you to go ahead, and import the archive mailbox, and it will be fine.

  20. Matthias Drobny

    piler 1.1.1, build 904 I think the problem persists as even my sent column has the wrong date.

    mysql> SELECT `id`, `piler_id`, FROM_UNIXTIME(sent), FROM_UNIXTIME(arrived) FROM `metadata` WHERE `id` IN (12965,12964) ORDER BY `sent` DESC;
+-------+--------------------------------------+---------------------+------------------------+
| id    | piler_id                             | FROM_UNIXTIME(sent) | FROM_UNIXTIME(arrived) |
+-------+--------------------------------------+---------------------+------------------------+
| 12965 | 4000000055ff413823e2b6d40086898cef6b | 2015-09-21 01:28:46 | 2015-09-21 01:28:46    |
| 12964 | 4000000055ff41370d9f86a4006bda4b057c | 2015-09-21 01:28:45 | 2015-09-21 01:28:45    |
+-------+--------------------------------------+---------------------+------------------------+

    Reindexing didn't change anything.

    reindex -f 12964 -t 12965
/usr/local/libexec/piler/indexer.delta.sh
/usr/local/libexec/piler/indexer.main.sh
/usr/bin/indexer --quiet tag1 --rotate
/usr/bin/indexer --quiet note1 --rotate

    My test emails:

    root@archive:~# pilerget 4000000055ff413823e2b6d40086898cef6b
Delivery-date: Mon, 24 Aug 2015 14:59:19 +0200
Received: from mail.example.de [1.2.3.4]
        by archive with IMAP (fetchmail-6.3.26)
        for <root@localhost> (single-drop); Mon, 21 Sep 2015 01:28:46 +0200 (CEST)
Received: from o12-138.mailrebronker.com ([185.105.12.138])
        by wh02.fis-ffm.de with esmtp (Exim 4.76)
        (envelope-from <bounce@totalsender.com>)
        id 1ZTrLI-0005Ca-B5
        for me@example.de; Mon, 24 Aug 2015 14:59:19 +0200
Date: Mon, 24 Aug 2015 14:59:28 +0200

root@archive:~# pilerget 4000000055ff41370d9f86a4006bda4b057c
Envelope-to: me@example.de
Delivery-date: Mon, 24 Aug 2015 09:48:56 +0200
Received: from mail.example.de [1.2.3.4]
        by archive with IMAP (fetchmail-6.3.26)
        for <root@localhost> (single-drop); Mon, 21 Sep 2015 01:28:45 +0200 (CEST)
Received: from x52-12.ds.master-zen.com ([185.36.205.12])
        by mail.example.de with esmtp (Exim 4.76)
        (envelope-from <bounce@ds.master-zen.com>)
        id 1ZTmUx-0002V3-Qz
        for me@example; Mon, 24 Aug 2015 09:48:55 +0200
Date: Mon, 24 Aug 2015 09:48:51 +0200

    Environment: All emails get bcc'ed to a separate account. Afterwards fetchmail is used. Should I change this step to direct pilerimport?

    I think, that the gui (and the metadata) causing trouble because of the 2 "received" entries. Piler should show the first (in time, last in header) entry date.

    Thanks

  21. Janos SUTO repo owner

    Yes, please try a message from August with direct pilerimport. Pilerimport takes the Date field, no matter what. The piler daemon checks if it's within -7 ... +1 days range, and if not, then it uses the current time. Let me know how it goes.

  22. Matthias Drobny

    This works. The main problem lies on the first bulk imported mails. They're partly from 2004 so your -7/+1 rules doesn't apply. Is there a chance/query to update the storage to reflect the correct data?

    Is this whole thing more of a bug or a feature? (Will be fixed or won't?)

    Thanks for your assistence! You're quite fast.

  23. Janos SUTO repo owner

    Well, I'd rather call it a feature, though probably there's some room for improvements. Eg. it may be worth to use the Date field, no matter what value it has. I've seen some spam emails from 2036, but a legitimate email usually has a proper Date header. Note that the -7/+1 rule applies to the time of receiving the email.

    The fix is not entirely impossible, though not easy. You have to fix 2 columns in the metadata table by reading the message again. If you have a few 10k messages archived (guessed from the mysql output) (and still have the original emails), then the simplest fix is to drop the archive, reset it, then perform a direct pilerimport.

  24. Matthias Drobny

    OK. I dismissed the idea of altering my data and reimported everything as you suggested. Thanks for your help.

  26. Log in to comment
Assignee
Type
bug
Priority
major
Status
resolved
Votes
1
Watchers
4
Jira: the preferred issue tracker for Bitbucket. Join the team!