blob cache

alexdmatveev · 2015-12-09 12:40:19

Hello gurus,

I need your help to deal with blob fields posting.

Till today I had a server on mongoDB and my statements like:

client.UpdateBlobFields(rec);

worked fine.

Today I exchanged the server part with MySQL connection.
Now all my statements like

client.UpdateBlobFields(rec);

write always the same value in blob field.

What code should I provide to get your help?

I have read SAD "12.4.4. How to cache"... and I tryed to make on server so:

aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
aRestServer.Cache.Clear;
aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

but without success.

Please show me the way.

Update:
MySQL database engine: InnoDB.
Maybe I should play with InnoDB buffer?
If yes, pls show me what should I do.
Thanks.

Last edited by alexdmatveev (2015-12-09 12:53:15)

ab · 2015-12-09 13:38:33

AFAIK the cache is not used for blobs.

How do you connect to MySQL?
Did you put some breakpoints and see what happens?

alexdmatveev · 2015-12-09 14:55:10

Thanks.

With mongoDB it worked pretty well.

Server part:

  aProps := TSQLDBUniDACConnectionProperties.Create('MySQL', 'mednet_server', 'root', 'Tiger4711');
  aProps.SpecificOptions.Values['Server'] := 'localhost';
  aProps.SpecificOptions.Values['Port'] := '3306';
  aProps.SpecificOptions.Values['UseUnicode'] := 'True';
  aProps.SpecificOptions.Values['Charset'] := 'utf8';
  aProps.ConnectionTimeOutMinutes := 480;

  aProps.ThreadingMode := tmThreadPool;
  //aProps.ThreadingMode := tmMainConnection;

  try
    aModel := CreateDataModel;
    VirtualTableExternalRegisterAll(aModel, aProps);
    VirtualTableExternalRegister(aModel, TServerAccounts, aProps, 'accounts');
    VirtualTableExternalRegister(aModel, TServerAccount_Institutions, aProps, 'account_institutions');
    VirtualTableExternalRegister(aModel, TPracticesoft_mail_params_default, aProps, 'practicesoft_mail_params');
    VirtualTableExternalRegister(aModel, TPracticesoft_form_params_default, aProps, 'practicesoft_form_params');

    MapFields(aModel);

    try
      DeleteFile('data1.db3');
      aRestServer := TSQLRestServerDB.Create(aModel,'data1.db3', true); // authentication=true
      aRestServer.Cache.Clear;
      aRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
      aRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

My test:

  Result := ConnectionManager.Client.Add(msg.Details, true) >= 0;
  ConnectionManager.Client.UpdateBlobFields(msg.Details); //here I post different objects with different blob values

  body := TTransaction_form.Create(ConnectionManager.Client, msg.Details.ID); 
  ConnectionManager.Client.RetrieveBlobFields(body); // here I always get the SAME blob value
  //and the VALUE is the first value I posted after restarting server part

and Also I have the same values in MySQL tables. I checked them in my MySQL Manager without mORMot.

Last edited by alexdmatveev (2015-12-09 15:04:57)

oz · 2015-12-09 15:12:01

Your test code shows only one UpdateBlob method call. It would be helpful to see more code.
Did you try Arnauds suggestion with breakpoints? Maybe your rec object already contains wrong values.
Another good thing to do is to seperate the issue into some testcase based demo project.
Did you try other DataAccess components (zeos) too?

alexdmatveev · 2015-12-09 15:23:29

yeah, I have provided some code with RetrieveBlobFields call and creation of a new object to fill it with RetrieveBlodFields.
Do you need more?

Zeos - it is not allowed in the project. Sorry.

If I have no good answer I will prepare separate project.

alexdmatveev · 2015-12-09 15:36:13

I have placed a breakpoint in SynCrossPlatformREST.pas in

function TSQLRest.Update(Value: TSQLRecord; FieldNames: string): boolean;
var tableIndex: Integer;
    json: string;
