Deleting repeating Events in Team Calendar causes duplicates in database and java.lang.OutOfMemoryError

Summary

Deleting Events in Team Calendar that are part of a repeating Event cause duplicates in the database, which causes performance issues and java.lang.OutOfMemoryError when viewing Team Calendars as the duplicates are loaded back into memory when re-loading the Calendar.

Environment

• Confluence 6.9.0
• Team Calendar 6.0.0 or 5.5.4

Steps to Reproduce

This is quite tricky to reproduce with clear, concise steps. It may differ but in general

1. In Team Calendars, create an all day Event that repeats every day and never ends
2. Delete one event, at least a week after the first one
3. Check the Team Calendar Exclusion table for duplicates (this is for Postgres, adjust as necessary)

   select "EXCLUSION", count(*) from "AO_950DC3_TC_EVENTS_EXCL" group by "EXCLUSION";
   

   There should be one event listed, the deleted one

1. Click on a date before the deleted event, and select All Future Events
2. Check the Team Calendar Exclusion table for all duplicates (this is for Postgres, adjust as necessary)

   select "EXCLUSION", count(*) from "AO_950DC3_TC_EVENTS_EXCL" group by "EXCLUSION";
   

   The Events are not removed.

Diagnosis

Run the following query to see if you may be affected by this issue (this is for Postgres, adjust as necessary):

select "ALL_DAY", "EVENT_ID", "EXCLUSION", count(*) from "AO_950DC3_TC_EVENTS_EXCL" group by "ALL_DAY", "EVENT_ID", "EXCLUSION" having count(*) > 1;

If there are rows returned, it's possible you are experiencing this issue, especially if the count for any row is high.

Expected Results

There should be only be two rows in AO_950DC3_TC_EVENTS_EXCL and the Events should be removed.

Actual Results

The below exception is thrown in the atlassian-confluence.log file:

2018-05-09 16:55:31,339 ERROR [http-nio-8090-exec-7] [common.error.jersey.ThrowableExceptionMapper] toResponse Uncaught exception thrown by REST service: Java heap space
 -- referer: http://confluence.mycompany.com:8090/calendar/mycalendar.action | url: /rest/calendar-services/1.0/calendar/events.json | traceId: b85eaf70252053ab | userName: admin
java.lang.OutOfMemoryError: Java heap space

Resolution

Upgrade to Team Calendars 6.0.12 which contains a fix for this issue.

Notes

Reviewing the heap dump from the java.lang.OutOfMemory error we can see that there are a few very large threads loaded with duplicates of the data, for example

The com.mysql.jdbc.JDBC42ResultSet is pulling all the data from AO_950DC3_TC_EVENTS_EXCL. http-nio-8090-exec-2 is the associated user thread.

The URL in the HAR fill shows it as

• https://confluence.mycompany.com/rest/calendar-services/1.0/calendar/events.json?subCalendarId=02a75ad8-f7d5-4365-ba6d-02f437279e94&userTimeZoneId=Sydney%2FAustralia&start=2018-04-28T00%3A00%3A00Z&end=2018-08-08T00%3A00%3A00Z&_=1525628684224

And this returns all the data, or generates the java.lang.OutOfMemoryError.

The error is caused by this code

            excludeRecurrence(
                    baseEvent,
                    !(baseEvent.getStartDate().getDate() instanceof net.fortuna.ical4j.model.DateTime || baseEvent.getEndDate().getDate() instanceof net.fortuna.ical4j.model.DateTime)
                            ? excludeDate.withZoneRetainFields(DateTimeZone.forID(srcSubCalendar.getTimeZoneId()))
                            : excludeDate.withZone(DateTimeZone.forID(srcSubCalendar.getTimeZoneId()))
            );

            calendarDataStore.updateEvent(srcSubCalendar, baseEvent);

Workaround

The only option is to delete the duplicates in the AO_950DC3_TC_EVENTS_EXCL table and informing users to not delete All Future Events.

MySQL

You can remove the duplicates by adding then removing this index

ALTER IGNORE TABLE AO_950DC3_TC_EVENTS_EXCL ADD UNIQUE INDEX AO_950DC3_TC_EVENTS_EXCL_TEMP_INDEX (ALL_DAY, EVENT_ID, EXCLUSION);
ALTER TABLE AO_950DC3_TC_EVENTS_EXCL DROP INDEX AO_950DC3_TC_EVENTS_EXCL_TEMP_INDEX;

CREATE TABLE AO_950DC3_TC_EVENTS_EXCL_NEW LIKE AO_950DC3_TC_EVENTS_EXCL;
ALTER TABLE AO_950DC3_TC_EVENTS_EXCL_NEW ADD UNIQUE INDEX (EVENT_ID);
INSERT IGNORE INTO AO_950DC3_TC_EVENTS_EXCL_NEW SELECT * FROM AO_950DC3_TC_EVENTS_EXCL;
DROP TABLE AO_950DC3_TC_EVENTS_EXCL;
RENAME TABLE AO_950DC3_TC_EVENTS_EXCL_NEW TO AO_950DC3_TC_EVENTS_EXCL;

PostgreSQL

Delete the duplicates with (this may take some time)

delete from "AO_950DC3_TC_EVENTS_EXCL" where "ID" in (select "ID" from (select "ID", row_number() over (partition BY "EXCLUSION" order by "ID") from "AO_950DC3_TC_EVENTS_EXCL") excl_table WHERE excl_table.row_number > 1);