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

Jump to bottom

FB 2.1.5: Missing entries in index (probably a side effect of CORE-2422 fix) [CORE3918] #4253

Closed
firebird-automations opened this issue Sep 7, 2012 · 12 comments
Closed

FB 2.1.5: Missing entries in index (probably a side effect of CORE-2422 fix) [CORE3918] #4253

firebird-automations opened this issue Sep 7, 2012 · 12 comments
Assignees
@hvlad
Labels
affect-version: 2.1.5 component: engine priority: major qa: not enough information resolution: duplicate type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Vadym Lebid (swan)

Duplicates CORE2966

Test Environment:
---------------
Windows7 x64
8 GB RAM
Firebird 2.1.5 x86 (Firebird-2.1.5.18496_0_Win32)
---------------------------------------------
Issue Description:
---------------
Database is just restored from fbk (gbak - r).
SQL statement
select
<SomeFields>
from
<TableWith_76842599_RecordsInIt>
where
<IndexedFieldWithCollatePXW_CYRL>=<WellKnownValue>
returns a wrong result set (less rows than expected).
Drop/create index doesn't help.
---------------------------------------------
Validation results:
---------------
gfix -z -validate -no_update -full

reports:
gfix version WI-V2.1.5.18496 Firebird 2.1
Summary of validation errors
Number of index page errors : 28

In firebird.log:
Index 1 is corrupt on page 299894 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
...
<8 such records here>
...
Index 1 is corrupt on page 321352 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
Index 1 is corrupt (missing entries) in table <TableName>

------------------------------------------------------------------------------------------
Builds tested and confirmed as being affected by this issue:
------------------------------------------------------------
2.1.5.18465
2.1.5.18471
2.1.5.18474
2.1.5.18480
2.1.5.18496
2.1.6.18505
2.1.6.18506
------------------------------------------------------------------------------------------
Builds tested and confirmed as NOT being affected by this issue:
------------------------------------------------------------
2.5.2.26538
2.1.4.18314
2.1.4.18420
2.1.4.18438
2.1.5.18464

So it seems that this issue got introduced as a side effect of Backported CORE2422 fix.

---------------------------------------------
Test database:
---------------
SQL DIALECT 3
DEFAULT CHARACTER SET WIN1251

PAGE_SIZE 16384
or
PAGE_SIZE 8192
------------------------------
Test table:
---------------
CREATE TABLE TST (
ID INTEGER NOT NULL,
DKEY VARCHAR(20) COLLATE PXW_CYRL
);
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
---------------------------------------------
Test data:
---------------
76842599 records in the table TST.
---------------------------------------------
Test SQL statements:
---------------
select *
from TST
where dkey='12213696'

Plan used: PLAN (TST INDEX (TST_DKEY))
Returns: 4 rows
---------------
select *
from TST
where dkey||''='12213696'

Plan used: PLAN (TST NATURAL)
Returns: 39 rows

--------------------------------------------------------------------------
Possible workarounds to solve the issue:
--------------------------------------------
1.Don't use COLLATE PXW_CYRL:
DROP INDEX TST_DKEY;
ALTER TABLE TST ADD DKEY_TMP VARCHAR(20);
UPDATE TST SET DKEY_TMP=DKEY;
ALTER TABLE TST DROP DKEY;
ALTER TABLE TST ALTER COLUMN DKEY_TMP TO DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
Enjoy.
2.Don't use 2-column index:
keep the field as it is (VARCHAR(20) COLLATE PXW_CYRL);
DROP INDEX TST_DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY);
That's it.
3.Delete some unnecessary records:
keep table's metadata as is;
DROP INDEX TST_DKEY;
DELETE FROM TST WHERE ID<17000000;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
So less records - no issue.
-------------------------------------------------------------------------------
Test database (fbk in 7z archive, 126 MB) will be sent on request.

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

No test database.
@firebird-automations
Copy link
Collaborator Author

Modified by: Vadym Lebid (swan)

description: Test Environment:
---------------
Windows7 x64
Firebird 2.1.5 x86 (Firebird-2.1.5.18496_0_Win32)
---------------------------------------------
Issue Description:
---------------
Database is just restored from fbk (gbak - r).
SQL statement
select
<SomeFields>
from
<TableWith_76842599_RecordsInIt>
where
<IndexedFieldWithCollatePXW_CYRL>=<WellKnownValue>
returns a wrong result set (less rows than expected).
Drop/create index doesn't help.
---------------------------------------------
Validation results:
---------------
gfix -z -validate -no_update -full