begin
  if (Value=nil) or (Value.ID<=0) then begin
    result := false;
    exit;
  end;
  tableIndex := Model.GetTableIndexExisting(Value.RecordClass);
  json := Model.Info[tableIndex].ToJSONUpdate(self,Value,FieldNames,false);
  result := ExecuteUpdate(tableIndex,Value.ID,json);
  if result then
    Value.fInternalState := InternalState;
end;

and I see that I send always different json value. It is correct.

But as result in DB I have the same blob value in all records.
All other fields (non-blob) work good.

ab · 2015-12-09 15:37:39

Try to disable statement cache at SynDB level.
Perhaps the blob value parameter is not reset between calls to MySQL.

Also ensure you have the latest revision of the framework (currently 2147).

alexdmatveev · 2015-12-09 16:01:22

could you write me name of the unit?
mORMot has lot of units and I want to be sure I will not make any troubles to mORMot server.

Thanks a lot for your help.

Last edited by alexdmatveev (2015-12-09 16:05:39)

alexdmatveev · 2015-12-09 16:57:11

unit SynDBUniDAC

procedure TSQLDBUniDACStatement.DatasetExecSQL;
begin
  (fQuery as TUniQuery).Execute;
end;

When it executes an query to update myfield it has always same parameter value.

What should I do to force it to refresh parameter value?

Thanks

ab · 2015-12-09 17:33:27

Parameter is bound within UpdateBlobFields method.

alexdmatveev · 2015-12-09 17:52:49

and here I stopped to understand.
I set breakpoints into all methods UpdateBlobFields in mormot.pas and mormotdb.pas.

Then I call client method to update blob.
And my breakpoints are not fired. I mean code execution does not reach my breakpoints.

I think you mean method:

function TSQLRestStorageExternal.UpdateBlobFields(Value: TSQLRecord): boolean;

Here I see parameters binding... But the method is not called at all.

Please help me to understand it.

Thanks a lot.

alexdmatveev · 2015-12-09 19:00:42

Maybe it is a problem of crossplatform library?

there is no method UpdateBlobFields

instead of the method it is offered to use Update method

function TSQLRest.Update(Value: TSQLRecord; FieldNames: string): boolean;

and that is the reason why UpdateBlobFields is not called on the server...

Is there a way to force update blob fields correctly via crossplaform library?

I have tryed to use asterix

ConnectionManager.Client.Update(msg.Details, '*');

as it is described here:

    /// update a member
    // - you can let default FieldNames='' to update simple fields, '*' to
    // update all fields (including BLOBs), or specify a CSV list of updated fields
    function Update(Value: TSQLRecord; FieldNames: string=''): boolean; virtual;

Last edited by alexdmatveev (2015-12-09 19:05:15)

ab · 2015-12-09 19:23:11

I did not notice that you were using the cross-platform client!

In fact, there is no blob update method in the cross-platform client.
If you use '*' as FieldNames, all fields are updated, and blob should be sent as Base-64.

I still do not understand your exact problem.
If it worked with MongoDB, there is no reason why it would not work with MySQL, due to the cross-platform client part...
I can't reproduce your issue here, so please try to use the debugger on server side, and find out what happens.

alexdmatveev · 2015-12-10 09:22:48

ok, thanks a lot.
we have finished this experiment. and moved back to mongo.

mORMot Open Source

#1 2015-12-09 12:40:19

blob cache

#2 2015-12-09 13:38:33

Re: blob cache

#3 2015-12-09 14:55:10

Re: blob cache

#4 2015-12-09 15:12:01

Re: blob cache

#5 2015-12-09 15:23:29

Re: blob cache

#6 2015-12-09 15:36:13

Re: blob cache

#7 2015-12-09 15:37:39

Re: blob cache

#8 2015-12-09 16:01:22

Re: blob cache

#9 2015-12-09 16:57:11

Re: blob cache

#10 2015-12-09 17:33:27

Re: blob cache

#11 2015-12-09 17:52:49

Re: blob cache

#12 2015-12-09 19:00:42

Re: blob cache

#13 2015-12-09 19:23:11

Re: blob cache

#14 2015-12-10 09:22:48

Re: blob cache

Board footer