TSynBigTable Flush/Pack problem

cocce · 2012-03-01 15:41:35

Hi, I use TSynBigTable class to store a table which has a unique fileld used as primary key.
I want to flush to disk every changes of records.
I use the UpdateToFile(True, false) function, which works fine after inserting new records (with new key field value). Instead, something goes wrong after updating some records (using an already used primary key value).

The main symptom that something has gone wrong is that when I try to reload the saved file I get an error stating 'TFileBufferReader: invalid content'.
But even before reloading the file, if I try multiple update operations at a certain point I find that the number of records in the dataset is wrong: for instance, if I try to continually resave records with keys ranging from 1 to 1000, at a certain point I find that the number of records is no more 1000, but it starts growing (1001, 1002, 1005, 1007 or something like that, and so on).

If instead of UpdateToFile, I use Pack() function after every block of inserts/updates, it works like a charm.

In attach a piece of code:

recordId := GetRecordId(AKeyFieldName, keyFieldValue);// get record Id searching primary key in table
rec.Init(FSynTable.Table, recordId);
PopulateFieldsValues();
if recordId > 0 then
begin //found KEY
FSynTable.RecordUpdate(rec);
end else // not found..add a new rec
FSynTable.RecordAdd(rec);

Is it correct code? Am I do anything wrong?
Thanks in advace.
Cocce

ab · 2012-03-01 16:16:52

Do you have any code to reproduce the issue?

Did you retrieve the latest version from http://synopse.info/fossil?

cocce · 2012-03-01 16:25:34

ab wrote:

Do you have any code to reproduce the issue?

I must extract the piece of code... i'll submit soon

ab wrote:

Did you retrieve the latest version from http://synopse.info/fossil?

Yes i tried it, but using internal "TestBigTable" function crash... and so i rolled back to last stable.

cocce · 2012-03-02 08:18:21

cocce wrote:

ab wrote:
Do you have any code to reproduce the issue?

You can find the example at this link: http://dl.dropbox.com/u/10759134/BigTableTest.7z
If you press more times the add button, you'll see the wrong record number.

cocce wrote:

ab wrote:
Did you retrieve the latest version from http://synopse.info/fossil?
Yes i tried it, but using internal "TestBigTable" function crashes... and so i rolled back to last stable.

Did you try the TestBigTable function inside SynBigTable.pas , I use D2007 and it crashes.

Thanks in advance
Cocce

ab · 2012-03-02 08:19:16

I think there was an issue introduced by
http://synopse.info/fossil/fdiff?v1=cd4 … 9767cfae9e

I'll take a look a this!

Thanks for the report.

ab · 2012-03-02 09:08:11

Here we are...

I've added a new sbtBeforeWrite step (e.g. to safely update indexes).
See http://synopse.info/fossil/info/cf26b333d0

This fixes the regression issue introduced by http://synopse.info/fossil/info/747cf5317c

Thanks for the report!

cocce · 2012-03-02 09:46:52

ab wrote:

Here we are...
I've added a new sbtBeforeWrite step (e.g. to safely update indexes).
See http://synopse.info/fossil/info/cf26b333d0
This fixes the regression issue introduced by http://synopse.info/fossil/info/747cf5317c
Thanks for the report!

Cool, and what about my piece of code?

Thanks

ab · 2012-03-02 10:17:28

I think your code sounds correct.

cocce · 2012-03-02 10:47:07

cocce wrote:

ab wrote:
Do you have any code to reproduce the issue?
You can find the example at this link: http://dl.dropbox.com/u/10759134/BigTableTest.7z
If you press more times the add button, you'll see the wrong record number.

ab wrote:

I think your code sounds correct.

Did you try the example in attach?

And so.. My code is all correct?
Thanks

Last edited by cocce (2012-03-02 11:37:07)

ab · 2012-03-02 15:15:49

I can't download it now (site blocked by proxy).

I'll check later.

