Writing Blobs from Streams Failing in SQLite... SOMETIMES...

[BytePlayer]

As a matter of paranoia, I have my code make sure all blobs are written correctly, and every now and again with V8 (alexandria), I get an assertion that it failed to write all the data to the blob. It's always just a few dozen or so bytes short, but I kind of think I need them, unless Zeos/SQLite is doing some kind of trimming of null characters at the end of the blob, or something of that ilk... and even if it is trying to be helpful I might still need them depending on how the reader would handle the situation.

Here's an example of the assertion and you can see it's just 22 bytes short

Untitled.png

Here's my code in case it offers any insight, which I kind of doubt

Code: Select all

 MS:=TMemoryStream.Create;

//Proprietary function to write data to a memoryStream
    StoreWorldStateToMemoryStream(inState,MS);
    var SanityCheck:Integer:=0;
    repeat
      if SanityCheck > 1 then
        Sleep(250);//to avoid a race condition??
      TBlobField(ToField).Clear; //shouldn't be needed but still...
      MS.Position:=0; //again, probably not needed but why not be safe.
      TBlobField(ToField).LoadFromStream(MS);  //Should be good, but ARE WE?
      Inc(SanityCheck);  //if it fails once, we'll try it four more times with small sleep in between then give it up...
    until (SanityCheck=5) or (TBlobField(ToField).BlobSize = MS.Size);

    Assert(TBlobField(ToField).BlobSize = MS.Size,'Mismatched Blob Data Size '+
       IntToStr(TBlobField(ToField).BlobSize)+' vs. '+IntToStr(MS.SIze)+' saving World State');
    Ms.Free;

This is using SQLite and I never had this problem with 7.x -- do I need to roll back or does anyone have any ideas why it would be doing this, and even better, what could be done about it!

[aehimself]

This by itself won't do much.

- Save the original data to the disk, save the contents of blob field to the disk. Compare the differences.
- After saving your blob, post the dataset and read it to a different stream. Save both streams to the disk and compare differences.

My guess is your data is safe and sound, only a size reporting issue is experienced (which still has to be looked at).

[BytePlayer]

Excellent suggestion -- I will do that and report back.

[Fr0sT]

OMG, this is the most paranoid code I've ever seen... Anyway it really doesn't check much. If you really are to check the loading, save blob to another memory stream and compare it with source one. Moreover, here you're checking only local assignment. Why not check if a blob was correctly saved to DB.
My guess is that blobfield reports the size with some internal payload.

[aehimself]

OMG, this is the most paranoid code I've ever seen...

"World state" suggests we are talking about a game engine. Imagine the lag 1,2 sec of saving world state will generate :)
Tbh I don't see the reason of this check either. I'd only do safeguards if actual corruption happened once but considering the to-be-saved and saved contents weren't compared until now this is clearly not the case.

Anyway, it is not up to us to decide :)

[Fr0sT]

Well, that's SQLite so I guess the DB is local - some actions could be sent to background threads.

P.S.

Code: Select all

foo := True;
if (foo <> True) then
  foo := True;

[BytePlayer]

OMG, this is the most paranoid code I've ever seen...

Thanks!

I'm not normally this paranoid, but I was having some problems so I started putting checks in. And I ended up doing the loop in case the issue was due to some sort of race condition...

Anyway it really doesn't check much. If you really are to check the loading, save blob to another memory stream and compare it with source one. Moreover, here you're checking only local assignment. Why not check if a blob was correctly saved to DB.

My guess is that blobfield reports the size with some internal payload.

Nope, no "internal payload" being reported by the blob. I know this as (a) 99.x% of the time, the BlobSize DOES report the same as the TMemoryStream.Size and I'd expect if there were a header payload it would always be reported incorrectly, not only once in a blue moon. And (B), in that rare case when the sizes don't match, if I save the blob to a file, the file exactly matches its reported size so it's not just erroneous size reporting.

I basically took your suggestion but rather than save the blob to another stream, I decided that when they next mismatched I would save both the original stream AND the blob to their own files so that I could compare them and see how they actually differed, or whether it was, in fact, merely, an erroneous reporting error.

