Deleting Calendar from Team Calendar with more than 1000 events cause Oracle ORA-01795

Summary

Calendar in Team Calendar cannot be removed due to more than 1000 events.

Environment

• Oracle Database only

Steps to Reproduce

1. Configure latest Confluence on Oracle with Team Calendars
2. Create calendar
3. Create more than 1000 events for that calendar
4. Remove Calander

Expected Results

Deleting calendar removes the calendar

Actual Results

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

 2017-10-19 13:56:24,984 ERROR [http-nio-8090-exec-21] [calendar3.rest.ExceptionMappers.GeneralExceptionMapper] getResponseError General exception happen on calendar resources
 -- referer: http://<base-url>/wiki/calendar/mycalendar.action | url: /wiki/rest/calendar-services/1.0/calendar/subcalendars.json | traceId: e8fd0a07b99f1130 | userName: yd7.park
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Oracle
	- version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
	- minor version:1
	- major version:12
Driver:
	- name:Oracle JDBC driver
	- version:12.1.0.1.0

java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.deleteWithSQL(EntityManagedActiveObjects.java:118)
	at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.deleteWithSQL(TenantAwareActiveObjects.java:281)

Notes

This bug is specifically only for Team Calander that have more than 1000 events and using Oracle database.

Diagnostic Step

Please use the SQL below to see if this bug is applicable to you before proceeding to the workaround step

select * from "AO_950DC3_TC_SUBCALS" tc,"AO_950DC3_TC_EVENTS" te where 
dBMS_LOB.INSTR( tc.name, '<Calendar >')>0 
and te."SUB_CALENDAR_ID" = tc."ID"

Please replace the <calender> with the calendar name
The SQL above have been tested but do necessary changes accordingly to the need of your instance.

Workaround

This workaround will only work if you have more than 1000 events on the calendar
Do note that this requires removing events from the database level. Please make the necessary backup

1. Shut down Confluence
2. Run the SQL Below

   • select tc."NAME",te."SUB_CALENDAR_ID"  from "AO_950DC3_TC_SUBCALS" tc,"AO_950DC3_TC_EVENTS" te where 
     dBMS_LOB.INSTR( tc.name, '<Calender')>0 
     and te."SUB_CALENDAR_ID" = tc."ID"
     

3. Note the SUB_CALENDAR_ID
4. Run the SQL and replace the SUB_CALENDAR_ID

   • delete from "AO_950DC3_TC_EVENTS" where "SUB_CALENDAR_ID"='<SUB_CALENDAR_ID>'
     

5. Start Confluence
6. Remove Calender