Issue 133 Calendar Feed Returned No Events - edmozley/freeitsm GitHub Wiki

The calendar subscription returned an empty calendar (issue #133)

Reported by mbsouth ยท Fixed in #1500 and #1501

1. What you saw

You subscribed to your own scheduled work โ€” the "Subscribe" link on the Tickets calendar โ€” and it had been working. Then, after updating from master on 2 September, the calendar went empty.

Not an error. Not a broken link. Thunderbird, Outlook, Google Calendar and Apple Calendar all reported the subscription as perfectly healthy, refreshed it on schedule, and showed nothing in it.

Opening the link directly showed why. The file was real, valid, and empty:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//FreeITSM//Calendar//EN
CALSCALE:GREGORIAN
METHOD:PUBLISH
X-WR-CALNAME:FreeITSM โ€” my scheduled work
X-WR-TIMEZONE:Europe/London
REFRESH-INTERVAL;VALUE=DURATION:PT6H
X-PUBLISHED-TTL:PT6H
END:VCALENDAR

249 bytes. Everything a calendar client needs to accept a subscription, and not one event. mbsouth described it exactly: "an ICS file that contains only metadata but no calendar entries".

2. What was actually wrong

Two values handed to a database query in the wrong order.

The query that finds your scheduled work looks like this. Read the two ? marks in the order they appear:

WHERE t.owner_id = ?                                    -- 1st placeholder
  AND t.work_start_datetime IS NOT NULL
  AND t.work_start_datetime >= (? - INTERVAL 3 MONTH)   -- 2nd placeholder
  AND t.deleted_datetime IS NULL
  AND COALESCE(ts.is_closed, 0) = 0

And this is what was passed to it:

$stmt->execute([naive_now(), $analystId]);

The date first, the analyst second โ€” the opposite of the order the query asks for. So the database was asked two questions that cannot have answers:

The query asked With the values as passed Result
owner_id = ? owner_id = '2026-09-07 23:19:49' MySQL converts a non-numeric string to 0 when comparing it to a number, so this asks for tickets owned by analyst 0 โ€” who does not exist
work_start_datetime >= (? - INTERVAL 3 MONTH) work_start_datetime >= (1 - INTERVAL 3 MONTH) 1 is not a date, so the subtraction produces NULL, and every comparison against NULL is neither true nor false

Either one alone returns nothing. Together they made the result certain.

Why nothing failed loudly

This is the part worth dwelling on, because it is why the bug reached people at all.

Nothing here is an error. MySQL does not object to comparing a number with a date-shaped string โ€” it quietly converts and moves on. It does not object to subtracting three months from the number 1 โ€” it returns NULL. The query ran successfully and returned zero rows, which is a perfectly ordinary thing for a query to do.

The feed then did exactly what it should with zero rows: it wrote a correct, well-formed, empty calendar. Every layer behaved impeccably. That is why calendar clients reported success, and why an installation could sit in this state indefinitely without a single line in a log.

3. It was three places, not one โ€” and the pattern is exact

The fault arrived with #1446, the structural half of storing every date in UTC (issue #126). That change pinned every database connection to UTC, which meant a set of comparisons that had been relying on NOW() returning the server's local clock had to start passing an explicit wall-clock value instead.

Four queries gained that new value. Three of the four got it wrong, and the same way each time:

File Query Before After
api/tickets/schedule_feed.php your scheduled tickets execute([$analystId]) execute([naive_now(), $analystId]) โŒ
api/tickets/schedule_feed.php your task deadlines execute([$analystId]) execute([$tkNow, $tkNow, $analystId]) โŒ
api/tickets/calendar_enrolment.php the Microsoft 365 back-fill execute([$analystId]) execute([naive_now(), $analystId]) โŒ
api/calendar/feed.php the shared team calendar execute([]) execute([naive_now()]) โœ…

Every call that already had a parameter got the new value put at the front of the list. In the SQL, the new placeholder had been added at the end โ€” the last condition in the WHERE clause. In the PHP, it went in at the start, because that is where it sits in the thinking: the cutoff date is the new subject, so it leads.

The one query that came out right is the only one that had no existing parameter to get out of order.

That is not four independent slips. It is one habit, applied four times, which happened to be harmless exactly once.

The half nobody reported

The third row above is a separate user-visible bug that no one had raised, and it is worth naming on its own.

When you switch your calendar over to the direct Microsoft 365 connection, FreeITSM back-fills the work you already have scheduled, so the calendar is useful the moment you turn it on. The code carries a comment saying precisely why:

Switching ON backfills what is already scheduled, rather than only affecting tickets touched from now on โ€” an empty calendar after opting in reads as "it didn't work".

With the parameters reversed, that back-fill selected nothing. Switching the connection on left the calendar empty โ€” which is the exact failure the comment says the code exists to prevent, and which is indistinguishable from the feature simply not working. That is presumably why it went unreported: there is nothing to report except "I turned it on and nothing happened".

4. The fix

The parameters could have been swapped and that would have been the end of it. They were named instead:

WHERE t.owner_id = :analyst
  AND t.work_start_datetime >= (:cutoff - INTERVAL 3 MONTH)

$stmt->execute([':analyst' => $analystId, ':cutoff' => naive_now()]);

A named parameter cannot be in the wrong order, because it has no order. Given that positional binding is what made this possible and that it went wrong three times in a single commit, correcting the symptom and leaving the mechanism in place would have been the smaller half of the job.

One detail in the task-deadline query is worth explaining, because it looks like duplication:

$stmt->execute([':analyst' => $analystId, ':cutoff_work' => $tkNow, ':cutoff_due' => $tkNow]);

The same value, under two names. PDO only lets you reuse one named placeholder in several spots while it is emulating prepared statements โ€” and whether it emulates them is a connection setting, not a guarantee. Two names cost nothing and cannot break on somebody else's configuration.

5. Files changed

File Change
๐ŸŸฅ api/tickets/schedule_feed.php Both queries โ€” your scheduled tickets and your task deadlines โ€” switched to named parameters. This is the file behind the reported symptom.
๐ŸŸฅ api/tickets/calendar_enrolment.php The Microsoft 365 back-fill query, same fix. The unreported half.
๐ŸŸฆ CHANGELOG.local.md #1500 and #1501

๐ŸŸฉ api/calendar/feed.php, the shared team calendar, was checked and left alone. It has a single placeholder, it was passed correctly, and 96 of its 96 events were confirmed to match its window. If your team calendar was working, that is why.

6. How it was proved

Three levels, because the first one on its own would not have been enough.

At the query. The feed's exact SQL, run against real data both ways:

AS SHIPPED  execute([naive_now(), analystId]) -> 0 rows
CORRECTED   execute([analystId, naive_now()]) -> 16 rows

and the two impossible comparisons confirmed directly: '2026-09-07 23:19:49' = 1 returns 0, and 1 - INTERVAL 3 MONTH returns NULL.

At the feed. The real endpoint fetched over HTTP with a real subscription token:

Bytes Events
As shipped 249 0
Fixed 6385 21 (16 tickets, 5 task deadlines)

In a browser. The same URL opened by hand, returning the full calendar.

โš ๏ธ The measurement that lied, and how it was caught

The first before-and-after over HTTP reported 21 events for the broken code as well. If that had been believed, the conclusion would have been that the bug did not reproduce and the reporter was mistaken.

The cause was opcache. PHP was configured with opcache.revalidate_freq=2, meaning a source file changed less than two seconds ago may still be served from cached bytecode. The test had swapped the file and fetched it milliseconds later, so both "before" and "after" ran the same code.

It was caught only because it contradicted the query-level result, which had already shown 0 rows against 16. Two independent measurements disagreeing is what exposed it; one measurement on its own would have been believed.

Re-run with the file swap and the fetch several seconds apart, the empty 249-byte calendar appeared as expected. If you are testing a PHP change by hitting a URL, put a few seconds between the edit and the request, or the server may still be running your old code.

7. What this means for you

Are you affected? If your installation was updated from master on or after 2 September 2026, or is running 1.1.0 or 1.1.1, then yes โ€” your subscription feed has been returning an empty calendar, whether or not you noticed.

Was anything lost? No. Nothing was deleted, corrupted or changed. The feed builds itself from live data every time it is requested, so it was never storing anything that could be lost โ€” it was only ever failing to select. Update, and everything reappears.

Do you need to re-subscribe? No. Your existing subscription URL is unchanged and still valid. Your calendar client will pick the entries up on its next scheduled refresh, which for most clients is somewhere between a few hours and a day. If you want them immediately, most clients offer a manual refresh on the calendar's context menu.

If you use the direct Microsoft 365 connection, its back-fill was also failing. After updating, it fills in on the next change to a ticket. To populate it straight away, turn the setting off and on again.

8. Not covered here

The same issue asked two further questions, both of which are feature requests rather than parts of this bug, and neither is answered by this fix:

  • CalDAV / CardDAV support (the reporter runs SabreDAV). FreeITSM currently offers two calendar routes: a Microsoft Graph push, and the universal read-only iCalendar subscription this page is about.
  • Synchronising ticket contacts with a CardDAV address book, including letting people maintain their own details through the self-service portal for GDPR Article 16 purposes.

Both are tracked on the issue itself rather than here.

Related pages