Innodb index corruption/missing messages

[Edwin Nichols]

Over the weekend, the MySQL database experienced corruption of an index for the metadata table resulting in the Innodb engine crashing. Unfortunately, MySQL restarted and Piler continued to accept live messages from what I can tell from the logs, but nothing was written to the metadata table. I am unable to confirm this, however since I don't have the message store location if they were written, but the mail server delivered the messages since they are no longer in the queue to be delivered and did not bounce. I saw in issue #318 for a similar issue that you mentioned writing a utility to find messages in the store and recreating the database entry, but that issue was resolved without having to complete the utility. Is there any means for me to find out if I have the archive messages on disk and to create the missing database entries? I have the maillog file for the relevant time period available if that is of any help.

[Edwin Nichols]

As a side note, I found a post that explained that the /var/piler/store directory stores messages in folders based on dates, so I am narrowing down which messages came in and where they are located to validate that the messages were written to the disk - so far, it appears they were, but that they are not inserted into the metadata, rcpt, attachment tables or in the Sphinx index.

[Janos SUTO]

If piler couldn't write the metadata table, then it should not accept the message, it should return a 450 temporary error. Check the mail logs, and see the status= part. Piler logs status=stored if everything is fine, and status=error or similar otherwise. In issue 318 the issue reporter used a mysql recovery / fixing tool to salvage the database. Btw. the mentioned utility has not been created ever. Though it's possible, however it cannot recover the deduplicated attachments without the attachment table.

Let me know how the recovery goes.

[Janos SUTO]

No news is good news, I hope you made it.