Mailcow-dockerized: SOGo EAS sync created 104505 tasks

[bluewalk]

I've had problems with syncing after a server failure so decided to reset the ActiveSync cache for devices. This worked flawlessly untill a couple of days ago SOGo would run out of memory (6GB).

I went ahead and checked SOGo hang on Calendar Tasks, looking at my Reminders app on my macbook showed I had about 104505 reminders (tasks) and with every sync 26 were added.
I immediately removed the sync from all devices and trying to clean out the tasks.

Unfortunately I cannot find how to do this without destroying my SOGo profile (which I don't want due to set labels, rules, preferences).

Is there any way to clean this up using sogo-tool or via the DB?

[andryyy]

Did you upgrade mac OS prior to this?

[bluewalk]

Yes I did, yesterday. Although those reminders also show up on my iPad and iPhone. I actually saw them increasing with 26 a minute on there as well

[andryyy]

Funny enough I have a customer who had the same thing happen to him today.

It was on hosted mailcow, but anyway... I just searched the duplicates with some regex and time (...). Should work like this:

# Remove account from Mac, then...
root@mx:/opt/mailcow-dockerized# docker-compose exec sogo-mailcow bash
root@be9139c73f04:/# su -s /bin/bash sogo
sogo@be9139c73f04:/$ cd /tmp/
sogo@be9139c73f04:/tmp$ /usr/sbin/sogo-tool backup /tmp [email protected]
<0x0x28761b0[SOGoCache]> Cache cleanup interval set every 900.000000 seconds
<0x0x28761b0[SOGoCache]> Using host(s) 'memcached' as server(s)
sogo@be9139c73f04:/tmp$ exit
root@be9139c73f04:/# cp /tmp/andre\@develcow.de /etc/sogo/
root@be9139c73f04:/# exit
root@mx:/opt/mailcow-dockerized# cp data/conf/sogo/[email protected] data/conf/sogo/[email protected]_original
# Now edit the file with whichever editor - nano is just an example and will probably not be useful here...
root@mx:/opt/mailcow-dockerized# nano data/conf/sogo/[email protected]
# Next...
root@mx:/opt/mailcow-dockerized# docker-compose exec sogo-mailcow bash
root@be9139c73f04:/# su -s /bin/bash sogo
sogo@be9139c73f04:/$ /usr/sbin/sogo-tool remove [email protected]
sogo@be9139c73f04:/$ cp /etc/sogo/andre\@develcow.de /tmp/andre\@develcow.de
sogo@be9139c73f04:/$ /usr/sbin/sogo-tool restore -f ALL /tmp [email protected]
<0x0xee7e10[SOGoCache]> Cache cleanup interval set every 900.000000 seconds
<0x0xee7e10[SOGoCache]> Using host(s) 'memcached' as server(s)
2019-10-08 16:10:20.069 sogo-tool[3972:3972] restoring record '44-5D592100-B-61DCF280.vcf'
2019-10-08 16:10:20.122 sogo-tool[3972:3972] restoring record '45-5D90E400-1-5172D500.ics'
sogo@be9139c73f04:/$

[bluewalk]

Ah that's funny.. not so great tho. I just made the backup, only 3.6 million lines... Guess I'll be better off removing the calendar completely.

[andryyy]

If it wasn't confidential, you could send me the file for a cleanup....

[bluewalk]

I can send you the file by email, to me this is undoable... For now I seem to be able to reduce the tasks list using Outlook.

[andryyy]

If you don't mind... you can also find me at Telegram via telegram.me/diekuh

[bluewalk]

I will, but have to leave soon. I noticed the following in the logs as well:

  | [529]: [ERROR] <0x55fc809eef90[SOGoAppointmentFolder]:personal> DAV property '{urn:ietf:params:xml:ns:caldav}schedule-tag' has no matching SQL field, response could be incomplete
-- | --

I think I'm better off deleting Personal Calendar and start over. I have a backup via outlook of my calendar and important tasks now

Can I do this with sogo-tool truncate-calendar with 0 days?

[andryyy]

"They can be safely ignored."

That's what inverse.ca says.

Don't know, you need to check their tool and/or ask them for support. :)

I too am leaving for today.

[bluewalk]

I ended up logging into the mailcow database and deleting items from sogo_quick_appointment with the right folder ID and where no Start Date:

MariaDB [mailcow]> SELECT COUNT(1) FROM sogo_quick_appointment WHERE c_folder_id = 21 AND c_startdate IS NULL;
+----------+
| COUNT(1) |
+----------+
|    97472 |
+----------+
1 row in set (0.07 sec)

MariaDB [mailcow]> DELETE FROM sogo_quick_appointment WHERE c_folder_id = 21 AND c_startdate IS NULL;
Query OK, 97472 rows affected (2.39 sec)

_Note: 97472 items because I managed to delete the folder via reminders on my phone first. But sync restarted with 26 new items a second_

Now SOGo is responsive again. Was this the right way to go as sogo-tool truncate-calendar would not take any folderId I provided? Of course did a sogo-tool cleanup on that account

[kstruessmann]

Same problem here. I will try delete them from the database. Happend after IOS13 update with the first new task using Siri.

[bluewalk]

I have a feeling it started with iOS 13 as well, hence i had almost 105k of tasks in my system. After deleting them and purging the ActiveSync cache it has been stable since this morning now. Hope it helps you too.

[andryyy]

Cannot confirm on many, many devices. :) I have only seen this with Mac OS and also only on a single device.