reports:
gfix version WI-V2.1.5.18496 Firebird 2.1
Summary of validation errors
Number of index page errors : 28

In firebird.log:
Index 1 is corrupt on page 299894 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
...
<8 such records here>
...
Index 1 is corrupt on page 321352 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
Index 1 is corrupt (missing entries) in table <TableName>

------------------------------------------------------------------------------------------
Builds tested and confirmed as being affected by this issue:
------------------------------------------------------------
2.1.5.18474
2.1.5.18480
2.1.5.18496
2.1.6.18505
2.1.6.18506
------------------------------------------------------------------------------------------
Builds tested and confirmed as NOT being affected by this issue:
------------------------------------------------------------
2.5.2.26538
2.1.4.18314
2.1.4.18420
2.1.4.18438

So this issue got introduced somewhen between 2.1.4.18438 and 2.1.5.18474.
I just don't have these builds handy to test.

---------------------------------------------
Test database:
---------------
SQL DIALECT 3
DEFAULT CHARACTER SET WIN1251

PAGE_SIZE 16384
or
PAGE_SIZE 8192
------------------------------
Test table:
---------------
CREATE TABLE TST (
ID INTEGER NOT NULL,
DKEY VARCHAR(20) COLLATE PXW_CYRL
);
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
---------------------------------------------
Test data:
---------------
76842599 records in the table TST.
---------------------------------------------
Test SQL statements:
---------------
select *
from TST
where dkey='12213696'

Plan used: PLAN (TST INDEX (TST_DKEY))
Returns: 4 rows
---------------
select *
from TST
where dkey||''='12213696'

Plan used: PLAN (TST NATURAL)
Returns: 39 rows

--------------------------------------------------------------------------
Possible workarounds to solve the issue:
--------------------------------------------
1.Don't use COLLATE PXW_CYRL:
DROP INDEX TST_DKEY;
ALTER TABLE TST ADD DKEY_TMP VARCHAR(20);
UPDATE TST SET DKEY_TMP=DKEY;
ALTER TABLE TST DROP DKEY;
ALTER TABLE TST ALTER COLUMN DKEY_TMP TO DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
Enjoy.
2.Don't use 2-column index:
keep the field as it is (VARCHAR(20) COLLATE PXW_CYRL);
DROP INDEX TST_DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY);
That's it.
3.Delete some unnecessary records:
keep table's metadata as is;
DROP INDEX TST_DKEY;
DELETE FROM TST WHERE ID<17000000;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
So less records - no issue.
-------------------------------------------------------------------------------
Test database (fbk in 7z archive, 126 MB) will be sent on request.

=>

Test Environment:
---------------
Windows7 x64
8 GB RAM
Firebird 2.1.5 x86 (Firebird-2.1.5.18496_0_Win32)
---------------------------------------------
Issue Description:
---------------
Database is just restored from fbk (gbak - r).
SQL statement
select
<SomeFields>
from
<TableWith_76842599_RecordsInIt>
where
<IndexedFieldWithCollatePXW_CYRL>=<WellKnownValue>
returns a wrong result set (less rows than expected).
Drop/create index doesn't help.
---------------------------------------------
Validation results:
---------------
gfix -z -validate -no_update -full

reports:
gfix version WI-V2.1.5.18496 Firebird 2.1
Summary of validation errors
Number of index page errors : 28

In firebird.log:
Index 1 is corrupt on page 299894 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
...
<8 such records here>
...
Index 1 is corrupt on page 321352 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
Index 1 is corrupt (missing entries) in table <TableName>

------------------------------------------------------------------------------------------
Builds tested and confirmed as being affected by this issue:
------------------------------------------------------------
2.1.5.18465
2.1.5.18471
2.1.5.18474
2.1.5.18480
2.1.5.18496
2.1.6.18505
2.1.6.18506
------------------------------------------------------------------------------------------
Builds tested and confirmed as NOT being affected by this issue:
------------------------------------------------------------
2.5.2.26538
2.1.4.18314
2.1.4.18420
2.1.4.18438
2.1.5.18464

So it seems that this issue got introduced as a side effect of <a href="/browse/CORE2422">Backported <strike>CORE2422</strike></a> fix.

---------------------------------------------
Test database:
---------------
SQL DIALECT 3
DEFAULT CHARACTER SET WIN1251

PAGE_SIZE 16384
or
PAGE_SIZE 8192
------------------------------
Test table:
---------------
CREATE TABLE TST (
ID INTEGER NOT NULL,
DKEY VARCHAR(20) COLLATE PXW_CYRL
);
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
---------------------------------------------
Test data:
---------------
76842599 records in the table TST.
---------------------------------------------
Test SQL statements:
---------------
select *
from TST
where dkey='12213696'

