Skip to content

Issue 369 Inbox Empty Until Database Verification

Ed Mozley edited this page Nov 16, 2026 · 1 revision

πŸ—„οΈ #369 After an upgrade, analysts' inboxes stayed empty until Database Verification was run

GitHub #369, reported by acelr22 Β· Tickets, System Β· any upgrade not followed by Database Verification; reminder added in 5.4.1 (#2982)

What you saw

After updating to 5.4.0, analysts opened Tickets and the inbox never filled: All Tickets 0, Select a folder to view tickets, and in the Messages view a red error:

Error loading emails: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'freeitsm.ticket_watchers' doesn't exist

Administrators could still see every ticket. It had happened just after changing Tickets β†’ Settings β†’ Team folders and back, so that setting looked like the cause.

What was wrong

Not the Team folders setting. I tried it both ways on a test install: with the database up to date, every analyst lists tickets either way.

The database was behind the code. An upgrade replaces FreeITSM's files; the database only gains the new version's tables and columns when an administrator runs System β†’ Database Verification. Ticket watching (#277, 5.1.0) added a ticket_watchers table, and the one rule that decides which tickets an analyst may see reads it:

// includes/ticket_department_scope.php - ticketScopeFilter()
$or[] = "EXISTS (SELECT 1 FROM ticket_watchers tds_w WHERE tds_w.ticket_id = {$p}id AND tds_w.analyst_id = $ph2)";

That rule applies to every analyst in a team. Administrators, and analysts in no team, skip it - which is why the people who could fix it saw nothing wrong, while everyone else got an empty inbox and a SQL error that did not say what to do.

Was it more than one place? The missing table broke this one rule, but the real gap is general: any new table or column breaks the pages that read it until Database Verification runs, and nothing in FreeITSM said so. The upgrade notes say to run it; nothing on screen did.

The fix

1. Database Verification records the version it brought the database up to. Only after a run with no errors:

// api/system/db_verify.php, at the end of a run
if (!array_filter($results, fn($r) => ($r['status'] ?? '') === 'error')) {
    dbVerifyRecordRun($conn);   // system_settings.db_verified_version = FREEITSM_VERSION
}

2. Administrators are reminded on every page while the code is newer than that, at the bottom of the screen: FreeITSM has been updated to 5.4.1. Run Database Verification so the database matches, or some pages will not work. Verify goes straight to the page; Hide puts it away until the browser is closed. It is not shown to anyone else, and not on the Database Verification page itself.

// includes/db_verify_status.php
function dbVerifyBehind(PDO $conn): ?array   // ['code' => '5.4.1', 'verified' => '5.4.0' | null], or null when up to date

An install that has never recorded a version counts as behind, so every install upgraded before this is reminded once, and one run clears it.

3. Everyone else gets words, not SQL. The ticket list and folder counts now answer a missing table or column (SQLSTATE 42S02 / 42S22) with The database needs updating for this version of FreeITSM. Ask an administrator to run System β†’ Database Verification. The SQL detail goes to the server's error log.

πŸ“ Files

File Change
🟣 includes/db_verify_status.php New: dbVerifyRecordRun(), dbVerifyBehind(), dbErrorIsSchemaBehind(), dbErrorForPeople()
🟒 api/system/db_verify.php Records the version after a clean run
πŸ”΅ includes/waffle-menu.php renderDbBehindNotice(), called from renderWaffleMenuJS() on every analyst page
🟒 api/tickets/get_emails.php, api/tickets/get_ticket_counts.php dbErrorForPeople() in their error answer
βšͺ lang/*/common.php common.db_behind.* in all 9 complete languages
βšͺ system/help/db-verify.php The reminder explained
🟒 tests/db-behind-notice.php 14 checks (below)

How it was verified

Reproduced first, on a test install: with ticket_watchers renamed out of the way, a non-admin analyst in a team got exactly the reporter's error from api/tickets/get_emails.php, while an administrator listed tickets normally. Control: with the table back, both list tickets - and flipping Team folders between Every team and Only the teams they belong to changed nothing.

tests/db-behind-notice.php repeats that over real HTTP and then checks the fix: the error now names Database Verification and contains no SQL; the folder counts say the same; the notice shows for an administrator when the recorded version differs, and when none was ever recorded; it is not shown to a non-admin; running Database Verification records the version; and the notice is then gone. 14 passed. The notice was also checked by eye in light and dark themes.

What this means for you

If your analysts see an empty inbox after an update: sign in as an administrator and run System β†’ Database Verification. From 5.4.1 FreeITSM reminds administrators itself, and tells everyone else what to ask for.

Related: Database Verification developer guide Β· Ticket watchers developer guide

FreeITSM

Getting Started

Modules

Multi-tenancy (planned)

Responses to feedback

Blue sky thinking

Bugs resolved

Pull requests

Links

Clone this wiki locally