But thanks for the feedback! Might be useful for others anyway.

Am 09.10.2019 um 19:54 schrieb Délano [e-mail]:


I have a feeling it started with iOS 13 as well, hence i had almost 105k of tasks in my system. After deleting them and purging the ActiveSync cache it has been stable since this morning now. Hope it helps you too.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub, or unsubscribe.

[bluewalk]

I’ll close this issue then. I will give additional feedback if the issue comes back or stays fixed for at least a week.

[bluewalk]

I am reopening this issue as it has happened again. This time I haven't set up reminders (tasks) on MacOS yet, to rule that out.

I have however set it up on my iPhone and iPad (both iOS 13). It all went well for a week untill I gave a reminder/task an reminder alert yesterday evening. As till now I have 135 duplicate tasks on my iPad which somehow don't show up on my iPhone. However they are visible in SOGo.

Also in the table sogo_quick_appointment they are visible with a different c_nextalarm

I think I have a reproducable bug now (most likely an SOGo issue though).

Steps to fix

1. Login to the database

source mailcow.conf
docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME}

2. Find your SOGo folder id

SELECT * FROM sogo_folder_info ORDER BY c_path2 ASC;

You'll see something like /Users/[email protected]/Calendar/personal then write down c_folder_id

3. Check data

Run the following query to see if the data found is correct:
__make sure c_folder_id is set__

SELECT COUNT(1) AS amount, c_name, c_title FROM sogo_quick_appointment WHERE c_startdate IS NULL AND c_folder_id = X GROUP BY c_title HAVING amount > 1;

In my case there was one task having amount = 125.

4. Clean up duplicate tasks

Run the following query to clean up the duplicate tasks
__make sure c_folder_id is set__

DELETE FROM sogo_quick_appointment
WHERE c_name IN (
SELECT c_name FROM (
    SELECT 
        c_name, c_title,
        ROW_NUMBER() OVER (
            PARTITION BY c_title
            ORDER BY c_nextalarm) AS row_num
    FROM 
        sogo_quick_appointment
    where c_startdate IS NULL AND c_folder_id = X
) t
WHERE 
    row_num > 1
);

You can then run the query from step 3 again to see if there any duplicates left. Et voilla!

[xatage]

I'm just trying Mailcow out and have been facing the same issue. I connected only my iPhone and iPad via EAS, so this issue probably occurs in connection with iOS13.

I don't know how to reproduce it exactly, but the amount of some of the created tasks is just growing very fast. Those tasks are only visible on the same device and in SOGo, but not on the other connected device.

The question is: is it a SOGo or iOS13 bug? Is SOGo aware of this issue?

[andryyy]

I don't see this on my phone.

I use an iPhone with iOS 13.

You already created a bug report with SOGo?

[andryyy]

Someone needs to post SOGo logs when this happens. Nobody can help without any logs.

[bluewalk]

I don't see this on my phone.

I use an iPhone with iOS 13.

