Strange get next-operation in Pervasive 8.6?

[Database Administrator]

Hi, i have a - may be deep - btrieve-question:

The situation is as follows: (Pervasive 8.6):

we have a plain btrieve-file (btrieve-version 7.x), say product.dat,
with internal indices
AUTOINC
and a second index(let me call'em KARTON) with two segments:
1. a fiels from type LONGINT (the field KARTON)
2. AUTOINC

Pervasive itself does only know the position of the keysegments - real
field-information are due to the application: there are no DDF-files!

The contents of the field KARTON is not unique, i.e. there are several
records containing the same number in this fields (the KARTON-Nr!).

Now I want to read all records with the same KARTON-Nr. K

so I use a while-loop:

start with

Get Greater Than or Equal operation (9= B_GET_GE)

and continue with

Get Next operation (6=B_GET_NEXT)

while the contents of the loaded record is identical with the
the given KOARTON-Nr (or B_GET_NEXT returns Status <>0).


In most cases this will lead to the desired result.

But now we have observed in a productive envrionment, that for unknown
reasons this while-loop reports more(!) records with the same
KARTON-NR then existent in the datafile, i.e. some of the records
appear twice.
It semms, that the B_GET_NEXT operation sometimes returns not the next
but the current record again.

Up to now we observe only one incident of this type.

Fact is, that during the while-loop runs, there where several update
(3) - and insert (2) operations on that file (from other sessions).
The updates definitely does not change the above index much less the
key involved (i.e. key-vales are not changed! this we where able to
verify by analyse a special log-file). The insert-operations of course
affect the index, but the really added records all come by KARTON-Nr=0
(the while - loop runs with KARTON-NR<>0).

We guess, that while insert new key to the index (insert-operation)
the key-pages (sometimes) are reorganised in such a way, that a
database-cursor (from an different session) points now in such a way,
that the following next (6)-operation will report the same record again?

The question is:
-is this possible at all? does the engine do such "reorganisation"?
-is this a known issus in PSQL 8.6 (and is it fixed in 9.x/10.x?)
-if the described (presumed!) reorganisation of key-pages will not
occur, what other reason may be respnsible for that? (all Get Next
operations of the while-loop returned Status 0!).

Any hints, comments, remarks etc. are very wellcome!

Regards
Mircea

[Database Administrator]

Does the second key actually have an AutoInc data type at the Btrieve
level, or an INTEGER type? (Check this with BUTIL -STAT and post the
report.)

I've not heard of this specific issue, but you should only have ONE
AUTOINC field per table, so this could be part of the problem. I would
first recommend patching to Service Pack 3 (PSQLV8.7) as the best first
step. It may provide some improvement. If not, you may have to test
this with PSQLv9 or PSQLv10 to see if the problem exists there, too.

It would be ideal if you can create a test application (or two or three)
that readily duplicates the problem, as then we can test each engine
combination rather easily, and we can also report the issue to Pervasive.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach@goldstarsoftware.com
http://www.goldstarsoftware.com
*** Next Pervasive Service & Support Class - Nov 2008 ***


nmm wrote:
> Hi, i have a - may be deep - btrieve-question:
>
> The situation is as follows: (Pervasive 8.6):
>
> we have a plain btrieve-file (btrieve-version 7.x), say product.dat,
> with internal indices
> AUTOINC
> and a second index(let me call'em KARTON) with two segments:
> 1. a fiels from type LONGINT (the field KARTON)
> 2. AUTOINC
>
> Pervasive itself does only know the position of the keysegments - real
> field-information are due to the application: there are no DDF-files!
>
> The contents of the field KARTON is not unique, i.e. there are several
> records containing the same number in this fields (the KARTON-Nr!).
>
> Now I want to read all records with the same KARTON-Nr. K
>
> so I use a while-loop:
>
> start with
>
> Get Greater Than or Equal operation (9= B_GET_GE)
>
> and continue with
>
> Get Next operation (6=B_GET_NEXT)
>
> while the contents of the loaded record is identical with the
> the given KOARTON-Nr (or B_GET_NEXT returns Status <>0).
>
>
> In most cases this will lead to the desired result.
>
> But now we have observed in a productive envrionment, that for unknown
> reasons this while-loop reports more(!) records with the same
> KARTON-NR then existent in the datafile, i.e. some of the records
> appear twice.
> It semms, that the B_GET_NEXT operation sometimes returns not the next
> but the current record again.
>
> Up to now we observe only one incident of this type.
>
> Fact is, that during the while-loop runs, there where several update
> (3) - and insert (2) operations on that file (from other sessions).
> The updates definitely does not change the above index much less the
> key involved (i.e. key-vales are not changed! this we where able to
> verify by analyse a special log-file). The insert-operations of course
> affect the index, but the really added records all come by KARTON-Nr=0
> (the while - loop runs with KARTON-NR<>0).
>
> We guess, that while insert new key to the index (insert-operation)
> the key-pages (sometimes) are reorganised in such a way, that a
> database-cursor (from an different session) points now in such a way,
> that the following next (6)-operation will report the same record again?
>
> The question is:
> -is this possible at all? does the engine do such "reorganisation"?
> -is this a known issus in PSQL 8.6 (and is it fixed in 9.x/10.x?)
> -if the described (presumed!) reorganisation of key-pages will not
> occur, what other reason may be respnsible for that? (all Get Next
> operations of the while-loop returned Status 0!).
>
> Any hints, comments, remarks etc. are very wellcome!
>
> Regards
> Mircea
>
>

[Database Administrator]

Hi Bill,

Thank you very much indeed!

I tried "butil -stat", and must see, that the fileversion is really 8.00!

The AutoInc-Key-Segment of the key involved is of type integer
pointing to the autoinc-data (so we have only one Index of type
Autoinc: I guess, btrieve will report an error, if one try to create a
second autoinc-key. I have not described it exact, sorry: what i meant
was, that the segment "point to" the autoinc-position!).

It is Index Nr. 5 in the butil-stat below.

I have described the situation a little bit simplified: really the
Index has 3 segments: the first of type string[1] (pos 1) is part of
any of our indices (expect for Index 0, which is AutoInc) due to
applicationmanaged record-deletion. It contains usually char(0), and
for "deletet" records the value char(64) (wo dont use btrieve record
deletion (Delete operation (4=B_DELETE))).

Segment 2 (pos 75) is the KARTON-NR and the last segemnt points to
position 126 - which is the autoinc.

It might be very sophisticated to reproduce the problm, so I think a
test application will potentially never produce the "issue".

The problm is, that it happens exactly once (up to now) and the
application runs already several years and the operation, in which the
issue occured, is performed very often. (There are also
several simillar while-loops (with other datafiles an other indices),
where the issue *never* has been observed.)

So I have no good Idea, how such a test-application should be designed.

I think, that the "incidence" is affected (if not caused) by the
operations from different sessions, so different test-executibles
running on different clients in a real network might be necessary for
reproducing.

I know: it might look like a bug in the application: we have checked
it several times, and couldn't find something wrong.
So we have built in some extra checks into the application, to dig
more information, in case it happens again.

I fear, all we can do, is updating to 8.7 or 9.x and waiting...

Reageds und thanks again!
Mircea

OT: the column "Unique Values" in the butil-stat is nebulous:
see for instance key 1 segement 1: a srting[1] can only hold 256
different values, so 1768 Unique Values are obscure.

Btrieve Maintenance Utility 8.60.192.030
Copyright (C) Pervasive Software Inc. 2004
All Rights Reserved.

File Statistics for produkt.dat

File Version = 8.00
Page Size = 4096
Page Preallocation = No
Key Only = No
Extended = No

Total Number of Records = 2504997
Record Length = 141
Data Compression = No
Variable Records = No

Available Linked Duplicate Keys = 0
Balanced Key = No
Log Key = 0
System Data = No
Total Number of Keys = 13
Total Number of Segments = 37

Key Position Type Null Values* ACS
Segment Length Flags Unique Values
0 1 126 4 AutoInc M -- 2504997 --
1 1 1 1 String RMD -- 1768 --
1 2 51 4 Date RMD -- 1768 --
2 1 1 1 String RMD -- 79865 --
2 2 33 1 String RMD -- 79865 --
2 3 10 1 String RMD -- 79865 --
2 4 92 4 Integer RMD -- 79865 --
2 5 55 4 Date RMD -- 79865 --
3 1 1 1 String RMD -- 2504997 --
3 2 11 8 Integer RMD -- 2504997 --
3 3 126 4 Integer RMD -- 2504997 --
4 1 1 1 String RMD -- 37212 --
4 2 92 4 Integer RMD -- 37212 --
4 3 55 4 Date RMD -- 37212 --
5 1 1 1 String RMD -- 2504997 --
5 2 75 4 Integer RMD -- 2504997 --
5 3 126 4 Integer RMD -- 2504997 --
6 1 1 1 String RMD -- 2504997 --
6 2 63 4 Integer RMD -- 2504997 --
6 3 126 4 Integer RMD -- 2504997 --
7 1 1 1 String RMD -- 2504740 --
7 2 10 1 String RMD -- 2504740 --
7 3 11 8 Integer RMD -- 2504740 --
8 1 1 1 String RMD -- 2 --
8 2 47 4 Date RMD -- 2 --
9 1 1 1 String RMD -- 2504087 --
9 2 19 8 Integer RMD -- 2504087 --
9 3 10 1 String RMD -- 2504087 --
10 1 1 1 String RMD -- 2504997 --
10 2 6 4 Integer RMD -- 2504997 --
10 3 126 4 Integer RMD -- 2504997 --
11 1 1 1 String RMD -- 2504997 --
11 2 2 4 Integer RMD -- 2504997 --
11 3 126 4 Integer RMD -- 2504997 --
12 1 1 1 String RMD -- 2504997 --
12 2 39 4 Date RMD -- 2504997 --
12 3 126 4 Integer RMD -- 2504997 --

Legend:
< = Descending Order
D = Duplicates Allowed
I = Case Insensitive
M = Modifiable
R = Repeat Duplicate
A = Any Segment (Manual)
L = All Segments (Null)
* = The values in this column are hexadecimal.
?? = Unknown
-- = Not Specified

The command completed successfully.





BtrieveBill schrieb:
> Does the second key actually have an AutoInc data type at the Btrieve
> level, or an INTEGER type? (Check this with BUTIL -STAT and post the
> report.)
>
> I've not heard of this specific issue, but you should only have ONE
> AUTOINC field per table, so this could be part of the problem. I would
> first recommend patching to Service Pack 3 (PSQLV8.7) as the best first
> step. It may provide some improvement. If not, you may have to test
> this with PSQLv9 or PSQLv10 to see if the problem exists there, too.
>
> It would be ideal if you can create a test application (or two or three)
> that readily duplicates the problem, as then we can test each engine
> combination rather easily, and we can also report the issue to Pervasive.
> Goldstar Software Inc.
> Pervasive-based Products, Training & Services
> Bill Bach
> BillBach@goldstarsoftware.com
> http://www.goldstarsoftware.com
> *** Next Pervasive Service & Support Class - Nov 2008 ***
>
>
> nmm wrote:
>> Hi, i have a - may be deep - btrieve-question:
>>
>> The situation is as follows: (Pervasive 8.6):
>>
>> we have a plain btrieve-file (btrieve-version 7.x), say product.dat,
>> with internal indices
>> AUTOINC
>> and a second index(let me call'em KARTON) with two segments:
>> 1. a fiels from type LONGINT (the field KARTON)
>> 2. AUTOINC
>>
>> Pervasive itself does only know the position of the keysegments - real
>> field-information are due to the application: there are no DDF-files!
>>
>> The contents of the field KARTON is not unique, i.e. there are several
>> records containing the same number in this fields (the KARTON-Nr!).
>>
>> Now I want to read all records with the same KARTON-Nr. K
>>
>> so I use a while-loop:
>>
>> start with
>>
>> Get Greater Than or Equal operation (9= B_GET_GE)
>>
>> and continue with
>>
>> Get Next operation (6=B_GET_NEXT)
>>
>> while the contents of the loaded record is identical with the
>> the given KOARTON-Nr (or B_GET_NEXT returns Status <>0).
>>
>>
>> In most cases this will lead to the desired result.
>>
>> But now we have observed in a productive envrionment, that for unknown
>> reasons this while-loop reports more(!) records with the same
>> KARTON-NR then existent in the datafile, i.e. some of the records
>> appear twice.
>> It semms, that the B_GET_NEXT operation sometimes returns not the next
>> but the current record again.
>>
>> Up to now we observe only one incident of this type.
>>
>> Fact is, that during the while-loop runs, there where several update
>> (3) - and insert (2) operations on that file (from other sessions).
>> The updates definitely does not change the above index much less the
>> key involved (i.e. key-vales are not changed! this we where able to
>> verify by analyse a special log-file). The insert-operations of course
>> affect the index, but the really added records all come by KARTON-Nr=0
>> (the while - loop runs with KARTON-NR<>0).
>>
>> We guess, that while insert new key to the index (insert-operation)
>> the key-pages (sometimes) are reorganised in such a way, that a
>> database-cursor (from an different session) points now in such a way,
>> that the following next (6)-operation will report the same record again?
>>
>> The question is:
>> -is this possible at all? does the engine do such "reorganisation"?
>> -is this a known issus in PSQL 8.6 (and is it fixed in 9.x/10.x?)
>> -if the described (presumed!) reorganisation of key-pages will not
>> occur, what other reason may be respnsible for that? (all Get Next
>> operations of the while-loop returned Status 0!).
>>
>> Any hints, comments, remarks etc. are very wellcome!
>>
>> Regards
>> Mircea
>>
>>

[Database Administrator]

See inline notes.

nmm wrote:
> Hi Bill,
>
> Thank you very much indeed!
>
> I tried "butil -stat", and must see, that the fileversion is really 8.00!

With any PSQLV8 release, the 8.x file format is the default.

> The AutoInc-Key-Segment of the key involved is of type integer
> pointing to the autoinc-data (so we have only one Index of type
> Autoinc: I guess, btrieve will report an error, if one try to create a
> second autoinc-key. I have not described it exact, sorry: what i meant
> was, that the segment "point to" the autoinc-position!).
>
> It is Index Nr. 5 in the butil-stat below.
>
> I have described the situation a little bit simplified: really the
> Index has 3 segments: the first of type string[1] (pos 1) is part of
> any of our indices (expect for Index 0, which is AutoInc) due to
> applicationmanaged record-deletion. It contains usually char(0), and
> for "deletet" records the value char(64) (wo dont use btrieve record
> deletion (Delete operation (4=B_DELETE))).
>
> Segment 2 (pos 75) is the KARTON-NR and the last segemnt points to
> position 126 - which is the autoinc.
>
> It might be very sophisticated to reproduce the problm, so I think a
> test application will potentially never produce the "issue".
>
> The problm is, that it happens exactly once (up to now) and the
> application runs already several years and the operation, in which the
> issue occured, is performed very often. (There are also
> several simillar while-loops (with other datafiles an other indices),
> where the issue *never* has been observed.)
>
> So I have no good Idea, how such a test-application should be designed.
>
> I think, that the "incidence" is affected (if not caused) by the
> operations from different sessions, so different test-executibles
> running on different clients in a real network might be necessary for
> reproducing.
>
> I know: it might look like a bug in the application: we have checked
> it several times, and couldn't find something wrong.
> So we have built in some extra checks into the application, to dig
> more information, in case it happens again.

May be all that you CAN do. Sorry I couldn't help more.

>
> I fear, all we can do, is updating to 8.7 or 9.x and waiting...
>
> Reageds und thanks again!
> Mircea
>
> OT: the column "Unique Values" in the butil-stat is nebulous:
> see for instance key 1 segement 1: a srting[1] can only hold 256
> different values, so 1768 Unique Values are obscure.

This is the number of unique key combinations. So, for Key1, you have
1768 unique combinations of the string/date field combination. The
database does not keep sub-key statistics at this time.

Another thing -- you might get a SMALLER data file if you flagged the
indices containing your AUTOINC field (which must be unique anyway) as
UNIQUE. This will use a more compact index structure, which should save
disk space (and thus memory) in the file.

>
> Btrieve Maintenance Utility 8.60.192.030
> Copyright (C) Pervasive Software Inc. 2004
> All Rights Reserved.
>
> File Statistics for produkt.dat
>
> File Version = 8.00
> Page Size = 4096
> Page Preallocation = No
> Key Only = No
> Extended = No
>
> Total Number of Records = 2504997
> Record Length = 141
> Data Compression = No
> Variable Records = No
>
> Available Linked Duplicate Keys = 0
> Balanced Key = No
> Log Key = 0
> System Data = No
> Total Number of Keys = 13
> Total Number of Segments = 37
>
> Key Position Type Null Values* ACS
> Segment Length Flags Unique Values
> 0 1 126 4 AutoInc M -- 2504997 --
> 1 1 1 1 String RMD -- 1768 --
> 1 2 51 4 Date RMD -- 1768 --
> 2 1 1 1 String RMD -- 79865 --
> 2 2 33 1 String RMD -- 79865 --
> 2 3 10 1 String RMD -- 79865 --
> 2 4 92 4 Integer RMD -- 79865 --
> 2 5 55 4 Date RMD -- 79865 --
> 3 1 1 1 String RMD -- 2504997 --
> 3 2 11 8 Integer RMD -- 2504997 --
> 3 3 126 4 Integer RMD -- 2504997 --
> 4 1 1 1 String RMD -- 37212 --
> 4 2 92 4 Integer RMD -- 37212 --
> 4 3 55 4 Date RMD -- 37212 --
> 5 1 1 1 String RMD -- 2504997 --
> 5 2 75 4 Integer RMD -- 2504997 --
> 5 3 126 4 Integer RMD -- 2504997 --
> 6 1 1 1 String RMD -- 2504997 --
> 6 2 63 4 Integer RMD -- 2504997 --
> 6 3 126 4 Integer RMD -- 2504997 --
> 7 1 1 1 String RMD -- 2504740 --
> 7 2 10 1 String RMD -- 2504740 --
> 7 3 11 8 Integer RMD -- 2504740 --
> 8 1 1 1 String RMD -- 2 --
> 8 2 47 4 Date RMD -- 2 --
> 9 1 1 1 String RMD -- 2504087 --
> 9 2 19 8 Integer RMD -- 2504087 --
> 9 3 10 1 String RMD -- 2504087 --
> 10 1 1 1 String RMD -- 2504997 --
> 10 2 6 4 Integer RMD -- 2504997 --
> 10 3 126 4 Integer RMD -- 2504997 --
> 11 1 1 1 String RMD -- 2504997 --
> 11 2 2 4 Integer RMD -- 2504997 --
> 11 3 126 4 Integer RMD -- 2504997 --
> 12 1 1 1 String RMD -- 2504997 --
> 12 2 39 4 Date RMD -- 2504997 --
> 12 3 126 4 Integer RMD -- 2504997 --
>
> Legend:
> < = Descending Order
> D = Duplicates Allowed
> I = Case Insensitive
> M = Modifiable
> R = Repeat Duplicate
> A = Any Segment (Manual)
> L = All Segments (Null)
> * = The values in this column are hexadecimal.
> ?? = Unknown
> -- = Not Specified
>
> The command completed successfully.
>
>
>
>
>
> BtrieveBill schrieb:
>> Does the second key actually have an AutoInc data type at the Btrieve
>> level, or an INTEGER type? (Check this with BUTIL -STAT and post the
>> report.)
>>
>> I've not heard of this specific issue, but you should only have ONE
>> AUTOINC field per table, so this could be part of the problem. I would
>> first recommend patching to Service Pack 3 (PSQLV8.7) as the best first
>> step. It may provide some improvement. If not, you may have to test
>> this with PSQLv9 or PSQLv10 to see if the problem exists there, too.
>>
>> It would be ideal if you can create a test application (or two or three)
>> that readily duplicates the problem, as then we can test each engine
>> combination rather easily, and we can also report the issue to Pervasive.
>> Goldstar Software Inc.
>> Pervasive-based Products, Training & Services
>> Bill Bach
>> BillBach@goldstarsoftware.com
>> http://www.goldstarsoftware.com
>> *** Next Pervasive Service & Support Class - Nov 2008 ***
>>
>>
>> nmm wrote:
>>> Hi, i have a - may be deep - btrieve-question:
>>>
>>> The situation is as follows: (Pervasive 8.6):
>>>
>>> we have a plain btrieve-file (btrieve-version 7.x), say product.dat,
>>> with internal indices
>>> AUTOINC
>>> and a second index(let me call'em KARTON) with two segments:
>>> 1. a fiels from type LONGINT (the field KARTON)
>>> 2. AUTOINC
>>>
>>> Pervasive itself does only know the position of the keysegments - real
>>> field-information are due to the application: there are no DDF-files!
>>>
>>> The contents of the field KARTON is not unique, i.e. there are several
>>> records containing the same number in this fields (the KARTON-Nr!).
>>>
>>> Now I want to read all records with the same KARTON-Nr. K
>>>
>>> so I use a while-loop:
>>>
>>> start with
>>>
>>> Get Greater Than or Equal operation (9= B_GET_GE)
>>>
>>> and continue with
>>>
>>> Get Next operation (6=B_GET_NEXT)
>>>
>>> while the contents of the loaded record is identical with the
>>> the given KOARTON-Nr (or B_GET_NEXT returns Status <>0).
>>>
>>>
>>> In most cases this will lead to the desired result.
>>>
>>> But now we have observed in a productive envrionment, that for unknown
>>> reasons this while-loop reports more(!) records with the same
>>> KARTON-NR then existent in the datafile, i.e. some of the records
>>> appear twice.
>>> It semms, that the B_GET_NEXT operation sometimes returns not the next
>>> but the current record again.
>>>
>>> Up to now we observe only one incident of this type.
>>>
>>> Fact is, that during the while-loop runs, there where several update
>>> (3) - and insert (2) operations on that file (from other sessions).
>>> The updates definitely does not change the above index much less the
>>> key involved (i.e. key-vales are not changed! this we where able to
>>> verify by analyse a special log-file). The insert-operations of course
>>> affect the index, but the really added records all come by KARTON-Nr=0
>>> (the while - loop runs with KARTON-NR<>0).
>>>
>>> We guess, that while insert new key to the index (insert-operation)
>>> the key-pages (sometimes) are reorganised in such a way, that a
>>> database-cursor (from an different session) points now in such a way,
>>> that the following next (6)-operation will report the same record again?
>>>
>>> The question is:
>>> -is this possible at all? does the engine do such "reorganisation"?
>>> -is this a known issus in PSQL 8.6 (and is it fixed in 9.x/10.x?)
>>> -if the described (presumed!) reorganisation of key-pages will not
>>> occur, what other reason may be respnsible for that? (all Get Next
>>> operations of the while-loop returned Status 0!).
>>>
>>> Any hints, comments, remarks etc. are very wellcome!
>>>
>>> Regards
>>> Mircea
>>>
>>>

[Database Administrator]

Thanks a lot again!

BtrieveBill schrieb:
> See inline notes.
>
> Another thing -- you might get a SMALLER data file if you flagged the
> indices containing your AUTOINC field (which must be unique anyway) as
> UNIQUE. This will use a more compact index structure, which should save
> disk space (and thus memory) in the file.
>

We will try!

Regards
Mircea