1 Topic by gilles.quenot (edited by gilles.quenot 2019-11-14 02:57:36)

  • gilles.quenot
  • Member
  • Offline
  • Registered: 2016-12-05
  • Posts: 61

Topic: Tons of contacts dupes after sogo restore

- iRedMail 0.9.9
- Linux debian9
- Store mail accounts in MariaDB
- Web server Nginx

====

Hi,

my backup file (done by iredmail sogo backup script) is 55MB.

What I have done :

sogo-tool restore -p ./dir <name@domain.tld>
sogo-tool restore -f ALL ./dir <name@domain.tld>

It takes =~5 hours to restore with sogo-tool restore -f ALL ./dir name

Now I have tons of contacts dupes in sogo UI, I have > 200000 contacts where I had around 1000 contacts.

Is it a known issue ? Is there a solution to remove dupes ?

Regards, GQ

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,090

Re: Tons of contacts dupes after sogo restore

Possible solutions:

1) Do you have backup file which was generated by /var/vmail/backup/backup_mysql.sh? Check /var/vmail/backup/mysql/. If old SOGo is same version as the one running on current system, you have restore the sql file with sql command like "mysql sogo < /path/to/backup.sql". Then recreate SQL VIEW "sogo.users".

2) Use my script:

#!/usr/bin/env bash
#
# Author: Zhang Huangbin <zhb@iredmail.org>
# Usage: bash restore-sogo.sh /path/to/sogo/backup/dir /etc/sogo/sieve.cred

export PATH="/usr/local/GNUstep/Local/Tools/Admin:$PATH"

# The directory contains backup files generated by 'sogo-tool'.
BACKUPDIR="$1"
SIEVE_CREDENTIAL="$2"

CMD_SOGO_TOOL='sogo-tool'

[ -d ${BACKUPDIR} ] || (echo "Directory doesn't exist: ${BACKUPDIR}"; exit 255)
[ -f ${SIEVE_CREDENTIAL} ] || (echo "File doesn't exist: ${SIEVE_CREDENTIAL}"; exit 255)

cd ${BACKUPDIR}

for user in $(ls); do
    echo "* Restoring ${user} - 1"
    ${CMD_SOGO_TOOL} restore -f ALL ${BACKUPDIR} ${user}
    ${CMD_SOGO_TOOL} restore -p ${BACKUPDIR} ${user}
done

echo "* Restarting memcached service."
service memcached restart
for user in $(ls); do
    echo "* Restoring ${user} - 2"
    ${CMD_SOGO_TOOL} restore -p ${BACKUPDIR} ${user}
done

# For Sieve rules
for user in $(ls); do
    echo "* Restoring sieve rules for user: ${user}"
    ${CMD_SOGO_TOOL} restore -p -c ${SIEVE_CREDENTIAL} ${BACKUPDIR} ${user}
done

Save above code as file "restore-sogo.sh". Sample usage: 

bash restore-sogo.sh /var/vmail/backup/sogo/2019/04/03/ /etc/sogo/sieve.cred

Note: file /etc/sogo/sieve.cred must contain a Dovecot master user.

3 Reply by gilles.quenot (edited by gilles.quenot 2019-11-14 07:19:56)

  • gilles.quenot
  • Member
  • Offline
  • Registered: 2016-12-05
  • Posts: 61

Re: Tons of contacts dupes after sogo restore

Hello,

I think you misunderstand my issue.

I just want to remove duplicate contacts.

After digging around, I have found the right tool : 

# sogo-tool remove-doubles me "Carnet d'adresses personnel"

But I get MySQL error : 

<0x0xb8d380[GCSFolderManager]> ERROR(-[GCSFolderManager performSQL:]): cannot execute
  SQL 'SELECT c_folder_id, c_path, c_location, c_quick_location, c_acl_location, c_folder_type FROM sogo_folder_info WHERE c_path1 = 'Users' AND c_path2 = 'me' AND c_path3 = 'Contacts' AND c_path4 = 'Carnet\ d'adresses personnel'':
  <MySQL4Exception: 0xd75c30> NAME:ExecutionFailed REASON:You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'adresses personnel'' at line 1
<0x0xb8d380[GCSFolderManager]> ERROR(-[GCSFolderManager recordAtPath:]): executing SQL failed: 'SELECT c_folder_id, c_path, c_location, c_quick_location, c_acl_location, c_folder_type FROM sogo_folder_info WHERE c_path1 = 'Users' AND c_path2 = 'me' AND c_path3 = 'Contacts' AND c_path4 = 'Carnet\ d'adresses personnel''
Folder 'Carnet\ d'adresses personnel' of user 'me' not found.

If I search my folders ID, I get : 

MariaDB [sogo]> select distinct c_foldername from sogo_folder_info
+------------------------------------+
| c_foldername                        |
+------------------------------------+
| xxxxxxxxxxxxx                     |
| xxxxxxxxxxxxx                     |
| Carnet d'adresses personnel   |
| Personal Calendar                  |
+------------------------------------+

Any idea ?

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,090

Re: Tons of contacts dupes after sogo restore

Seems your sogo sql table structure is not updated to the latest version, but i'm not sure whether it's the root cause, maybe a bug of the sogo-tool.

I suggest posting to SOGo mailing list to get help from SOGo developers.

By the way, replied after 6 months? Maybe i should not reply again because you don't really need a solution big_smile

5 Reply by gilles.quenot (edited by gilles.quenot 2019-11-29 12:08:20)

  • gilles.quenot
  • Member
  • Offline
  • Registered: 2016-12-05
  • Posts: 61

Re: Tons of contacts dupes after sogo restore

Got it to work !

sogo-tool remove-doubles me@domain.tld personal

It's not very well documented what is the folder ID !

From the output of sogo-tool : 

# sogo-tool remove-doubles
Usage: remove-doubles USER FOLDER

         USER       the owner of the contact folder
         FOLDER     the id of the folder to clean up

got 'personal' string from within the sogo URL.

I have 220 000 contacts, 52000 dupe detected, ran 'strace', I think it will run for more than half a day (one DELETE per second).

Issue is not new, yes 6 month, was very annoying, search via web UI was mandatory (it took a long time to get a single contact).