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
- With the index present: repeated access violations crash the whole server under normal DML load; record-version cleanup cannot complete.
- After DROP INDEX: crashes stop completely; the server is stable.
Impact
A single partial index can crash the entire SuperServer (all databases/connections), not just the offending statement.
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.