Piler Database rcpt and audit table crash

[Yogesh Sharma]

Hi Team

Need help to restore piler database rcpt and audit table , new data is only storing in logs but not in piler so it’s kinda critical and i don’t have database backup is there anyway i can rebuild those table ?

Thanks

[Janos SUTO]

Issue #1167 was marked as a duplicate of this issue.

[Janos SUTO]

Based on your description the archive host is a toast. I’m not aware of such utility that brings back the lost or corrupted tables. Not sure what you have available, but the least painful solution might be to deploy a new archive, and import all your existing emails. So consider this path if it’s viable to you.

Then be sure to backup your data regularly, and monitor the archive host, don’t allow it to run out of space.

[Yogesh Sharma]

It have all the space now , what happen if i take current data backup and upgrade the database , or can i create audit and rcpt table manually because i am able to search old mails perfectly but new mails are not coming in piler but they are coming into mail.info and mail.log file
and if i have to create a new piler setup then what about the mails which have store in logs

[Janos SUTO]

If the archive host is back online, then great. The audit table can be created from scratch, it’s ok to be empty. However, the rcpt table is a crucial one. It’s used to find out if the given user has permission to see a message. With that being said, if only auditors are using the archive, you may get away with it.

Regarding the new emails. They are definitely not in the log files. Check the mail.log file, and look at the piler related entries, and figure out what’s wrong, and if piler is actually archiving the messages. Look for the lines having the “status=” string.

[Yogesh Sharma]

Yeah only auditor is using to search mails so i guess i can get away with rcpt but how can i create auditor table from scratch,
and regarding for new mail i get CESMTP ERROR in gui mode i try to restart rc.piler and rc.searchd both service are running
below are screen shot attach for your reference
1. Mail.log screen shot

‌

2. Database service status

3. rc.piler service status

‌

[Janos SUTO]

Locate the db-mysql.sql file, find the audit table schema definition in it, and create the missing table.

Btw. what’s “CESMTP ERROR in gui mode”? I asked you to show me log lines with “status=”. Also verify that piler-smtp is running.

[Yogesh Sharma]

Hi Janos
Piler-smtp is showing running but i am unable to telnet port 25 on it’s hostname and also i saw last lines and did’t find status= line in mail.log file
and for gui error i am attaching screen shot

‌

‌

[Yogesh Sharma]

logs files from system

[Janos SUTO]

Did you care to actually look inside the maillog file? Kill piler-smtp, then start it again, and try telnet localhost 25.

[Yogesh Sharma]

Hi Janos

Thanks for highlighting really appreciate your response it’s really help me

and i have restarted piler-smtp but it’s stuck after i hit piler-smtp but piler-smtp is started i have check in another session but mails are going into error folder in /var/piler/error and also getting rcpt table error in mail.log , i have uploaded new log file in same link but i also check it’s showing incorrect information in file