cocce · 2012-03-11 21:21:12

ab wrote:

I can't download it now (site blocked by proxy).
I'll check later.

Hi, did you take a look?

Thanks

cocce · 2012-03-21 19:15:24

Hi ab, i m waiting for your answer...

I m in stuck at this problem, I apologize for my insistence

Thank you very much!

ab · 2012-03-21 20:48:48

Sorry but the link is broken.

The fix I commited (see above) would fix the Flush/Pack issue, in all cases.

cocce · 2012-03-22 08:08:58

I already try the new version but the problem is still present.
The link is active and correct, how can I send you the zip file?

Thanks

ab · 2012-03-22 09:56:08

Send it to
webcontact01
at
synopse
dot
info

ab · 2012-03-22 13:39:22

I got the files.
There is indeed some problems here.
I'll investigate ASAP - but perhaps not finished before end of next week.

Thanks for the report.

cocce · 2012-04-04 09:26:28

ab wrote:

I got the files.
There is indeed some problems here.
I'll investigate ASAP - but perhaps not finished before end of next week.
Thanks for the report.

Hi Ab, have you got some news?

Thanks

cocce · 2012-04-09 19:08:42

Hi, i m waiting for your answer...
Thanks

ytoff57 · 2012-04-16 03:25:43

Hi ab I discovered your lib a while ago and started to use it for a project. I can confirm that there is indeed an issue with updating records. I use TSynBigTableRecord class and the method UpdateToFile() to write the database to disk. When I do a RecordAdd(..) everything's fine but if I use RecordUpdate(..) I'm no longer able to retrieve or update or even delete that record. I still can list it with GetAllIDs but when I Search for it, it's like it's ID is no longer linked with the actual data... Do you have a fix for this bug yet because that kind of data corruption won't be good once the database will grow out of testing phase!

Thank you!

cocce · 2012-05-21 06:54:56

Hi, is there a news about this bug? I m blocked.... please give me an answer!

ab · 2012-05-21 07:54:09

I was able to reproduce the issue (thanks to your files), but did not succeed in finding out the cause.

I suspect it is a weird border-side effect of the current implementation.
But I was not able to find out where it comes from.

I'll retry today.

cocce · 2012-05-21 14:46:28

Thank you very much.

ab wrote:

I was able to reproduce the issue (thanks to your files), but did not succeed in finding out the cause.
I suspect it is a weird border-side effect of the current implementation.
But I was not able to find out where it comes from.
I'll retry today.

cocce · 2012-05-28 09:39:10

ab wrote:

I was able to reproduce the issue (thanks to your files), but did not succeed in finding out the cause.
I suspect it is a weird border-side effect of the current implementation.
But I was not able to find out where it comes from.
I'll retry today.

Did you discover the cause?

ab · 2012-05-28 12:37:13

No, sorry, I'm still stucked with this issue.

cocce · 2012-05-28 12:47:17

ab wrote:

No, sorry, I'm still stucked with this issue.

May I help you in some way?

mpv · 2012-05-28 13:35:46

2 cocce
Link with sources you provide is really broken
can you send example sources to me -
moops66 at
ukr dot net
Maybe I will help..

cocce · 2012-06-04 14:19:29

@mpv
I sent you a email!

mpv · 2012-06-19 12:26:57

@cocce
No e-mail from you

cocce · 2012-06-19 15:20:02

mpv wrote:

@cocce
No e-mail from you

I m not able to send you a mail...

Try to download this

https://dl.dropbox.com/u/10759134/BigTableTest.zip

Thanks in advance
Cocce

Gigo · 2014-09-26 14:41:04

Hello ab,

sorry to open this issue again, but TBigTableRecord has some issues with saving updated tables with indexed fields.

consider following :
1. create TBigTableRecord with at least 1 indexed field
2. add 10 records
3. update any record (first, for example)
4. destroy TBigTableRecord
5. try to load TBigTableRecord (will fail)

