mORMot Open Source

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

AV at BatchSend with Zeos/FB

I was not able to follow changes in mORMot for a while and now I'm getting Access Violation at sending the Batch. The code of my project wasn't changed in that part.
AV appears in Zeos unit ZDbcInterbase6Utils.pas at line 1969 in Put subroutine of GetExecuteBlockString function:

  procedure Put(const Args: array of RawByteString; var Dest: PAnsiChar);
  var I: Integer;
  begin
    for I := low(Args) to high(Args) do //Move data
    begin
      {$IFDEF FAST_MOVE}ZFastCode{$ELSE}System{$ENDIF}.Move(Pointer(Args[i])^, Dest^, {%H-}PLengthInt(NativeUInt(Args[i]) - StringLenOffSet)^);         <---- here
      Inc(Dest, {%H-}PLengthInt(NativeUInt(Args[i]) - StringLenOffSet)^);
    end;
  end;

I tried to understand what has been changed and began rolling back mORMot versions. After severel steps (or tens of steps) I began getting other errors... As the result the last working (for me) commit is:
(git, master, 2020-03-21) 7108ee23d38d9b12a9cfe01a5642d0e87429a2a3 introduce TAESCTR.ComposeIV() modular method instead of TAESCTRNIST ...
The strange part is that with this 'old' commit Zeos Put subroutine from the above is not used at all in my project.

There were so many changes lately, so I got a bit confused. Therefore I decided to report my troubles here. If somebody gets the same, at least he'll know the working commit.

Delphi 10.3.3, Win64 target platform
Zeos (git, latest master, 2020-02-23) dac64fafcd00576a2c4ebed9c7dafadd8566e6b3
Firebird 2.5.9

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

The strange part is that with this 'old' commit Zeos Put subroutine from the above is not used at all in my project.

All Zeos internal Batches have been broken for a long time, because the batch ababilites haven't been determined correctly. Now you'r back on fast zeos batch bindings without the strings mORMot did create for you. Those are slow for FireBird and since FB exists they always recomment using parameters and native bindings.

Could you please update Zeos from SVN and test. R6380 see https://sourceforge.net/p/zeoslib/code-0/6380/.

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

After downloading snapshot 6380 and updating mORMot to the latest commit, I can't compile SynDBZeos unit with the error:

[dcc64 Error] SynDBZeos.pas(1272): E2035 Not enough actual parameters

The error is pointing to WriteIZBlob subroutine of TSQLDBZEOSStatement.ColumnsToJSON:

procedure WriteIZBlob;
var blob: IZBlob;
begin
  blob := fResultSet.GetBlob(col+FirstDbcIndex);
  WR.WrBase64(blob.GetBuffer,blob.Length,true); // withMagic=true             <--- here
end;

It seems, that blob.GetBuffer expects parameters.

Hope, I haven't mixed anything while getting the SVN snapshot.

danielkuettner

Member

From: Germany

Registered: 2014-08-06

Posts: 357

Re: AV at BatchSend with Zeos/FB

The code where the error comes from has a define {$ifdef ZEOS72UP}.

But you has updated to ZEOS 7.3.

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

That's true if you did not add the $USE_SYNCOMMONS define to your project base.
I've added full data streaming to zeos lately. Means with zeos you can write/read lobs from the database directly (if the driver supports an api for that: FB/IB, Oracle, Postgres OID lobs, MySQL, ASA, OleDB). So we need a temporary buffer for the streams.
@ab could you apply the following patch to SynDBZeos(compatible with all zeos versions):

Index: SynDBZeos.pas
===================================================================
--- SynDBZeos.pas	(revision 8968)
+++ SynDBZeos.pas	(working copy)
@@ -1267,9 +1267,11 @@
     Len: NativeUInt; // required by Zeos for GetPAnsiChar out param (not PtrUInt)
 procedure WriteIZBlob;
 var blob: IZBlob;
