How To Migrate Buddy List From ZeXtras Chat into Zimbra Chat

Few days ago, i am perform upgrading Zimbra NE from 8.6 into 8.8.3 on my client. When upgrading into Zimbra 8.8, i am getting problem with proxy and has been solved with adding vhost. The solution has been wrote on previous post : https://imanudin.net/2017/09/18/solved-cannot-access-webmail-zimbra-8-8-from-ip-address/

Another problem is my client use chat as another method to connect with another user besides email. They are used ZxChat. I am try to move/migrate buddy from old server into new server with this method : https://imanudin.net/2016/02/15/zextras-tips-how-to-backup-and-restore-buddy-list-on-zxchat/ but did not work. I am also try to install ZeXtras latest version, but unsupported. You can find compatibility list from here : https://wiki.zextras.com/wiki/Compatibility_List

When try to dig information about ZxChat and Zimbra Chat, they have different method to save and connect buddy on database. If using ZxChat, you can find database on /opt/zimbra/conf/zextras/zxchat. But, if using Zimbra chat, you can find database on MySQL/MariaDB. Table structure on Zimbra Chat especially RELATIONSHIP, Zimbra chat have 5 columns. But on ZxChat, it have 6 columns. I know it when open db2.script file from zxchat folder.

So, i am should be remove one column from db2.script that not used on Zimbra chat and convert it to sql. For easy my work, i am create simple script to remove column that unused. The following are how i am move buddy list from ZxChat into Zimbra Chat

– Copy db2.script from old server into Zimbra 8.8 (new server). You can find db2.script from /opt/zimbra/conf/zextras/zxchat folder on old server. I am assuming you save db2.script on /tmp folder

– Create a script to convert on the new server (Zimbra 8.8)

cd /tmp/
vi convert-zxchat.sh

Fill with the following line

#!/bin/bash
# script convert ZeXtras chat into Zimbra Chat
# By : https://imanudin.com and https://imanudin.net

# Convert User

grep -i "INSERT INTO USER" db2.script | cut -d "," -f3 --complement > user.sql
sed -i 's/$/);/' user.sql

# Convert Buddy/Relationship

grep -i "INSERT INTO RELATIONSHIP" db2.script | cut -d "(" -f2 | cut -d "," -f1 --complement > relationship.sql

# if getting problem when restore, please use below method and give comment grep -i "INSERT INTO RELATIONSHIP" above
# grep -i "INSERT INTO RELATIONSHIP" db2.script | cut -d "," -f1 --complement > relationship.sql
sed -i 's/^/INSERT INTO RELATIONSHIP VALUES(/' relationship.sql
sed -i 's/$/;/' relationship.sql

echo "User and relationship have been succeeded converted. Please see user.sql and relationship.sql in this folder"

save, give execute access and run script

chmod +x convert-zxchat.sh
bash convert-zxchat.sh

When script has been done, the script will result 2 files. The file are user.sql and relationship.sql. Those file should be import into chat database on the new Zimbra (8.8)

– Backup chat database

su - zimbra
source ~/bin/zmshutil ; zmsetvars
/opt/zimbra/common/bin/mysqldump chat -S $mysql_socket -u root --password=$mysql_root_password > /srv/chat.sql

– Insert data into user and relationship tables

mysql chat < /tmp/user.sql
mysql chat < /tmp/relationship.sql

– Restart Mailbox

zmmailboxdctl restart

Please try to check from Zimbra webmail. The buddy list be supposed appear and available on user that have buddy from ZxChat formerly.

Finally, my client can be connect again with chat

Good luck and hopefully useful 🙂