Replication failure with "Record ... is ambiguously identified"

[jussuf]

Our setup has one master and three identical replicas. All replicas are read-only. Suddenly only one of them had following error.

db2 (replica) Fri Dec 5 13:07:08 2025
        Database: /databases/my_database.fdb
        ERROR: Record in table MY_TABLE is ambiguously identified using the primary/unique key
        At segment 31680082, offset 127601

How can this happen?

Firebird TCP/IP server version LI-V4.0.6.3222 Firebird 4.0 HQbird

Database size is over 500GB. Usage is quite active, mostly with INSERT/UPDATE but MY_TABLE is somewhat special as contents are periodically deleted and new rows inserted (about 2000 rows in total).

[sim1984]

Does this table have a primary key or only a unique index/unique constraint? If the latter, are there any fields in the unique index that can contain NULLs?

[jussuf]

Table has primary key, no NULLs there.

[aafemt]

What about results of gstat -i -t MY_TABLE? Does number of nodes in the PK index matches number of record versions?

[jussuf]

Fortunately we still have copy of the database file from that time.

Analyzing database pages ...
MY_TABLE (332)
    Primary pointer page: 881, Index root page: 882
    Pointer pages: 1, data page slots: 32
    Data pages: 32, average fill: 29%
    Primary pages: 29, secondary pages: 3, swept pages: 19
    Empty pages: 14, full pages: 8
    Fill distribution:
         0 - 19% = 17
        20 - 39% = 2
        40 - 59% = 1
        60 - 79% = 11
        80 - 99% = 1

    Index MY_TABLE_PRIMARY (0)
        Root page: 32309757, depth: 2, leaf buckets: 18, nodes: 8268
        Average node length: 12.89, total dup: 5613, max dup: 104
        Average key length: 10.92, compression ratio: 2.75
        Average prefix length: 21.40, average data length: 8.60
        Clustering factor: 5617, ratio: 0.68
        Fill distribution:
             0 - 19% = 0
            20 - 39% = 9
            40 - 59% = 9
            60 - 79% = 0
            80 - 99% = 0

[aafemt]

total dup: 5613, max dup: 104

Something is definitely wrong with this index. Or rather this table.

[jussuf]

Something is definitely wrong with this index. Or rather this table.

Was wrong. After reinit it's been running happily again. Also IMHO important fact that this corruption occured only on one replica out of three. Could it be some sort of race condition in the replication code?