Apr 25 21:22:45 cearchive piler[5765]: 40000000608590571c6c40b4000b1aa46e5a: error: mysql_stmt_prepare() Incorrect information in file: './piler/rcpt.frm' => sql: INSERT INTO rcpt (id,to,todomain) VALUES(?,?,?)
Apr 25 21:22:45 cearchive piler[5765]: ERROR: 40000000608590571c6c40b4000b1aa46e5a: rollback sql stmt=DELETE FROM sph_index WHERE id=5297809
Apr 25 21:22:45 cearchive piler[5765]: ERROR: 40000000608590571c6c40b4000b1aa46e5a: rollback sql stmt=DELETE FROM rcpt WHERE id=5297809
Apr 25 21:22:45 cearchive piler-smtp[6011]: received: V9PWQE36KBD5IC4C, from=SRS@M0aNd=JW, size=9570, client=40.107.138.44, fd=14, fsync=137087
Apr 25 21:22:45 cearchive piler-smtp[6011]: connected from 40.107.139.73:65056 on fd=19 (active connections: 23)
Apr 25 21:22:45 cearchive piler-smtp[6011]: connected from 40.107.138.84:12637 on fd=28 (active connections: 24)
Apr 25 21:22:45 cearchive piler-smtp[6011]: connected from 40.107.138.50:9988 on fd=32 (active connections: 25)
Apr 25 21:22:45 cearchive piler[5764]: ERROR: 40000000608590571c6c87040058750471f3: rollback sql stmt=DELETE FROM metadata WHERE id=5297810
Apr 25 21:22:45 cearchive piler[5764]: ERROR: 40000000608590571c6c87040058750471f3: rollback sql stmt=DELETE FROM attachment WHERE piler_id='40000000608590571c6c87040058750471f3'
Apr 25 21:22:45 cearchive piler[5764]: 0/2VNTA5SUZWMNTVIM: moved to /var/piler/error/2VNTA5SUZWMNTVIM
Apr 25 21:22:45 cearchive piler[5764]: 0/2VNTA5SUZWMNTVIM: 40000000608590571c6c87040058750471f3, size=9357/4520, attachments=0, reference=, message-id=[e-mail], retention=2557, folder=0, delay=0.4623, status=error
Apr 25 21:22:45 cearchive piler-smtp[6011]: received: XAZBPVAYQR2N0ZOF, from=SRS@M0aNd=JW, size=9762, client=40.107.139.75, fd=15, fsync=75384
Apr 25 21:22:46 cearchive piler[5765]: ERROR: 40000000608590571c6c40b4000b1aa46e5a: rollback sql stmt=DELETE FROM metadata WHERE id=5297809
Apr 25 21:22:46 cearchive piler[5765]: ERROR: 40000000608590571c6c40b4000b1aa46e5a: rollback sql stmt=DELETE FROM attachment WHERE piler_id='40000000608590571c6c40b4000b1aa46e5a'
Apr 25 21:22:46 cearchive piler[5765]: 1/A3IHXNIB5VRD755C: moved to /var/piler/error/A3IHXNIB5VRD755C
Apr 25 21:22:46 cearchive piler[5765]: 1/A3IHXNIB5VRD755C: 40000000608590571c6c40b4000b1aa46e5a, size=10595/4776, attachments=0, reference=, message-id=[e-mail], retention=2557, folder=0, delay=0.5379, status=error
Apr 25 21:22:46 cearchive piler-smtp[6011]: disconnected from 40.107.138.89 on fd=12 (24 active connections)
Apr 25 21:22:46 cearchive piler-smtp[6011]: received: 4BKGKVXYF0KKHCD8, from=SRS@M0aNd=JW, size=9560, client=40.107.139.89, fd=16, fsync=55926
Apr 25 21:22:46 cearchive piler[5764]: 400000006085905737fba464003134b690fc: error: mysql_stmt_prepare() Incorrect information in file: './piler/rcpt.frm' => sql: INSERT INTO rcpt (id,to,todomain) VALUES(?,?,?)
Apr 25 21:22:46 cearchive piler[5764]: ERROR: 400000006085905737fba464003134b690fc: rollback sql stmt=DELETE FROM sph_index WHERE id=5297811
Apr 25 21:22:46 cearchive piler[5764]: ERROR: 400000006085905737fba464003134b690fc: rollback sql stmt=DELETE FROM rcpt WHERE id=5297811
Apr 25 21:22:46 cearchive piler-smtp[6011]: disconnected from 40.107.138.44 on fd=14 (23 active connections)
Apr 25 21:22:46 cearchive piler-smtp[6011]: connected from 40.107.138.82:18614 on fd=14 (active connections: 24)
Apr 25 21:22:46 cearchive piler[5764]: ERROR: 400000006085905737fba464003134b690fc: rollback sql stmt=DELETE FROM metadata WHERE id=5297811
Apr 25 21:22:46 cearchive piler[5764]: ERROR: 400000006085905737fba464003134b690fc: rollback sql stmt=DELETE FROM attachment WHERE piler_id='400000006085905737fba464003134b690fc'
Apr 25 21:22:46 cearchive piler[5764]: 0/TAB5Y0UV5A4UC5L7: moved to /var/piler/error/TAB5Y0UV5A4UC5L7
Apr 25 21:22:46 cearchive piler[5764]: 0/TAB5Y0UV5A4UC5L7: 400000006085905737fba464003134b690fc, size=8457/4136, attachments=0, reference=, message-id=[e-mail], retention=2557, folder=0, delay=0.2533, status=error
Apr 25 21:22:46 cearchive piler-smtp[6011]: received: 6SP4J9CQ5VHCOQYI, from=SRS@M0aNd=JW, size=9727, client=40.107.139.54, fd=21, fsync=96433
Apr 25 21:22:46 cearchive piler[5765]: 400000006085905800e151a4008c6941401c: error: mysql_stmt_prepare() Incorrect information in file: './piler/rcpt.frm' => sql: INSERT INTO rcpt (id,to,todomain) VALUES(?,?,?)
Apr 25 21:22:46 cearchive piler[5765]: ERROR: 400000006085905800e151a4008c6941401c: rollback sql stmt=DELETE FROM sph_index WHERE id=5297812
Apr 25 21:22:46 cearchive piler[5765]: ERROR: 400000006085905800e151a4008c6941401c: rollback sql stmt=DELETE FROM rcpt WHERE id=5297812
Apr 25 21:22:46 cearchive piler-smtp[6011]: received: CV0AS7WVC3CSKMB2, from=SRS@M0aNd=JW, size=9680, client=40.107.139.50, fd=17, fsync=141973
Apr 25 21:22:46 cearchive piler-smtp[6011]: disconnected from 40.107.139.75 on fd=15 (23 active connections)
Apr 25 21:22:46 cearchive piler[5765]: ERROR: 400000006085905800e151a4008c6941401c: rollback sql stmt=DELETE FROM metadata WHERE id=5297812
Apr 25 21:22:46 cearchive piler[5765]: ERROR: 400000006085905800e151a4008c6941401c: rollback sql stmt=DELETE FROM attachment WHERE piler_id='400000006085905800e151a4008c6941401c'
Apr 25 21:22:46 cearchive piler[5765]: 1/O4CSTL2DRF2K0K7C: moved to /var/piler/error/O4CSTL2DRF2K0K7C
Apr 25 21:22:46 cearchive piler[5765]: 1/O4CSTL2DRF2K0K7C: 400000006085905800e151a4008c6941401c, size=8460/4144, attachments=0, reference=, message-id=[e-mail], retention=2557, folder=0, delay=0.4372, status=error
Apr 25 21:22:46 cearchive piler-smtp[6011]: received: 05H4G9YISKBPMAZT, from=SRS@M0aNd=JW, size=9713, client=40.107.139.87, fd=20, fsync=86736
Apr 25 21:22:46 cearchive piler-smtp[6011]: disconnected from 40.107.139.89 on fd=16 (22 active connections)
Apr 25 21:22:46 cearchive piler[5764]: 40000000608590580b7ad5cc00cc88a1d09b: error: mysql_stmt_prepare() Incorrect information in file: './piler/rcpt.frm' => sql: INSERT INTO rcpt (id,to,todomain) VALUES(?,?,?)

can i recreate rcpt file from scratch or can we empty rcpt table ?

[Janos SUTO]

You can create it from scratch if it’s corrupted and useless. The bottomline is that you need the rcpt table.

[Janos SUTO]

Let me know if I can help you further.

[Yogesh Sharma]

Hey Janos

can you guide me about database recreation ,
and our infrastructure is interested in piler enterprises edition it would really help if you can help with that also

[Janos SUTO]

There’s a create table statement for the rcpt table in util/db-mysql.sql file. Be sure to execute it along with the index creation.