You already created a bug report with SOGo?

I think it happens when syncing multiple devices, eg. Create a task with reminder on device 1, then device 2 will sync. After reminder occurs device 2 will keep creating a new one every minute. At least, that's what happened last time which must be a SOGo issue.

I haven't created a report, as I thought it might just be me or a configuration issue.

For now I've disabled sync or reminders. Alternatively you can use CalDAV for calendars and EAS for mail and contacts of course.

[xatage]

@bluewalk
Probably you are right and it only happens if there are more than one device used. Have you already reported this issue at SOGo?

[bluewalk]

@xatage I have not yet, as stated in my previous message because I was under the impression it was just something on my side.

edit
I see it has been reported already: https://sogo.nu/bugs/view.php?id=4567
I have added a note with reference to this issue.

[andryyy]

Is/was someone able to get logs with...

  SOGoEASDebugEnabled = YES;
  MySQL4DebugEnabled = YES;

...enabled, when this happened?

Thank you, @bluewalk, for posting a SQL fix. :)

[andryyy]

@bluewalk Can you please try to replicate with the latest SOGo image? Just run update.sh. :)

[andryyy]

For the lazy:

cd /opt/mailcow-dockerized
source mailcow.conf

docker exec -i $(docker ps -qf name=mysql) mysql -u${DBUSER} -p${DBPASS} ${DBNAME} << EOF
SELECT c_folder_id, COUNT(1) AS amount, c_name, c_title FROM sogo_quick_appointment WHERE c_startdate IS NULL AND c_folder_id IN (SELECT c_folder_id FROM sogo_folder_info WHERE c_path3 = "Calendar") GROUP BY c_title HAVING amount > 1
EOF

# Replace "FOLDER-ID-HERE"

docker exec -i $(docker ps -qf name=mysql) mysql -u${DBUSER} -p${DBPASS} ${DBNAME} -BN << EOF
DELETE FROM sogo_quick_appointment
WHERE c_name IN (
SELECT c_name FROM (
    SELECT 
        c_name, c_title,
        ROW_NUMBER() OVER (
            PARTITION BY c_title
            ORDER BY c_nextalarm) AS row_num
    FROM 
        sogo_quick_appointment
    where c_startdate IS NULL AND c_folder_id = FOLDER-ID-HERE
) t
WHERE 
    row_num > 1
);
EOF

[bluewalk]

@bluewalk Can you please try to replicate with the latest SOGo image? Just run update.sh. :)

I have just tried, set a tet reminder on my iPhone with a alarm set and presto, the item appeared twice on my iPad (one with alarm and one without alarm). They also appear twice in the database. It does however seem to be better, I'll let it set for a while and see what happens.

[edit]
I've now added my reminders to my Macbook as well and now they appear 4 times on my iPad and the SOGo interface.
As long as I add an alert to a reminder/task it happens. Tasks without a reminder have no issue.

[edit2]
When adding a reminder on my iPhone by just clicking New reminder and entering the text it gets saved once.
When adding a reminder on my iPhone by just clicking New reminder and then clicking the (i) and enter a title and then clicking done, it get's saved twice.

[andryyy]

Hm, but it did not bomb your account? Edit: Thank you for testing!

[bluewalk]

No it didn’t. Just made replicas for every device that was syncing, doubles when created the way as described earlier and also one when the reminder went off.

[andryyy]

Deleting them the way you described above will not delete them from the profile though. Do you have an idea how to integrate that in your snippet? :)

[bluewalk]

Deleting them the way you described above will not delete them from the profile though. Do you have an idea how to integrate that in your snippet? :)

When I run the script they dissapear from the SOGo webmail, and (most of the times) also from the devices. If that fails, I just remove the sync and re-add it.
Besides that I did su -s /bin/bash sogo and then /usr/sbin/sogo-tool cleanup 0 [email protected]

[andryyy]

Yes, but the profile size stays the same sadly. Try to backup the profile and check yourself.

[bluewalk]

I believe you, I also kept getting a reminder on my Macbook of the event that was deleted via SOGo webinterface. Guessing that is a MacOS issue, once I hit Completed the notification dissapeared. Did you change anything to prevent the "bombing" of the account?

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.