[area]

My client had the problem that they used Thunderbird to transfer some mail folders to Zimbra and so the "internal date" for all the emails was set to the date of the transfer (Dec 4, 2006) - see IMAP Sync and "received" date.

So I wrote a script which:
- identifies the database for a user
- dumps all the rows from the mail_item table (or just the rows for a specific folder)
- reads the dumped data and locates the raw in email under /opt/zimbra/store
- extracts the date from the raw email and converts it to yyyy-mm-dd hh:mm:ss
- creates an SQL update statement (converting yyyy-mm-dd hh:mm:ss to seconds)
- saves the SQL update statements to a file
- shows the commands to execute the file of SQL statements

Caveats:
- the script skips messages where the 'internal date' is not 04-12-2006 (dd-mm-yyyy format)
- assumes /opt/zimbra/store/0
- it worked for me - it may not for you!
- uses the Korn shell (some minor changes required for sh or bash)

Nothing like SQL and a bit of shell scripting...

Rgds - Angus

Here's the script:

#!/bin/ksh

SCRIPT=`basename $0`
USAGE="usage: [FOLDER_ID=nnnn] $SCRIPT username"

: ${DOMAIN:=yourdomain.com}
STORE_BASE=/opt/zimbra/store/0

if [ $# -ne 1 ]; then
   echo $USAGE >&2
   echo "(to list the folders for a user: echo -e \"sm username@${DOMAIN}\\\\ngaf\" | su - zimbra -c zmprov)"
   exit 1
fi

USER=$1
USERNAME=${USER}@${DOMAIN}

# Define the output files.
MB_DUMP_FILE=/tmp/mb_${USER}.list
UPD_SQL_STATS=/tmp/sql_${USER}.list
[ "$FOLDER_ID" ] && UPD_SQL_STATS=/tmp/sql_${USER}_${FOLDER_ID}.list
TBL_DUMP_FILE=/tmp/mailitems_${USER}.dump

# Delete all the output files.
for file in $MB_DUMP_FILE $UPD_SQL_STATS $TBL_DUMP_FILE; do
   [ -f $file ] && rm -f $file
done

ID=`echo "select id from mailbox where comment='${USERNAME}'" | su - zimbra -c "mysql -N zimbra"`
if [ -z "$ID" ]; then
   echo "\nerror: can't find mailbox ID for user $USERNAME!!!"
   exit 1
fi

MAILBOX_DB=mboxgroup${ID}
STORE_PATH=${STORE_BASE}/${ID}/msg/
echo "
Username  : $USERNAME
Database  : $MAILBOX_DB
Store path: $STORE_PATH"

# Columns in mailitem table:
#    mailbox_id
#    id
#    type
#    parent_id
#    folder_id
#    index_id
#    imap_id
#    date
#    size
#    volume_id
#    blob_digest
#    unread
#    flags
#    tags
#    sender
#    subject
#    name
#    metadata
#    mod_metadata
#    change_date
#    mod_content
# mailbox_id   id type parent_id folder_id index_id imap_id       date size volume_id...
# 23         7223    5      NULL      7221     7223    7223 1165200534 4842         1...

col_list="id, from_unixtime(date,'%d-%m-%Y %l:%i:%s%p'), sender, subject"
where_clause="type=5"
[ "$FOLDER_ID" ] && where_clause="$where_clause and folder_id=$FOLDER_ID"
echo "\nDumping all emails for '$USERNAME' from $MAILBOX_DB to $MB_DUMP_FILE..."
echo "(where clause: $where_clause)"
echo "select $col_list from mail_item where $where_clause" |
     su - zimbra -c "mysql --skip-column-names $MAILBOX_DB" > $MB_DUMP_FILE
email_cnt=`cat $MB_DUMP_FILE | wc -l | sed 's/  *//'`

echo "\
SET AUTOCOMMIT=0;
START TRANSACTION; " > $UPD_SQL_STATS

echo "\nReading $email_cnt emails $MB_DUMP_FILE - saving SQL update statements to $UPD_SQL_STATS..."
cat $MB_DUMP_FILE |
while read id date time cols; do
   # echo "ID=$id; date=$date; time=$time; other cols=$cols"

   # Check that there is only one file for the current ID.
   file_cnt=`ls -l ${STORE_PATH}/*/${id}-*.msg 2> /dev/null | wc -l`
   if [ "$file_cnt" = 0 ]; then
      echo "error: no files found for ID $id!!!"
      continue
   fi
   if [ "$file_cnt" -gt 1 ]; then
      echo "error: more than one file found for ID $id!!!"
      ls -l ${STORE_PATH}/*/${id}-*.msg
      continue
   fi
   if [ "$date" != "04-12-2006" ]; then
      echo "skip: date <> 04-12-2006"
      continue
   fi

   # Check that only one row matches the current ID.
   row_cnt=`echo "select count(*) as count from mail_item where id=$id" | su - zimbra -c "mysql --skip-column-names $MAILBOX_DB"`
   # echo "row_cnt=$row_cnt"
   if [ "$row_cnt" = 0 ]; then
      echo "error: can't locate row for ID $id!!!"
      continue
   fi
   if [ "$row_cnt" -gt 1 ]; then
      echo "error: more than one row found for ID $id!!!"
      continue
   fi

   # Extract the date from the raw email.
   email_date=`sed -n "s/\r$//;s/^Date: //p" ${STORE_PATH}/*/${id}-*.msg | head -1`
   if [ -z "$email_date" ]; then
      echo "error: can't extract a date from the raw email for id $id!!!"
      echo "file: `ls ${STORE_PATH}/*/${id}-*.msg`"
      continue
   fi

   # Convert the date from the email to yyyy-mm-dd hh:mm:ss format.
   email_yymmdd_hhmmss=`echo $email_date | sed -f /root/scripts/convert_date.sed`
   if [ -z "$email_yymmdd_hhmmss" ]; then
      echo "error: can't convert date ($email_date) for id $id!!!"
      continue
   fi

   # Display SQL statement to update the mailbox.
   echo "ID=$id: $email_date ==> $email_yymmdd_hhmmss"
   upd_sql="update mail_item set date=unix_timestamp('${email_yymmdd_hhmmss}') where id=$id"
   # echo "update SQL: $upd_sql"
   echo "${upd_sql};" >> $UPD_SQL_STATS
   # echo "$upd_sql" | su - zimbra -c "mysql $MAILBOX_DB"
done
echo "COMMIT;" >> $UPD_SQL_STATS

echo "
Command to backup the 'mail_item' table:

    echo \"select * into OUTFILE '$TBL_DUMP_FILE' from mail_item\" | su - zimbra -c \"mysql $MAILBOX_DB\"

Command to execute the update statements:

    su - zimbra -c \"mysql $MAILBOX_DB\" < $UPD_SQL_STATS
"

And here is the sed script to re-format the date (save as /root/scripts/convert_date.sed):

# Fri, 16 Sep 2005 08:30:39 +1000
s/^[A-Z][a-z][a-z], \([0-9][0-9]\) \([A-Z][a-z][a-z]\) \([0-9][0-9][0-9][0-9]\) \([0-9][0-9]:[0-9][0-9]:[0-9][0-9]\) +\([0-9]*\)$/\3-\2-\1 \4/
s/Jan/01/
s/Feb/02/
s/Mar/03/
s/Apr/04/
s/May/05/
s/Jun/06/
s/Jul/07/
s/Aug/08/
s/Sep/09/
s/Oct/10/
s/Nov/11/
s/Dec/12/

And here is some sample output:

FOLDER_ID=7221 ~/scripts/dump_zimbra_mailbox.ksh gray

Username  : gray@yourdomain.com
Database  : mboxgroup23
Store path: /opt/zimbra/store/0/23/msg/

Dumping all emails for 'gray@yourdomain.com' from mboxgroup23 to /tmp/mb_gray.list...
(where clause: type=5 and folder_id=7221)

Reading 52 emails /tmp/mb_gray.list - saving SQL update statements to /tmp/sql_gray_7221.list...
ID=7223: Thu, 12 Oct 2006 11:03:07 +1000 ==> 2006-10-12 11:03:07
ID=7225: Thu, 27 Jul 2006 09:31:22 +1000 ==> 2006-07-27 09:31:22
...
ID=7313: Fri, 16 Sep 2005 08:30:39 +1000 ==> 2005-09-16 08:30:39
ID=7314: Fri, 16 Sep 2005 08:41:51 +1000 ==> 2005-09-16 08:41:51

Command to backup the 'mail_item' table:

    echo "select * into OUTFILE '/tmp/mailitems_gray.dump' from mail_item" | su - zimbra -c "mysql mboxgroup23"

Command to execute the update statements:

    su - zimbra -c "mysql mboxgroup23" < /tmp/sql_gray_7221.list