Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Values disappearing from index [CORE3839] #4181

Closed
firebird-automations opened this issue May 2, 2012 · 19 comments
Closed

Values disappearing from index [CORE3839] #4181

firebird-automations opened this issue May 2, 2012 · 19 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: prenosil (prenosil)

Relate to CORE3844
Is duplicated by CORE3841

We have poblems with indexes on FB2.5, basically records sometimes disappear from index,
i.e. select using index will not find the record
SELECT * FROM Tab WHERE ID = 123;
while select not using the index will return correct result:
SELECT * FROM Tab WHERE ID+0 = 123;

* All 2.5.x versions have that problem, i.e. 2.5.0, 2.5.1, 2.5.2, Linux/32, windows/64
Last version I am 100% sure is affected is WI-V2.5.1.26351 Firebird 2.5
(it also happend on WI-V2.5.2.26469 Firebird 2.5, although there was 1 or 2 days
gap between checking that indexes are o.k. and upgrading to that version)

* Just during last two months, errors occured on approx 50 production databases out of 200.
(Usual number of concurrent connections is between 1 - 10.)

* databases have more than 300 tables, but 95% of errors are on 4 indexes on 2 tables.
(All are sigle-column indexes on not null integer field.)
One of the tables is Master table which could be sometimes several times updated by triggers on Detail.
The other is log table which is insert-only.

* validation of databases never show any errors, no unusual entries in fb log.
All databases have forced-write-ON.
I am not aware of any PC/FB crashes (but they are customers' PCs...).

* errors can occur even on old records that nobody should touch recently

* quite interesting is test that compares number of values in the table and in the index, i.e.

SELECT Count(DISTINCT Id) FROM Tab;

vs.

SET STATISTICS INDEX MyIndex;
SELECT CAST(1/RDB$STATISTICS AS BIGINT) FROM RDB$INDICES WHERE rdb$index_name='...';

For normal databases, I get the same values,
for corrupted databases, the value obtained using 1/rdb$statistics is HIGHER.

Commits: 7b9dd04 2c7bcf9 eda40f7

====== Test Details ======

No test case provided.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Could you provide me with corrupted database ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Ivan, i'm sorry, but without additional info this ticket is useless and i can do nothing with it

@firebird-automations
Copy link
Collaborator Author

Commented by: prenosil (prenosil)

Seems my reply get lost somewhere ...
So, where can I send you the link to download the database ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Please, send the link to the hvlad at users sourceforge net

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

I confirm the index corruption. Currently i don't know the reason but it looks like it is specific for DESCENDING indices.
Investigating.

BTW, validation detects it and put into firebird.log message

Index 2 is corrupt on page 155282 level 0\. File: \.\.\\\.\.\\\.\.\\src\\jrd\\validation\.cpp, line: 1542
 in table EXPEDICELOG \(300\)

@firebird-automations
Copy link
Collaborator Author

Commented by: prenosil (prenosil)

I had to fix more then 100 of these corruptions, but after validating about dozen of the databases I stopped because I never encoutered any error reported in the log file. So the sample database I sent seems atypical, perhaps I should send another one ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

If you have corrupted database and validation can't find errors in it - sure i need to look at it.

Could you confirm that in all cases corrupted indices is descending and not unique ?

@firebird-automations
Copy link
Collaborator Author

Commented by: prenosil (prenosil)

Yes, all are descending not unique foreign keys:

SHOW INDEX DOKLADH_FK_ID_BALICEK;
SHOW INDEX DOKLADH_FK_ID_DAVKAPOJ;
SHOW INDEX DOKLADH_FK_ID_EXPEDICE;
SHOW INDEX EXPEDICELOG_FK_ID_EXPEDICE;
SHOW INDEX DOKLADP_FK_ID_EXPEDICE;
SHOW INDEX PRIJEMKAH_FK_ID_PRFAKTURAH;

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

The fix is committed, awaiting for feedback (if it helps)

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue relate to CORE3844 [ CORE3844 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: prenosil (prenosil)

I installed fixed version on several databases, now I will have to monitor them for several weeks to be sure the problem is gone.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Ivan, any news on this ?

@firebird-automations
Copy link
Collaborator Author

Commented by: prenosil (prenosil)

So far everything looks o.k. The corruption did not appear on any of the servers with fixed version any more.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue is duplicated by CORE3841 [ CORE3841 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Duplicate [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: No test => Not enough information

Test Details: No test case provided.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment