Microsoft 365 Calendar multiplying

[shults]

For some reason I have my deafault personal callendar 3 times. I is shown in the query without any ID. Whats should I do to correct it?

[rob_kouw]

This afternoon synching Outlook didn’t work, both calendar and email. In the meantime I added some meetings, and someone changed some.

After a reboot there was one calendar extra again. I saved a copy of the sqlite file a number of times past weeks. Today I saved two copies. I’ll try to look into the changes.

[rob_kouw]

A log from my findings. I copied the db file to my laptop, opened it with SQLite and exported the tables Calendars and Components to a csv file. Opened them in Excel, and analyzed the data in there. There are 8 sets of Agenda (Calendar), National Holidays (Feestdagen) and Birthdays (Verjaardagen).

Interesting to see there are Calendar items spread over 3 different CalendarIds (field Notebook). All three show recent modifications. In another backup of the db file of early in the morning of the same day, there were 7 sets of which only the first one ‘1b5…’ was in use as ‘Agenda’.

On my X10iii, I made a copy of the db file (see the instructions from @ahappyhuman) and opened sqlite3. I tested all queries on my laptop first, before executing them on my phone.

First instead of the cumbersome Excel routine, check the number of active CalendarIds like this. Query #1:

SELECT cal.CalendarId
     , cal.createdDate
	 , COUNT(*)
	 , cal.Name
  FROM Calendars AS cal
 INNER JOIN Components AS cmp ON cmp.Notebook = cal.CalendarId
 WHERE cal.pluginName = 'EasInvitationPlugin'
 GROUP BY cal.CalendarId
 ORDER BY cal.createdDate;

Next: delete all the calendars without Components from the table Calendars. The accompanying Calendarproperties are magically deleted too. Query #2:

DELETE FROM Calendars
 WHERE CalendarId NOT IN (SELECT DISTINCT Notebook FROM Components);

Now, I am really curious to try and change the Notebook to the newest CalendarId for the Agenda items. This works on my laptop at least, and you may have to change the word ‘Agenda’ into the appropriate word in your database. Query #3:

UPDATE Components
   SET Notebook = (SELECT CalendarId
					 FROM Calendars
					WHERE pluginName = 'EasInvitationPlugin'
					  AND Name = 'Agenda'
					ORDER BY createdDate DESC
					LIMIT 1)
 WHERE Notebook IN (SELECT CalendarId 
                      FROM Calendars 
                     WHERE pluginName = 'EasInvitationPlugin' 
                       AND Name = 'Agenda');

You may have to perform this again for other “double” Names too, e.g. ‘Feestdagen’. Query #1 will tell you this.
EDIT: This query performs on my laptop, but not in sqlite3 on my phone. See the adapted code below.

But Query #2 already clears up the number of calendars in “Manage calendars”, pull-down menu of the Calendar app. So I’ll let it be for now, and keep an eye on things.

EDIT: In the meantime, I tried Query #3 too. The correct order of things should be:
Query #1, then
Query #3, and finally
Query #2.

Like this (after a decent backup), Query #1:

SELECT cal.CalendarId
     , cal.createdDate
	 , COUNT(*)
	 , cal.Name
  FROM Calendars AS cal
 INNER JOIN Components AS cmp ON cmp.Notebook = cal.CalendarId
 WHERE cal.pluginName = 'EasInvitationPlugin'
 GROUP BY cal.CalendarId
 ORDER BY cal.createdDate;

Followed by one or several times this combination of queries with the proper Name instead of ‘Agenda’. The line are quite long as I had to overcome some error messages from sqlite3. A combination of Query #3 and Query #2:

UPDATE Components 
 SET Notebook = (SELECT CalendarId FROM Calendars AS c2 WHERE c2.pluginName = 'EasInvitationPlugin' AND c2.Name = 'Agenda' ORDER BY c2.createdDate DESC LIMIT 1)
 WHERE Notebook IN (SELECT c3.CalendarId FROM Calendars AS c3 WHERE c3.pluginName = 'EasInvitationPlugin' AND c3.Name = 'Agenda');

DELETE FROM Calendars
 WHERE CalendarId NOT IN (SELECT DISTINCT Notebook FROM Components);

EDIT: things look really clean now. A new test-event on both laptop and phone synced properly. The color changed from green to blue. I will keep you posted.

[rob_kouw]

I may have found the smoking gun: accepting an invitation on the phone.

I noticed earlier that the event does not appear to be accepted on the laptop. I accepted again.

Checking the calendars on the phone, new ones had been added.

The I restored the db from this morning without making a backup first. Don’t do that! I think I have to delete my account and recreate it.

[rob_kouw]

The SQL queries need to be tweaked, they do not work in all situations.

[ahappyhuman]

Nice find! Accepting calendar invites was my suspicion too, but I could not find a way to reproduce it reliably

[rob_kouw]

It is not the entire story though. A couple of days ago, I noticed new calendars. And I am cautious not to accept invitations on my phone now. Still improving my queries:

SELECT cal.CalendarId
     , datetime(cal.createdDate, 'unixepoch', 'localtime') AS created 
	 , datetime(cal.syncDate, 'unixepoch', 'localtime') AS lastSync
	 , SUM(CASE WHEN cmp.Notebook IS NULL THEN 0 ELSE 1 END) AS counted
	 , cal.Name
  FROM Calendars AS cal
  LEFT OUTER JOIN Components AS cmp ON cmp.Notebook = cal.CalendarId
 WHERE cal.pluginName = 'EasInvitationPlugin'
 GROUP BY cal.CalendarId
 ORDER BY cal.createdDate;

Then it showed that the newest entries had no Components attached, and they were not updated as well. I have no idea what might have happened on Sun 23 March 17:01 or the following Monday that may have caused this.

After adding a test entry within Outlook, after syncing it showed that the most recent Agenda has a syncDate, and a Component attached. So that is the most recent one now.

I’m going to update the 233 calendar items to the new CalendarId and see what happens.

DELETE FROM Calendars 
 WHERE syncDate = 0 AND pluginName = 'EasInvitationPlugin';
UPDATE Components
   SET Notebook = 'da04f9b6-85eb-44f4-a856-95007ec37c54'
 WHERE Notebook = '745a5001-ca8f-4fca-b7b2-bddb6ec846bd';

Edit 2025-04-16: keeping the old Calendars and deleting the new ones is not the way to go.
@tortoisedoc My company uses 2FA as well.

[tortoisedoc]

Quick post to highlight the findings I made.
After cleaning the (multiple duplicated) calendars with the steps above, I noticed the O365 calendars werent updating anymore ( authentication expired ?).
So I attempted to re-login to the O365 account, i.e. remove and add the account (found no other way).
We have 2FA on the corporate side, wonder if that does affect.
After re-adding the account, the existing calendars duplicated .
So I guess removing the accounts doesnt properly clean up after itself…

[rob_kouw]

After a few times of cleaning up, I notice that some of the appointments appear more than once as a Notification. Last week I had an appointment that gave 4 Notifications simultaneously. Searching the Alerts table, the ComponentId did not show double entries, so I couldn’t find the source of this behaviour.