if I pack TBigTableRecord before destruction everything is OK

cause :
When we modify record, TBigTableRecord will add hidden record and update OrderedIndex array
When TBigTableRecord is saved to file, OrderedIndex still contains new PhysicalIndex {11,1,2,3,4,5,6,7,8,9,10}
On next load of TBigTableRecord from file, TSynTableFieldProperties.CreateFrom will load OrderedIndex array with new PhysicalIndex {11,1,2,3,4,5,6,7,8,9,10},
but will fail to create OrderedIndexReverse (actually on sanity check "if OrderedIndex[ i ]>=OrderedIndexCount then RD.ErrorInvalidContent").

fix:
Pack TBigTableRecord before destruction (would be nice to add this to TBigTableRecord.Destroy so we can forget about it,
if TBigTableRecord has indexed fields and is updated, table is unusable anyway)

or

Take some speed penalty and ditch OrderedIndexReverse array for reverse lookup.
Make more complicated reverse lookup procedure, so you can handle ie {11,1,2,3,4,5,6,7,8,9,10}
Current implementation cannot handle OrderedIndexReverse[ 11 ].
Maybe use TDynArray.IndexOf on OrderedIndex to find indexes.

ab · 2014-09-26 17:20:52

Could you please create a ticket, with a link to your post?

Gigo · 2014-09-29 11:36:51

In procedure TSynBigTable.Pack

  if (self=nil) or ((fDeletedCount=0) and (fAliasCount=0)) or (fCount=0) or
     (fFile=0) then

should be :

  if (self=nil) or ((fDeletedCount=0) and (fAliasCount=0) and (fInMemoryCount=0)) or (fCount=0) or
     (fFile=0) then

because if you try to pack table with no changed records TSynTable.GetData will generate exception at

if PByte(result)^<=$7f then

ab · 2014-09-29 14:37:31

Does http://synopse.info/fossil/info/f402c03383 works now?

I have added the corresponding regression tests, which seems to fix the issue.

Gigo · 2014-09-29 16:30:54

Thanks for quick update. First I thought issue is fixed, but then I've tried this :

1. Create TSynBigTableRecord
2. Add 65537+ records
3. Destroy TSynBigTableRecord
4. Open TSynBigTableRecord
5. Modify first and last record
6. Destroy TSynBigTableRecord without packing
7. Open TSynBigTableRecord

Same ErrorInvalidContent message.

With Pack() before Destroy everything is fine.

Here is the code I've tested

procedure TForm1.BtnABClick(Sender: TObject);
var
  Tbl : TSynBigTableRecord;
  rec: TSynTableData;
  Fld1,Fld2 : TSynTableFieldProperties;
  aId,i,l,j : integer;
begin
  l := 65537;
  if FileExists('Test.big') then
    if not DeleteFile('Test.big') then
      exit;

  // CREATE TABLE
  Tbl := TSynBigTableRecord.Create('Test.big','Test1');
  try
    if not Tbl.AddField('Int',tftInt32,[tfoIndex,tfoUnique]) then
      exit;
    if not Tbl.AddField('BigInt',tftInt64,[tfoIndex]) then
      exit;
    Tbl.AddFieldUpdate;

    // ADD RECORDS
    Fld1 := tbl.Table['Int'];
    Fld2 := tbl.Table['BigInt'];

    for i := 1 to l do
      begin
        rec.Init(Tbl.Table);
        rec.SetFieldValue(Fld1,i);
        rec.SetFieldValue(Fld2,i);

        aID := Tbl.RecordAdd(rec);
        if aID=0 then
          begin
            ShowMessage('Error adding record');
            exit;
          end;
      end;
  finally
    FreeAndNil(Tbl);
  end;
  // MODIFY DATA
  Tbl := TSynBigTableRecord.Create('Test.big','Test1');
  try
    Fld2 := tbl.Table['BigInt'];

    rec := Tbl.RecordGet(1);
    if rec.ID > 0 then
      begin
        j := Rec.GetFieldValue(Fld2);
        Rec.SetFieldValue(Fld2, j+1);
        Tbl.RecordUpdate(rec);
      end;

    rec := Tbl.RecordGet(Tbl.Count);
    if rec.ID > 0 then
      begin
        j := Rec.GetFieldValue(Fld2);
        Rec.SetFieldValue(Fld2, j+1);
        Tbl.RecordUpdate(rec);
      end;

    //Tbl.Pack();
  finally
    FreeAndNil(Tbl);
  end;

  Tbl := TSynBigTableRecord.Create('Test.big','Test1');
  try
    ShowMessage('Voila');   // never shown
  finally
    FreeAndNil(Tbl);
  end;