Plan used: PLAN (TST INDEX (TST_DKEY))
Returns: 4 rows
---------------
select *
from TST
where dkey||''='12213696'

Plan used: PLAN (TST NATURAL)
Returns: 39 rows

--------------------------------------------------------------------------
Possible workarounds to solve the issue:
--------------------------------------------
1.Don't use COLLATE PXW_CYRL:
DROP INDEX TST_DKEY;
ALTER TABLE TST ADD DKEY_TMP VARCHAR(20);
UPDATE TST SET DKEY_TMP=DKEY;
ALTER TABLE TST DROP DKEY;
ALTER TABLE TST ALTER COLUMN DKEY_TMP TO DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
Enjoy.
2.Don't use 2-column index:
keep the field as it is (VARCHAR(20) COLLATE PXW_CYRL);
DROP INDEX TST_DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY);
That's it.
3.Delete some unnecessary records:
keep table's metadata as is;
DROP INDEX TST_DKEY;
DELETE FROM TST WHERE ID<17000000;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
So less records - no issue.
-------------------------------------------------------------------------------
Test database (fbk in 7z archive, 126 MB) will be sent on request.

environment: Windows7 x64, Firebird 2.1.5 x86 => Windows7 x64, 8 GB RAM, Firebird 2.1.5 x86

summary: FB 2.1.5: Missing entries in 2-column index with COLLATE PXW_CYRL in 1st column => FB 2.1.5: Missing entries in index (probably a side effect of CORE2422 fix)

@firebird-automations
Copy link
Collaborator Author

Modified by: Vadym Lebid (swan)

description: Test Environment:
---------------
Windows7 x64
8 GB RAM
Firebird 2.1.5 x86 (Firebird-2.1.5.18496_0_Win32)
---------------------------------------------
Issue Description:
---------------
Database is just restored from fbk (gbak - r).
SQL statement
select
<SomeFields>
from
<TableWith_76842599_RecordsInIt>
where
<IndexedFieldWithCollatePXW_CYRL>=<WellKnownValue>
returns a wrong result set (less rows than expected).
Drop/create index doesn't help.
---------------------------------------------
Validation results:
---------------
gfix -z -validate -no_update -full

reports:
gfix version WI-V2.1.5.18496 Firebird 2.1
Summary of validation errors
Number of index page errors : 28

In firebird.log:
Index 1 is corrupt on page 299894 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
...
<8 such records here>
...
Index 1 is corrupt on page 321352 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
Index 1 is corrupt (missing entries) in table <TableName>

------------------------------------------------------------------------------------------
Builds tested and confirmed as being affected by this issue:
------------------------------------------------------------
2.1.5.18465
2.1.5.18471
2.1.5.18474
2.1.5.18480
2.1.5.18496
2.1.6.18505
2.1.6.18506
------------------------------------------------------------------------------------------
Builds tested and confirmed as NOT being affected by this issue:
------------------------------------------------------------
2.5.2.26538
2.1.4.18314
2.1.4.18420
2.1.4.18438
2.1.5.18464

So it seems that this issue got introduced as a side effect of <a href="/browse/CORE2422">Backported <strike>CORE2422</strike></a> fix.

---------------------------------------------
Test database:
---------------
SQL DIALECT 3
DEFAULT CHARACTER SET WIN1251

PAGE_SIZE 16384
or
PAGE_SIZE 8192
------------------------------
Test table:
---------------
CREATE TABLE TST (
ID INTEGER NOT NULL,
DKEY VARCHAR(20) COLLATE PXW_CYRL
);
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
---------------------------------------------
Test data:
---------------
76842599 records in the table TST.
---------------------------------------------
Test SQL statements:
---------------
select *
from TST
where dkey='12213696'

Plan used: PLAN (TST INDEX (TST_DKEY))
Returns: 4 rows
---------------
select *
from TST
where dkey||''='12213696'

Plan used: PLAN (TST NATURAL)
Returns: 39 rows