+  Raw: RawByteString;
 begin
   blob := fResultSet.GetBlob(col+FirstDbcIndex);
-  WR.WrBase64(blob.GetBuffer,blob.Length,true); // withMagic=true
+  raw := blob.GetString;
+  WR.WrBase64(Pointer(raw),Length(Raw),true); // withMagic=true
 end;
 {$ifend}
 begin // take care of the layout of internal ZDBC buffers for each provider

As a workaround just add the USE_SYNCOMMONS define to your project base conditions.

Last edited by EgonHugeist (2020-03-29 09:27:19)

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

As a workaround just add the USE_SYNCOMMONS define to your project base conditions.

Ok, I've done that - compilation goes fine now, thanks but select from the table with text blob doesn't want to work, now:

20200329 09424359 DB    SynDBZeos.TSQLDBZEOSStatement(040df640) Prepare 782us select ID,Name,PeriodType,PeriodSettings,OnOff,ActiveStart,ActiveEnd,Status,LastRun,Technical from UPKEEP_SCHEDULE where Technical=? and OnOff=? and Status=?
20200329 09424359 SQL   SynDBZeos.TSQLDBZEOSStatement(040df640) ExecutePrepared 1.26ms select ID,Name,PeriodType,PeriodSettings,OnOff,ActiveStart,ActiveEnd,Status,LastRun,Technical from UPKEEP_SCHEDULE where Technical=0 and OnOff=1 and Status=0
20200329 09424815 EXC   EZIBSQLException ("SQL Error: invalid BLOB ID") [] at b49f5c

PeriodSettings is a text blob field. Please, tell me if I'm losing smth obvious.

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

Then you propably have a commit or rollback in your code while reading from the resultset. Is it possible? FB thows all blob id's away if you do that...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: AV at BatchSend with Zeos/FB

Applied Michael patch as https://synopse.info/fossil/info/f008a2f59b

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

@Vitaly
added junked reads for utf8 clobs + firebird. But i think that would not help for the "invalid BLOB ID". Plz update.
Again: check your code for commit/rollback. Do you have such while reading the results?

@ab thank's for the quick commit

Last edited by EgonHugeist (2020-03-29 12:20:21)

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Sorry for making you wait.
I've updated mORMot with the patch, downloaded 6382 Zeos revision, removed use_syncommons definition (suddenly I began getting other errors with it) and the "invalid BLOB ID" problem has gone!
Although it seems that I'm getting now the same first AV problem, with which I started this thread

20200329 13382356 trace uNextDB.TNextDB(0467c860) BatchSend {"TSQLRestBatch(0462a9b0)":{"Count":2,"SizeBytes":1074}}
20200329 13382356  +    SynDBZeos.TSQLDBZEOSConnection(048791a0).Connect
20200329 13382356 trace         Connect to firebird D:\Norjournal\AFK_DEMO.FDB for localhost at 3050:
20200329 13382356  +    uNextDB.TNextDB(0467c860).EngineBatchSend  inlen=1074
20200329 13382360 DB            Connected to localhost using D:\Norjournal\AFK_DEMO.FDB 2005009
20200329 13382360 DB            SynDBZeos.TSQLDBZEOSStatement(04554110) Prepare 816us select current_timestamp from rdb$database
20200329 13382360 SQL           SynDBZeos.TSQLDBZEOSStatement(04554110) ExecutePrepared 1.52ms select current_timestamp from rdb$database
20200329 13382360  -    00.081.573
20200329 13382360 DB    SynDBZeos.TSQLDBZEOSStatement(045542d0) Prepare 674us update UPKEEP_JOURNAL_TASK set Status=?,Started=?,TotalValue=?,CurrentValue=?,Completed=?,Finished=?,Other=?,Results=? where ID=?
20200329 13382361 SQL   SynDBZeos.TSQLDBZEOSStatement(045542d0) ExecutePrepared 4.80ms wr=1 update UPKEEP_JOURNAL_TASK set Status=1,Started='2020-03-29 16:38:19',TotalValue=20,CurrentValue=5,Completed='25%',Finished=null,Other='{   "BaseURL": "https://personregister-web.test.nhn.no",   "User": "#####",   "Password": "#####"  }',Results='{   "PersonsToLoad": 1039,   "PersonsLoaded": 355,   "PersonsProcessed": 3  }' where ID=64
20200329 13382361 DB            SynDBZeos.TSQLDBZEOSStatement(04554490) Prepare 683us update PREG_PERSON set SSN=?,GivenName=?,MiddleName=?,Surname=?,DateAdrFrom=?,DateOfBirth=?,DateStatus=?,RegStatus=?,MaritalStatus=?,MothersNIN=?,FathersNIN=?,OldNIN=?,SpouseNIN=?,PostalAddress=?,PostalCode=?,PostalPlace=?,St=?,Municipality=?,SchoolDistrict=?,Constituency=?,BasicStatisticalUnit=?,CurrentAddress=?,Gender=?,LastUpdate=? where ID=?
20200329 13385059 EXCOS         EAccessViolation (c0000005) [] at b4335a
20200329 13385100 EXC           EAccessViolation ("Access violation at address 0000000000B4335A in module 'Upkeep.exe'. Read of address FFFFFFFFFFFFFFFC") [] at b4335a

