HGIGA Service Docs - Powered by Discuz! Board

標題: [iSherlock//Other]iSherlock MailInfo Update Key 方法 [打印本頁]

作者: service.support 時間: 2008-4-14 14:09 標題: [iSherlock//Other]iSherlock MailInfo Update Key 方法

這是我們之前處理美和的方法, 目前只能暫時這樣子處理, 會再努力研究~看能減少這種情況或用程式自動修復(20080414)

iSherlock MailInfo Update Key 方法：

適用下列狀況

(1)當 /var/log/maillog.log 中出現如下的訊息:

Apr 14 04:02:25 maillog[7058]: mail dir= /usr/local/maillog/msg-1208095272-S3DM13.11632.01
Apr 14 04:02:25 maillog[7058]: Subject: ★↗線上視訊↖★☆優惠會員買3000'送3000☆★
Apr 14 04:02:25 maillog[7058]: charset From: x-charset, value = big5
Apr 14 04:02:25 maillog[7058]: Rdate=2008-04-13 22:01:09,Mode=RL,Type=spam_quarantine,Score=55.9,CharSet=big5
Apr 14 04:02:25 maillog[7058]: Sender=tarain1126@cowell.com.tw,Receiver=night@email.meiho.edu.tw,MessageId=<S3DM13.11632.01@mars.meiho.edu.tw>
Apr 14 04:02:25 maillog[7058]: ### Same message-id: <S3DM13.11632.01@mars.meiho.edu.tw>, MID: 16541036319839556107, doing merge
Apr 14 04:02:25 maillog[7058]: ## No Update MailInfo.Receiver
Apr 14 04:02:25 maillog[7058]: gzip mime file: took 0.0008 sec. orig size = 1858 , compressed size = 814
Apr 14 04:02:25 maillog[7058]: DBD::mysql::st execute failed: Duplicate entry '9223372036854775807-mail' for key 1 at /usr/local/iSherlock/maillog/maillog.pl

(2)然後再進入 mysql 查看 MailInfo:
# mysql iSherlock
> select MID from MailInfo order by MID desc limit 20;
發現 MID 突然變得很大, 例如:
+------------------------+
| MID                         |
+------------------------+
| 16541036319839556107 |
| 16541036319839556106 |
| 16541036319839556105 |
| 16541036319839556104 |
| 16541036319839556103 |
| 16541036319839556102 |
| 16541036319839556101 |
| 16541036319839556100 |
| 16541036319839556099 |
|          19569480 |
+------------------------+
會發現 MID 從 19569480 立即變成 16541036319839556099

=========================================================================

此時, 我們要進行以下的處理:
1. 停止 maillog.pl
# /etc/rc.d/init.d/maillog stop
# mv /etc/rc.d/init.d/maillog /etc/rc.d/init.d/maillog.old
2. 停止 MySQL, 將 MailInfo Table 變為 MailInfo_bak Table
#  /etc/rc.d/init.d/mysql stop
# cd /var/lib/mysql/iSherlock
# mv MailInfo.MYI MailInfo_bak.MYI
# mv MailInfo.MYD MailInfo_bak.MYD
# mv MailInfo.frm MailInfo_bak.frm
3. 啟動 MySQL, 透過 iSherlcok DB Schema 重建 MailInfo Table
# /etc/rc.d/init.d/mysql start
# mysql < /usr/local/iSherlock/bin/iSherlock_db.sql
4. 修正 MailInfo_bak 中的 MID, 把變成超大的 MID 改回正常
a. 先得知 MID 是在什麼時候變大
# mysql iSherlock
> select MID from MailInfo_bak order by MID desc limit 20;
+------------------------+
| MID                         |
+------------------------+
| 16541036319839556107 |
| 16541036319839556106 |
| 16541036319839556105 |
| 16541036319839556104 |
| 16541036319839556103 |
| 16541036319839556102 |
| 16541036319839556101 |
| 16541036319839556100 |
| 16541036319839556099 |
|          19569480 |
+------------------------+
是從 19569480 突然變為 16541036319839556099
b. 將超大的 MID 回復正常
所以只要 MID 大於 16541036319839556099 的, 都要減去 16541036319819986618 (這是從 16541036319839556099-19569480-1 而得的)
>  update MailInfo_bak set MID=MID-16541036319819986618 WHERE MID >= 16541036319839556099
5. 將修復好的 MailInfo_bak 的資料灌回給 MailInfo
# mysql iSherlock
>  INSERT INTO MailInfo SELECT * FROM MailInfo_bak;
6. 其它的 Table 也跟著 Update: Recipients, MailData, SMTP, AttachInfo, LogIndex, SMTP_yyyymm, , Attach_yyyymm
Update Recipients set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
Update MailData set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
Update AttachInfo set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
Update LogIndex set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
Update SMTP set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
Update Attach_200804 set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
Update SMTP_200804 set MID=MID-16541036319819986618 WHERE MID >=16541036319839556099;
7. 回復相關程序:
a. 移除 MailInfo_bak Table
# mysql iSherlock
> drop table MailInfo_ba;
b. 啟動 maillog 等相關服務
# mv /etc/rc.d/init.d/maillog.old /etc/rc.d/init.d/maillog
# /etc/rc.d/init.d/maillog start
# tail -f /var/log/maillog.log (看看有沒有正常將信件寫入資料庫)

桓基科技(股)公司  HGiga Inc.
NET研發部研發工程師
彭義昆 Patrick Peng

歡迎光臨 HGIGA Service Docs (http://docs.hgiga.com/Discuz4/)