--------------------------------------------------------------------------
Possible workarounds to solve the issue:
--------------------------------------------
1.Don't use COLLATE PXW_CYRL:
DROP INDEX TST_DKEY;
ALTER TABLE TST ADD DKEY_TMP VARCHAR(20);
UPDATE TST SET DKEY_TMP=DKEY;
ALTER TABLE TST DROP DKEY;
ALTER TABLE TST ALTER COLUMN DKEY_TMP TO DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
Enjoy.
2.Don't use 2-column index:
keep the field as it is (VARCHAR(20) COLLATE PXW_CYRL);
DROP INDEX TST_DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY);
That's it.
3.Delete some unnecessary records:
keep table's metadata as is;
DROP INDEX TST_DKEY;
DELETE FROM TST WHERE ID<17000000;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
So less records - no issue.
-------------------------------------------------------------------------------
Test database (fbk in 7z archive, 126 MB) will be sent on request.

=>

Test Environment:
---------------
Windows7 x64
8 GB RAM
Firebird 2.1.5 x86 (Firebird-2.1.5.18496_0_Win32)
---------------------------------------------
Issue Description:
---------------
Database is just restored from fbk (gbak - r).
SQL statement
select
<SomeFields>
from
<TableWith_76842599_RecordsInIt>
where
<IndexedFieldWithCollatePXW_CYRL>=<WellKnownValue>
returns a wrong result set (less rows than expected).
Drop/create index doesn't help.
---------------------------------------------
Validation results:
---------------
gfix -z -validate -no_update -full

reports:
gfix version WI-V2.1.5.18496 Firebird 2.1
Summary of validation errors
Number of index page errors : 28

In firebird.log:
Index 1 is corrupt on page 299894 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
...
<8 such records here>
...
Index 1 is corrupt on page 321352 level 0. File: ..\..\..\src\jrd\validation.cpp, line: 1559
in table <TableName>
Index 1 is corrupt (missing entries) in table <TableName>

------------------------------------------------------------------------------------------
Builds tested and confirmed as being affected by this issue:
------------------------------------------------------------
2.1.5.18465
2.1.5.18471
2.1.5.18474
2.1.5.18480
2.1.5.18496
2.1.6.18505
2.1.6.18506
------------------------------------------------------------------------------------------
Builds tested and confirmed as NOT being affected by this issue:
------------------------------------------------------------
2.5.2.26538
2.1.4.18314
2.1.4.18420
2.1.4.18438
2.1.5.18464

So it seems that this issue got introduced as a side effect of Backported CORE2422 fix.

---------------------------------------------
Test database:
---------------
SQL DIALECT 3
DEFAULT CHARACTER SET WIN1251

PAGE_SIZE 16384
or
PAGE_SIZE 8192
------------------------------
Test table:
---------------
CREATE TABLE TST (
ID INTEGER NOT NULL,
DKEY VARCHAR(20) COLLATE PXW_CYRL
);
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
---------------------------------------------
Test data:
---------------
76842599 records in the table TST.
---------------------------------------------
Test SQL statements:
---------------
select *
from TST
where dkey='12213696'

Plan used: PLAN (TST INDEX (TST_DKEY))
Returns: 4 rows
---------------
select *
from TST
where dkey||''='12213696'

Plan used: PLAN (TST NATURAL)
Returns: 39 rows

--------------------------------------------------------------------------
Possible workarounds to solve the issue:
--------------------------------------------
1.Don't use COLLATE PXW_CYRL:
DROP INDEX TST_DKEY;
ALTER TABLE TST ADD DKEY_TMP VARCHAR(20);
UPDATE TST SET DKEY_TMP=DKEY;
ALTER TABLE TST DROP DKEY;
ALTER TABLE TST ALTER COLUMN DKEY_TMP TO DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
Enjoy.
2.Don't use 2-column index:
keep the field as it is (VARCHAR(20) COLLATE PXW_CYRL);
DROP INDEX TST_DKEY;
CREATE INDEX TST_DKEY ON TST (DKEY);
That's it.
3.Delete some unnecessary records:
keep table's metadata as is;
DROP INDEX TST_DKEY;
DELETE FROM TST WHERE ID<17000000;
CREATE INDEX TST_DKEY ON TST (DKEY, ID);
So less records - no issue.
-------------------------------------------------------------------------------
Test database (fbk in 7z archive, 126 MB) will be sent on request.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Vadim,

please, put archive somewhere for download and send url to me

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Vadim,
your assumption about CORE2422 is absolutely correct.
Working on fix now.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Actually, this is duplicate of CORE2966

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Duplicate [ 3 ]

Fix Version: 2.1.6 [ 10460 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue duplicates CORE2966 [ CORE2966 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 2.1.6 [ 10460 ] =>

@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 database.

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

@hvlad hvlad

Labels
affect-version: 2.1.5 component: engine priority: major qa: not enough information resolution: duplicate type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@hvlad @firebird-automations