Ghost data rows added into Firebird database table?

I faced today strange case when receiving customer database for investigation.

System settings:

• Firebird server v 2.5.9.26074
• Firebird client v 2.6.5
• Database file is accessed directly by the application, i.e., it is NOT registered via aliases.conf.

When I first looked into database, everything seemed to be pretty consistent. However, during the first startup there are two rows added in certain table without any detected SQL execution. I have confirmed with debugger that the application is not adding these rows. I also used Audit and Trace inferface (fbtracemgr) and saw in log file that there are not such rows added to the database.

There is one hint that something is wrong in the original database. The table that contains the problem is using INSERT trigger to set the table row's ID column value from generator. Now the generator value seem to be one too high in the original database. This leads me to think that the "ghost data" has already been entered in the file in some sort of cache as the generator is already increment by one.

The result is that after these the two ghost rows are added, the next real addition to the table leads into exception:

 FirebirdSql.Data.FirebirdClient.FbException (0x80004005): violation of 
 PRIMARY or UNIQUE KEY constraint "INTEG_275" on table "DATALOG" ---> 
 violation of PRIMARY or UNIQUE KEY constraint "INTEG_275" on table 
 "DATALOG"

as there already exist row with equal ID that the generator suggests.

Is there persistent "unsaved data cache" that could contain row data entered during the previous application runs? What could lead to this situation? Power break during database writing or backuping?

Any thoughts?

Firebird server v 2.5.9.26074

There is no such version released.

Firebird-2.5.8.27089 http://www.firebirdsql.org/en/firebird-2-5/

Basically u seem to use some destabilized FB developers internal build, which can have any number of strange averse effects.

So I would advice to use standard released verison or if using snapshot builds is required for some untold reasons - to ask developers in firebird-support mail list - http://www.firebirdsql.org/en/support/

Though don't hold your breath for much of support over exotic Firebird builds.

UPD. Thanks to Mark, here it is: https://www.firebirdsql.org/en/firebird-2-5-0/

2.5.0 - was the first release after a significant reworking of the engine. Not the most stable, obviously. For example there was an issue with indices right in the next 2.5.1 version.

if the behavior would be repeated on standard 2.5.8 Firebird, then i would suggest exporting all the database (at least all the meta-data, but maybe the data as well) into a long text file, SQL script, and then searching for the said table name in it. For example there might be on-database-connect triggers adding some data. Or stored procedures. Or views made on triggers. Or something yet else. For example - though malpractice - even UDF function may make it's own database connection and do things, though this should be shown in FBTrace.

However, during the first startup there are two rows added in certain table

startup of what ?

will those rows still be added if you use standard tools like iSQL/FlameRobin/IBExpert/etc just to connect and then disconnect from the database?

as there already exist row with equal ID that the generator suggests

Generator can not suggest things like that. It can only suggest that once such a number was reserved for possibly being added to one or another table. It does not mean the row was actually inserted, was inserted into that table, was not deleted later.

You may try to search with indices prohibited, in case index corruption could occur, something like

  select id+0, count(*) from tableName group by 1

Also http://www.firebirdfaq.org/faq324/

when receiving customer database for investigation

BTW, how exactly did they created a copy of the database to give you? Did they made back-up (FBK) ? If not, did they stopped Firebird server before making copies?