Of course I'll try to test it more (and doublecheck all versions and commits) - just informing now about current status.

Concerning transaction handling. Could 'hard_commit=true' influence somehow badly here?

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

Still not able to reproduce it. I did some commit which suppress the reported AV, but i have no clue where the regression should come from yet... the Args in the function should never be empty.
Any news on your side?

Last edited by EgonHugeist (2020-03-29 17:39:43)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: AV at BatchSend with Zeos/FB

Where is the exact location of the EAccessViolation?

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Ok, with Zeos 6386 the AV has gone, it seems that your change was good, thanks! But (sorry, another trouble...) I began getting the exception:

20200329 20540057 trace uNextDB.TNextDB(046fc860) BatchSend {"TSQLRestBatch(046aa430)":{"Count":2,"SizeBytes":1074}}
20200329 20540057  +    uNextDB.TNextDB(046fc860).EngineBatchSend  inlen=1074
20200329 20540057 DB            SynDBZeos.TSQLDBZEOSStatement(028b3000) Prepare 663us update PREG_PERSON set SSN=?,GivenName=?,MiddleName=?,Surname=?,DateAdrFrom=?,DateOfBirth=?,DateStatus=?,RegStatus=?,MaritalStatus=?,MothersNIN=?,FathersNIN=?,OldNIN=?,SpouseNIN=?,PostalAddress=?,PostalCode=?,PostalPlace=?,St=?,Municipality=?,SchoolDistrict=?,Constituency=?,BasicStatisticalUnit=?,CurrentAddress=?,Gender=?,LastUpdate=? where ID=?
20200329 20540833 EXC           EZIBSQLException ("SQL Error: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 46; =") [] at b4129c
20200329 20540837 EXC           EZIBSQLException ("SQL Error: Dynamic SQL Error; SQL error code = -104; Token unknown - line 1, column 46; =") [] at b4129c

After some debugging I found that resulting Zeos sql statement looks like:

