Retour d'expérience Entourage -> Zimbra
General bash Entourage mysql ZimbraChangement, dans Zimbra 8 des dates des messages suite à l’importation d’une boite depuis entourage
Description du problème
Le problème est survenu lors de l’utilisation d’entourage en connexion IMAP pour importer la boite locale dans Zimbra. Entourage semble mettre les dates des messages importés à la date de l’import. La conséquence est que tous les messages dans Zimbra apparaissent avec la même date de réception. Il n’y a que lorsque l’on clique sur le message que l’on voit (dans le message) la bonne date.
Ce qui a motivé ce travail est que ce problème a été identifié alors qu’une bonne partie des messages avaient été “coupés/collés”… et qu’il n’était plus possible de revenir en arrière…
Notre système Zimbra
Notre système ne comporte qu’un seul serveur Zimbra (version 8). Les mails concernés n’ont pas encore été migrés vers la partie hsm.
Identifier les volumes de stockage
su - zimbra $ zmvolume -l Volume id: 1 name: message1 type: primaryMessage path: /opt/zimbra/store compressed: false current: true Volume id: 2 name: index1 type: index path: /opt/zimbra/index compressed: false current: true Volume id: 3 name: hsm1 type: secondaryMessage path: /hsm compressed: false current: true #### Identifier le groupe id et le dossier contenant les messages
Ces deux solutions sont différentes et il me semble que cela permet d’avoir une relative confiance dans les résultats obtenus.
Première solution
Trouver la configuration de la boite d’un utilisateur donné en passant par le ZimbraId qui est unique à chaque utilisateur.
su - zimbra $ zmprov ga mon.mail@mon.domaine.fr zimbraId zimbraId: a75fe8be-2538-4a2b-a0f2-1da770d4b395 /opt/zimbra/bin/mysql --protocol=socket --socket=/opt/zimbra/db/mysql.sock mysql > SELECT * FROM zimbra.mailbox WHERE account_id="a75fe8be-2538-4a2b-a0f2-1da770d4b395" \G *************************** 1. row *************************** id: 123 group_id: 23 account_id: a75fe8be-2538-4a2b-a0f2-1da770d4b395 index_volume_id: 2 item_id_checkpoint: 219920 contact_count: 561 size_checkpoint: 36940550395 change_checkpoint: 518332 tracking_sync: 175969 tracking_imap: 1 last_backup_at: 1391831732 comment: mon.mail@mon.domaine.fr last_soap_access: 1392244457 new_messages: 12 idx_deferred_count: 0 highest_indexed: NULL version: 2.7 last_purge_at: 1392275215 1 row in set (0.00 sec) Le champ ‘group_id’ indique 23. La base de donnée concernée est ‘mboxgroup23’. Le champ ‘id’ indique 123. Le dossier qui contient les messages est alors /opt/zimbra/store/0/123.
Deuxième solution
Demander le mailboxId de l’utilisateur et calculer le nom de la base concernée.
su - zimbra $ zmprov gmi mon.mail@mon.domaine.fr mailboxId: 123 quotaUsed: 36940603783 Le champ ‘mailboxId’ indique 123. Il n’y a que 100 groupes dans la base. La doc indique que pour avoir le numéro de la base de donnée il faut prendre le modulo sur 100 soit : 23. Ce champ indique également le nom du dossier qui contient les messages : /opt/zimbra/store/0/123.
Format des messages
ls /opt/zimbra/store/0/123/msg/0/ 2281-3973.msg 2325-4015.msg 2398-4171.msg 2562-4503.msg 2686-4707.msg 2778-4873.msg 2874-5036.msg 2950-5159.msg 3060-5459.msg 3205-5676.msg 3290-5826.msg 3418-6023.msg 3567-6352.msg 3648-6491.msg 3749-6636.msg 3840-6765.msg 3921-6911.msg 4027-7089.msg ... Le premier nombre correspond au champ ‘id’ du message et le deuxième nombre correspond au champ ‘mod_content’ :
Les messages sont stockés de la même manière dans la partie hsm avec les mêmes conventions (dans notre exemple le dossier 123 dans le hsm : /hsm/0/123/)
La base de donnée
su - zimbra $ /opt/zimbra/bin/mysql --protocol=socket --socket=/opt/zimbra/db/mysql.sock mysql > show databses; +--------------------+ | Database | +--------------------+ | information_schema | | backup | | mboxgroup1 | | mboxgroup10 | | mboxgroup100 | | mboxgroup11 | | mboxgroup12 | | mboxgroup13 | | mboxgroup14 | ... ... | mboxgroup97 | | mboxgroup98 | | mboxgroup99 | | mysql | | performance_schema | | test | | zimbra | +--------------------+ 106 rows in set (0.01 sec) On voit effectivement les 100 mboxgroup (de 1 à 100 inclus) et les bases afférentes à mysql et à zimbra. Sélection de la base 23 :
mysql > use mboxgroup23; Database changed mysql > show tables; +-----------------------+ | Tables_in_mboxgroup23 | +-----------------------+ | appointment | | appointment_dumpster | | data_source_item | | imap_folder | | imap_message | | mail_item | | mail_item_dumpster | | open_conversation | | pop3_message | | revision | | revision_dumpster | | tag | | tagged_item | | tombstone | +-----------------------+ 14 rows in set (0.00 sec) Cette base regroupe les messages des utilisateurs 23, 123, 223 (utilisateurs pour lesquel le id correspond à l’équation id % 100 = 23). Les deux tables qui nous intéressent ici sont mail_item et mail_item_dumpster. Si j’ai bien compris les messages supprimés sont dans la table mail_item_dumpster les autres étant dans mail_item; Voyons les champs de ces tables :
mysql > show columns from mail_item; +--------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+-------+ | mailbox_id | int(10) unsigned | NO | PRI | NULL | | | id | int(10) unsigned | NO | PRI | NULL | | | type | tinyint(4) | NO | | NULL | | | parent_id | int(10) unsigned | YES | | NULL | | | folder_id | int(10) unsigned | YES | | NULL | | | index_id | int(10) unsigned | YES | | NULL | | | imap_id | int(10) unsigned | YES | | NULL | | | date | int(10) unsigned | NO | | NULL | | | size | bigint(20) unsigned | NO | | NULL | | | locator | varchar(1024) | YES | | NULL | | | blob_digest | varchar(44) | YES | | NULL | | | unread | int(10) unsigned | YES | | NULL | | | flags | int(11) | NO | | 0 | | | tags | bigint(20) | NO | | 0 | | | tag_names | text | YES | | NULL | | | sender | varchar(128) | YES | | NULL | | | recipients | varchar(128) | YES | | NULL | | | subject | text | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | metadata | mediumtext | YES | | NULL | | | mod_metadata | int(10) unsigned | NO | | NULL | | | change_date | int(10) unsigned | YES | | NULL | | | mod_content | int(10) unsigned | NO | | NULL | | | uuid | varchar(127) | YES | | NULL | | +--------------+---------------------+------+-----+---------+-------+ 24 rows in set (0.00 sec) On retouve le champ mailbox_id qui contient l’identifiant de la boite de messagerie. On va l’utiliser pour s’assurer qu’on modifie effectivement les messages de la bonne boite de messagerie (123 dans notre exemple). Il y a également les champs id et mod_content qui font référence au nom du fichier stockés sur le disque. C’est eux qui vont servir pour sélectionner un message :
mysql > SELECT * FROM mail_item WHERE mailbox_id = 123 AND id = 31259 AND mod_content = 56625 LIMIT 1\G; *************************** 1. row *************************** mailbox_id: 123 id: 31259 type: 5 parent_id: 31263 folder_id: 16711 index_id: 31259 imap_id: 31259 date: 1392159600 size: 8345 locator: 1 blob_digest: WJOVKzLQQvCJ1KvxF3DYNUNy19SotnyuL3+4hpDm480= unread: 0 flags: 0 tags: 0 tag_names: NULL sender: Anonyme recipients: anonyme@anonyme.fr subject: Comment convaincre la communaut� de bouger face aux attaques ? name: NULL metadata: d1:f148:Bonjour, je suis globalement d'accord avec ....?1:s57:=?ISO-8859-e mod_metadata: 210781 change_date: 1390847848 mod_content: 56625 uuid: NULL 1 row in set (0.00 sec) ERROR: No query specified Le champ qui contient la date erronée mise par entourage est le champ date. C’est lui que nous allons mettre à jour. La table mail_item_dumpster contient exactement les mêmes champs.
Scripts
J’ai adapté le premier script (fixtimestamp.sh) et j’ai sorti de ce dernier la partie mise à jour de la base pour en faire un script spécifique (update_db.bash).
fixtimestamp.sh
Le principe de ce script est de parcourir le système de fichier où sont stockés les messages sous forme de texte, d’en extraire la date d’après le champ Date: de l’en-tête mail (cf RFC 5322 section 3.6.1 il me semble) d’en extraire l'id et le mod_content (d’après le nom du fichier) et de généré un fichier de mise à jour nommé timestamp.sql qui sera à exécuter dans la bonne base du mysql.
On passe en paramètre l’adresse mail de l’utilisateur en question : ./fixtimestamp.sh mon.mail@mon.domaine.fr
#!/bin/bash # Author: Lam Kin Yan # Email: hinyinlam [ at ] gmail.com # Date: 13-Oct-2008 # Location: Hong Kong SAR # License: GPLv3 # website: www.hinyinsolution.com # Version: 0.01 PREVIOUS_FILENAME=""; MAILBOXID=/opt/zimbra/bin/zmprov gmi $1 | sed -n '1,1s/mailboxId: //gp'; if [ "$MAILBOXID" = "" ]; then exit; fi rm -f timestamp.sql find /opt/zimbra/store/0/$MAILBOXID/ -type f -name '*.msg' | while read FILENAME_PATH; do FILENAME=basename $FILENAME\_PATH; TIMESTRING=head -n 50 $FILENAME\_PATH | grep "^Date: "| head -n 1 | sed 's/Date: //g'; TIMESTAMP=date +"%s" -s "$TIMESTRING" 2 >/dev/null; if [ "$TIMESTAMP" != "" ]; then if [ "$FILENAME" != "$PREVIOUS_FILENAME" ]; then FILENAME=echo $FILENAME | sed 's/\.msg//g'; MAILID=echo $FILENAME | cut -f1 -d\-; MODID=echo $FILENAME | cut -f2 -d\-; PREVIOUS_FILENAME=$FILENAME.msg; echo "UPDATE mail_item SET `date` = '$TIMESTAMP' WHERE mailbox_id = $MAILBOXID AND id = $MAILID AND mod_content = $MODID LIMIT 1;" > > timestamp.sql; echo "Fixing $MAILID to $TIMESTAMP"; fi else echo "----------Not ok: $FILENAME $TIMESTRING"; fi done; echo "Finished Mapping SQL." #### update_db.bash
Cette partie est celle qui va faire la mise à jour dans le mysql. Il faut passer deux paramètres au script : l’adresse de messagerie et le nom du fichier contenant les instructions sql : ./update_db mon.mail@mon.domaine.fr monfichier.sql
#!/bin/bash # Usage : ./update_db mon.mail@mon.domaine.fr monfichier.sql MYSQL_COMMAND="/usr/bin/ionice -c 3 /opt/zimbra/bin/mysql --protocol=socket --socket=/opt/zimbra/db/mysql.sock" MAILBOXID=/opt/zimbra/bin/zmprov gmi $1 | sed -n '1,1s/mailboxId: //gp'; DBGROUPID=expr $MAILBOXID % 100 rm -f update_db_output.txt; if [ "$MAILBOXID" = "" ]; then echo "No mailboxId to work with" exit; fi if [[ -r $2 ]]; then echo "Updating database ..."; cat $2 | $MYSQL_COMMAND mboxgroup$DBGROUPID > >update_db_output.txt 2 >&1; echo "Update Done"; else echo "SQL file does not exists or is not readable." fi #### Réindexation de la boite