Repository navigation
Access violation crashes server when a partial (conditional) index with a complex predicate is present on a table under DML load #9070
Description
Activity
Could you provide a reproducible test case or a full memory dump of the crashed process ?
Anything ? So far it is impossible nor to fix the issue nor even to confirm it.
Sorry for the slow reply, and thanks a lot for following up.
As a workaround we disabled the index, and it's been running fine ever since, so the pressure on our side eased and I honestly haven't found the time to put together a proper test case yet.
Could I ask for a little more time? I'll get a reproducible case to you next week.
Sure, awaiting for more info.
ThanksSorry for the long silence — here is the reproducible test case, and it turned out to be something
quite different from what the original report suggested.The trigger is
SET STATISTICSon the partial index while another connection is scanning that
same index. Two connections are enough, and the crash follows within seconds. Firebird 5.0.4
(SuperServer, ODS 13.1, page size 16384, stockfirebird.conf), reproduced on both Windows
Server 2016 and Windows 11.Preparing the database takes about 20 minutes, almost all of it step 2 below — the index has to
have seen some churn, a freshly built one survives this.Steps
Attached (
repro9070.zip) are four scripts and a README.01-create-database.sqlbuilds a self-contained database: 89,533 rows inDOKUMENT, ~99,000
inDOKUMENTFREIGABE, then the partial index. Its last statement prints the plan — it must
nameIX_WORKLIST, otherwise the reader would scan past the index.02-load.sql— required: run it in six parallel sessions for 15 minutes. A freshly built
index does not crash, no matter how often you repeat steps 3 and 4; rows have to move in and
out of the predicate for a while first. In production the index had been in place for two days.03-reader.sql— repeated twelve times, in two parallel sessions. It does nothing but read
over the index predicate. Two details matter: it must run inREAD COMMITTED, and it must
commit every few seconds and start over. Under a snapshot transaction, or in one long-running
transaction, nothing ever happens — the reader then works from a frozen view and never sees
the statistics written in step 4.04-set-statistics.sql— run this in a third session while the readers are going. It contains
a singleset statistics index IX_WORKLIST;.
run.ps1performs all four steps if that is easier; it is PowerShell for convenience only,
nothing about the test case itself is Windows-specific.Within seconds:
Access violation. The code attempted to access a virtual address without privilege to do so. This exception will cause the Firebird server to terminate abnormally.and both clients die with
SQLSTATE 08006.What is not required
We measured each of these separately, so this does not send anyone down the paths we spent three
days on:- No write load at the moment of the crash. Writes are only needed beforehand, to age the
index. Six concurrent writers doing ~90,000 updates each, with tenSET STATISTICSin between,
ran six minutes without a problem — as long as nobody was reading over the index. - No garbage collection pressure. In the crashing runs the backlog from oldest interesting to
next transaction stayed below 50. Separate runs that drove it past 4,000,000 with a concurrent
gbaknever crashed by themselves. - No long duplicate chains. The index holds ~1,400 entries in production and ~20,000 in the
test case; both crash. - Not the statement on its own.
SET STATISTICSrun serially — even followed by an index scan
in the same session, in a loop — never crashed, on three different databases. - Not one platform. Reproduced on Windows Server 2016 and Windows 11.
How we found it
The production database keeps a DDL audit trail, which is why this is pinned down at all. The
index was created on 2026-06-20 and behaved for two days. The firstSET STATISTICSran on
2026-06-22 at 12:23:03; the index was dropped 68 minutes later, recreated at 13:37:23, and dropped
again after 109 seconds:2026-06-20 15:50:55 create index … on DOKUMENT (TYP) where … 2026-06-22 12:23:03 SET STATISTICS INDEX … 2026-06-22 13:31:43 drop index 2026-06-22 13:37:23 create index … 2026-06-22 13:39:12 drop indexThat two-day gap is what pointed at the statement rather than the index. It also explains the
original report: we assumed plain DML load was the cause, because that was what we happened to be
watching.One more detail from back then, in case it helps: the nightly
gbakof that night never
completed — it died with the server, which is why we have no backup from the night the index was
active.A full memory dump of a crashed 5.0.4 process is available (from the synthetic test database, no
customer data) — say the word and I will attach it.Note on the identifiers: table and column names are German and kept verbatim from our schema. In
the original report I generalized them, and in hindsight that was a mistake — it made it
impossible to tell whether the reported DDL matched the real one. These are the real ones.Thanks, looking into it
The fix is preparing, thanks for patience
Reacted by haba-beton- linked a pull request that will close this issueFixed bug #9070 : Access violation crashes server when a partial (conditional) index with a complex predicate is present on a table under DML load #9178
on Oct 1, 2026
Firebird version: 5.0.4
ODS: 13.1
Page size: 16384
SQL dialect: 3
OS: Windows Server 2016
Summary
Creating a partial index (CREATE INDEX … WHERE …) with a complex multi-column predicate on a large, frequently-modified table caused the server to begin crashing with access violations that terminate the entire server process, dropping all client connections simultaneously. Crashes occur under normal operational DML/query load (not only during the nightly sweep/backup). DROP INDEX immediately and permanently stops the crashes; no other change was made. Reproduced on two independent databases carrying the same DDL.
firebird.log entry (repeated, multiple times per minute under load)
Access violation.
The code attempted to access a virtual address without privilege to do so.
This exception will cause the Firebird server to terminate abnormally.
Clients concurrently see Error reading/writing data to the connection (isc 335544726 / 335544727) and intermittent connection refused while the process restarts. The Oldest Interesting Transaction stopped advancing.
The index involved
Partial index on a large, high-churn table (object/column names generalized; original is German):
create index IX_WORKLIST on DOKUMENT (TYP)
where STATUS <> 'VERWORFEN'
and TYP in ('EINKAUF-RECHNUNG','EINKAUF-GUTSCHRIFT')
and (
( (IXFREIGABEGESCHAEFTSLEITUNG is null or IXFREIGABEGESCHAEFTSLEITUNG = 'OFFEN')
and coalesce(IXFREIGABESACHLICH,'') not in ('ABGEWIESEN','OFFEN')
and coalesce(IXFREIGABEKAUFMAENNISCH,'') not in ('ABGEWIESEN','OFFEN')
and coalesce(IXFREIGABEPREISPRUEFUNG,'') not in ('ABGEWIESEN','OFFEN')
and 'FREIGEGEBEN' in (coalesce(IXFREIGABESACHLICH,''),coalesce(IXFREIGABEKAUFMAENNISCH,''),coalesce(IXFREIGABEPREISPRUEFUNG,'')) )
or
( IXFREIGABEGESCHAEFTSLEITUNG = 'FREIGEGEBEN'
and not ( coalesce(IXFREIGABESACHLICH,'') not in ('ABGEWIESEN','OFFEN')
and coalesce(IXFREIGABEKAUFMAENNISCH,'') not in ('ABGEWIESEN','OFFEN')
and coalesce(IXFREIGABEPREISPRUEFUNG,'') not in ('ABGEWIESEN','OFFEN')
and 'FREIGEGEBEN' in (coalesce(IXFREIGABESACHLICH,''),coalesce(IXFREIGABEKAUFMAENNISCH,''),coalesce(IXFREIGABEPREISPRUEFUNG,'')) ) )
)
The table receives continuous INSERT/UPDATE/DELETE; an AFTER trigger maintains the columns referenced in the predicate, so rows enter/leave the index frequently and the predicate is re-evaluated often. The table accumulates many record back-versions under normal load.
Behaviour
Impact
A single partial index can crash the entire SuperServer (all databases/connections), not just the offending statement.