Sadly it's a real error, and I've attached the two written files as a zip to keep the BBB happy. However, here is the binary comparison of the two files.

WRongBlobSize.jpg

As you can see, for some reason the blob (the one on the right) has doubled (aka duplicated) the last 47 bytes of my stream... and it does it so repeatedly (at least five times) despite being fully cleared out prior to each attempt to write to it.

doubled-bytes.jpg

I am hoping you can duplicate the issue and track it down by loading the original stream file.bin into a TMemoryStrean then trying to load it into a blob within an SQLite Database

Needless to say, this is kind of a show-stopper problem for us... so any help or insight you can provide will be gratefully accepted.

[marsupilami]

Hello BytePlayer,

I tried to reproduce your issue. But for me it seems to work as expected. I use the following code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  MS: TMemoryStream;
  x: Integer;
  DataField: TBlobField;
begin
  MS := TMemoryStream.Create;
  MS.LoadFromFile('D:\Projekte\Zeos\2022-09-27 SQLite + BLOB\Original Stream File.bin');
  ZConnection1.Connect;
  ZConnection1.ExecuteDirect('delete from testtable');
  ZQuery1.Open;
  for x := 1 to 1000 do begin
    Button1.Caption := IntToStr(X);
    ZQuery1.Append;
    DataField := ZQuery1.FieldByName('DATA') as TBlobField;
    ZQuery1.FieldByName('ID').AsInteger := X;
    DataField.Clear;
    MS.Position := 0;
    DataField.LoadFromStream(MS);
    if DataField.BlobSize <> MS.Size then
      raise Exception.Create('Mismatched Blob Data Size');
    ZQuery1.Post;
  end;
end;

Test enviromnemt: Delphi 11.1, SQLite 3390300, the program is built for X86 on Windows.

Could you please try to create a test case (Delphi source code, sqlite database) where the problem can be reproduced?

Best regards,

Jan

[BytePlayer]

Okay, I'll try but it's unfortunately erratic...

[BytePlayer]

No joy replicating it either. I've put in some more checks/tests so if/when it happens again, I'll hopefully be able to give you more data...

[marsupilami]

I can only assume that this is some kind ob memory corruption. Possibly Zeos fault. But without a test case it is very hard to debug.

[BytePlayer]

Yes, as a software developer I completely get that. We'll see if it happens again for if I've somehow accidentally fixed it...

[BytePlayer]

Well, sadly I did not accidentally fix it though I've gathered a bit more information and a pretty hackish work around.

It seems to be happening exclusively during a "timed backup" which is an idle event where the program takes all the data is has been working with, and updates the database with any changed data. It is not in a thread but it does start during an App Idle Event and is also triggered by an ApplicationDeactivate Event(i.e. the application losing focus) so I don't know if that gives anyone any clues.

My hacky work around is this: when I catch a stream to blob mismatch, I clear the blob (yet again!) post what I have and then close the table and reopen it, and the next time it tries to write to the blob it works. This, needless to say, is suboptimal, but at least it seems to always work in this case.

I will try it next to clear the blob, post it, then edit it and see if that works. Any insight or ideas on what I should looking for, or things I should pay closer attention to when it gets int this state are gratefully accepted.

[Fr0sT]

Heisenbug
Are you building with Delphi? Try to include FastMM4 and turn on all memory corruption detection options. Maybe this will give some hint

BTW. Your dumps seem to show that BLOBs contain all source contents plus a tail of (previous?) source contents while in the initial post you complained about truncating (though screenshot is showing the opposite - blob is larger than mem stream!). This really matters because truncating and wrong widening are usually caused by different conditions

[BytePlayer]

I think I misspoke (mistyped?) when I said that they were too short. In all cases since then they have been too long... and as far as I can tell, it's not previous text that's being left over, it's a duplication of the final text... though it may be that it's only happening after it's been written once and it's somehow being offset...? Or I could be just talking gibberish.

I have discovered a situation where it happens quite regularly but only in our full program which is roughly 1.5M lines of code, and which I can't exactly share. I shall do as you suggest, but if you have additional things you think I should check at least it seems repeatable (yay me) now.