EXECUTE BLOCK(P0_0 VARCHAR(11) CHARACTER SET =?,P1_0 VARCHAR(50) CHARACTER SET =?,P2_0 VARCHAR(50) CHARACTER SET =?,P3_0 VARCHAR(50) CHARACTER SET =?,P4_0 DATE=?...

which, I assume, means that CHARACTER SET is lost.

We have to use ISO8859_1 in this legacy DB, earlier I solved it with the setting ZeosURL.Properties.Values['codepage'] := 'ISO8859_1 ' in TSQLDBZEOSConnectionProperties instance and it worked fine.

It seems, that the problem appears only if those fields in FB DB have ISO8859_1 character set. I made such a test: I picked up a new DB and allowed mORMot to create all metadata (it created all fields as UTF8). Then I looked at the result Zeos sql statement:

EXECUTE BLOCK(P0_0 VARCHAR(11) CHARACTER SET ISO8859_1=?,P1_0 VARCHAR(50) CHARACTER SET ISO8859_1=?,P2_0 VARCHAR(50) CHARACTER SET ISO8859_1=?,P3_0 VARCHAR(50) CHARACTER SET ISO8859_1=?,P4_0 TIMESTAMP=?...

and batch sending worked as intended.

Should I use some other approach to get it working with ISO8859_1 FB fields?

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

@Vitaly
before writing something about charactersets + mORMot let's see if the characterset can be found now: https://sourceforge.net/p/zeoslib/code-0/6387/
The regression happens only if there is a spezial collation for the field. Is there such one?
please update + test..

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Great! It all works again! Thank you very much!

Concerning collation: COLLATE NO_NO

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

Great. Now please test the USE_SYNCOMMONS define again. Does it work?

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Nope. I might be wrong (need to debug it more to understand the difference), but so far it seems that with USE_SYNCOMMONS in mORMot.pas

function TSQLTable.FieldIndex(FieldName: PUTF8Char): integer;
begin
  if (self<>nil) and (fResults<>nil) and (FieldName<>nil) and (FieldCount>0) then
  ...

FieldCount = 0 and the function returns -1.
Without this definition FieldCount = 14 and it returns the correct field index.

Hope this information will be anyhow useful

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

No idea yet. Which compiler are you using?
Note mORMot is optimized for UTF8 only. And expects utf8 in all areas. 
So please add

 ZeosURL.Properties.Values['codepage'] := 'UTF8'

Much better would be recreation your DB with default characterset UTF8.

It would be nice if you could pinpoint the problem...

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Delphi 10.3.3, Win64 target platform.
I'd love to switch to UTF8, but we have such a restriction currently because of the legacy software.

Sure, I'll try to debug it more. But a bit later, have to go now.
I will try with UTF8 codepage property also but, as far as I remember, it caused Firebird error in some particular cases. It was the reason for making my current codepage setting much time ago.
I'll report about results here.

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

Hope i got it. I found a missing ColumnCodePage determination for LOB SUBTYPE TEXT in the ResultSetMetainformation. I did a bit more also for the CharacterSet NONE.
Please update + test...

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Yes, all works fine (with and without USE_SYNCOMMONS)! Zeos 6392, and I've updated mORMot to the latest, but I don't think that it mattered here. Great!

Concerning UTF8 codepage setting in Zeos properties in my case (for ISO8859_1 FB DB):
All works, but until it tries to insert/update with the value, containing some non-ISO8859_1 character. Then I get such an exception:

Project Upkeep_Admin.exe raised exception class EZIBSQLException with message 'SQL Error: arithmetic exception, numeric overflow, or string truncation; Cannot transliterate character between character sets; At block line: 15, col: 1

With my current Zeos codepage setting transliteration goes fine, unicode symbols are transformed to some corresponding ISO8859_1 symbols smoothly. At least it seems so for me

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

Yes, all works fine (with and without USE_SYNCOMMONS)! Zeos 6392, and I've updated mORMot to the latest, but I don't think that it mattered here. Great!

Thanks for confirmation.

Concerning UTF8 codepage setting in Zeos properties in my case (for ISO8859_1 FB DB):
All works, but until it tries to insert/update with the value, containing some non-ISO8859_1 character.

Here we go:
That doesn't make sence 4me. Why is it possible that users inserting data which your database is NOT able to store. Avoiding this exception by using ISO8859_1 instead of UTF-8 means silent charcter loss. Means all unicode characters which can not be converted silently turn to '?'-questeion-marks. Is that what you want and the user who sends the data expects?

The more, zeos is doing duplicate A2W2A conversions for all character getters and setters. Ok the server usally is doing that (if not UTF8). So in your case the client needs the CPU ticks. That's neither fast nor a good design.

I'd love to switch to UTF8, but we have such a restriction currently because of the legacy software.

I don't know anything about the software. So i understand your POV. 
However i strongly recommend not using  ISO8859_1 for your if you can't guarantee all characters are cyrillic encoded. Instead of, create your DB in with UTF-8 default charcterset. Just use ISO8859_1 collations if you need them.

OR just give the user a note / how an failure to notify him such characters are not supported, as FB shows you...

Last edited by EgonHugeist (2020-03-31 06:36:47)

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Why is it possible that users inserting data which your database is NOT able to store.

Strictly saying, this particular service is not for users. It loads (and updates) a huge amount of data from different national services. Most of these services provide UTF8 data. And some of this data (names, mostly) sometimes contains non-ISO8859_1 characters. So, anyway, I have to make a transliteration before posting it to DB. The reason why I'm not doing that: I tested Zeos codepage setting and it worked as needed automatically.

Avoiding this exception by using ISO8859_1 instead of UTF-8 means silent charcter loss. Means all unicode characters which can not be converted silently turn to '?'-questeion-marks. Is that what you want and the user who sends the data expects?

I made a separate test to find how it works (as I did earlier, much time ago). Here is the result:
'ČOAVVÁ ÁDO' name received from the person registry as UTF8. It contains Č, which is not a part of ISO8859_1 encoding. Then mORMot->Zeos->FB and in DB (with ISO8859_1) we have 'COAVVÁ ÁDO', which seems rather good for use in another software, where users work. No question marks or another significant loss.
I do not consider myself as anyhow expert in encodings and stuff. So, any correction from your side is very welcome.

That's neither fast nor a good design.

I understand, that it is slower than with UTF8 only. Probably it is not the best design with such a restriction of using ISO8859_1 FB DB. I'd appreciate any hint for better design, but without converting DB to UTF8. We can't do that, so far at least.

However i strongly recommend not using  ISO8859_1 for your if you can't guarantee all characters are cyrillic encoded

Sorry, this part I couldn't understand even after reading it several times. Could you explain it to me a bit more, please? Why it should be cyrillic encoded? Maybe I'm wrong, but I think that ISO8859_1 (or Latin-1) doesn't support cyrillic characters, and it doesn't make sense in fact: NO_NO collation is for Norwegian language. I'm a bit confused here...

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: AV at BatchSend with Zeos/FB

Sorry, this part I couldn't understand even after reading it several times. Could you explain it to me a bit more, please? Why it should be cyrillic encoded? Maybe I'm wrong, but I think that ISO8859_1 (or Latin-1) doesn't support cyrillic characters, and it doesn't make sense in fact: NO_NO collation is for Norwegian language. I'm a bit confused here...

My bad, sorry, i just read you're from Russia ):

According the conversions: Seems you're a lucky man in your case. Others may see questionmarks for some chars only.

Btw. keep the USE_SYNCOMMONS define. That make the ColumnsToJSON process faster.

I've no more advices by now... Happy coding

ps.
Just commited a small performance improvement for your situation: https://sourceforge.net/p/zeoslib/code-0/6393/ Hope it helps a bit

Vitaly

Member

From: UAE

Registered: 2017-01-31

Posts: 168

Website

Re: AV at BatchSend with Zeos/FB

Btw. keep the USE_SYNCOMMONS define. That make the ColumnsToJSON process faster.

Got it, will use it in all projects with mORMot and Zeos.

Just commited a small performance improvement for your situation: https://sourceforge.net/p/zeoslib/code-0/6393/ Hope it helps a bit

Already downloaded and tested 6398, all seems fine

Many thanks for advising and everything you've done!! I think I already can't imagine any of my Delphi/FPC project without mORMot and Zeos

And sorry for my late messages sometimes in this discussion, all that pre-quarantine troubles...