end;

mORMot Open Source

#1 2012-03-01 15:41:35

TSynBigTable Flush/Pack problem

#2 2012-03-01 16:16:52

Re: TSynBigTable Flush/Pack problem

#3 2012-03-01 16:25:34

Re: TSynBigTable Flush/Pack problem

#4 2012-03-02 08:18:21

Re: TSynBigTable Flush/Pack problem

#5 2012-03-02 08:19:16

Re: TSynBigTable Flush/Pack problem

#6 2012-03-02 09:08:11

Re: TSynBigTable Flush/Pack problem

#7 2012-03-02 09:46:52

Re: TSynBigTable Flush/Pack problem

#8 2012-03-02 10:17:28

Re: TSynBigTable Flush/Pack problem

#9 2012-03-02 10:47:07

Re: TSynBigTable Flush/Pack problem

#10 2012-03-02 15:15:49

Re: TSynBigTable Flush/Pack problem

#11 2012-03-11 21:21:12

Re: TSynBigTable Flush/Pack problem

#12 2012-03-21 19:15:24

Re: TSynBigTable Flush/Pack problem

#13 2012-03-21 20:48:48

Re: TSynBigTable Flush/Pack problem

#14 2012-03-22 08:08:58

Re: TSynBigTable Flush/Pack problem

#15 2012-03-22 09:56:08

Re: TSynBigTable Flush/Pack problem

#16 2012-03-22 13:39:22

Re: TSynBigTable Flush/Pack problem

#17 2012-04-04 09:26:28

Re: TSynBigTable Flush/Pack problem

#18 2012-04-09 19:08:42

Re: TSynBigTable Flush/Pack problem

#19 2012-04-16 03:25:43

Re: TSynBigTable Flush/Pack problem

#20 2012-05-21 06:54:56

Re: TSynBigTable Flush/Pack problem

#21 2012-05-21 07:54:09

Re: TSynBigTable Flush/Pack problem

#22 2012-05-21 14:46:28

Re: TSynBigTable Flush/Pack problem

#23 2012-05-28 09:39:10

Re: TSynBigTable Flush/Pack problem

#24 2012-05-28 12:37:13

Re: TSynBigTable Flush/Pack problem

#25 2012-05-28 12:47:17

Re: TSynBigTable Flush/Pack problem

#26 2012-05-28 13:35:46

Re: TSynBigTable Flush/Pack problem

#27 2012-06-04 14:19:29

Re: TSynBigTable Flush/Pack problem

#28 2012-06-19 12:26:57

Re: TSynBigTable Flush/Pack problem

#29 2012-06-19 15:20:02

Re: TSynBigTable Flush/Pack problem

#30 2014-09-26 14:41:04

Re: TSynBigTable Flush/Pack problem

#31 2014-09-26 17:20:52

Re: TSynBigTable Flush/Pack problem

#32 2014-09-29 11:36:51

Re: TSynBigTable Flush/Pack problem

#33 2014-09-29 14:37:31

Re: TSynBigTable Flush/Pack problem

#34 2014-09-29 16:30:54

Re: TSynBigTable Flush/Pack problem

Board footer