Multithreading Problem

itSDS · 2015-11-26 15:25:41

Hi AB we encounter exception in SynDB in our MVC - Service.

The first Problem is that we cannot reproduce it easily.

I'll explain what happens.

a View with 4 edit controls is displayed in the browser. There a button "Check and Save" is displayed.
Now the user press this button multiple times (doppelclick, trippleclick, ...)
In the service the same viewfunction is called as often as it is clicked after each other. This is handled in a number of Threads.
In this viewfunction a lot of "RestModel" Queries are called. Cause of the multi click they are called parallel in several Threads.
(as in your mvcserver but lets say 10 to 100 queries in a row)

Normaly if you only click this button once, a Result Page should be shown.

Now we encounter a lot exception:

Erste Gelegenheit für Exception bei $77873E28. Exception-Klasse EFDException mit Meldung '[FireDAC][Phys][MySQL]-326. Ausführen der Aktion nicht möglich, weil die vorherige Aktion noch nicht abgeschlossen ist'. Prozess Service.exe (4896)
Erste Gelegenheit für Exception bei $008EC1E7. Exception-Klasse $C0000005 mit Meldung 'access violation at 0x008ec1e7: write of address 0x00640092'. Prozess Service.exe (4896)
Erste Gelegenheit für Exception bei $77873E28. Exception-Klasse EHttpApiServer mit Meldung 'HttpSendHttpResponse failed: Der angegebene Netzwerkname ist nicht mehr verfügbar (64)'. Prozess Service.exe (4896)
Erste Gelegenheit für Exception bei $77873E28. Exception-Klasse EAccessViolation mit Meldung 'Zugriffsverletzung bei Adresse 008EC1E7 in Modul 'Service.exe'. Schreiben von Adresse 00640092'. Prozess Service.exe (4896)
Erste Gelegenheit für Exception bei $77873E28. Exception-Klasse EAccessViolation mit Meldung 'Zugriffsverletzung bei Adresse 008EC1E7 in Modul 'Service.exe'. Schreiben von Adresse 00640092'. Prozess Service.exe (4896)
Erste Gelegenheit für Exception bei $77873E28. Exception-Klasse EHttpApiServer mit Meldung 'HttpSendHttpResponse failed: Der angegebene Netzwerkname ist nicht mehr verfügbar (64)'. Prozess Service.exe (4896)
Erste Gelegenheit für Exception bei $77873E28. Exception-Klasse EAccessViolation mit Meldung 'Zugriffsverletzung bei Adresse 008EC1E7 in Modul 'Service.exe'. Schreiben von Adresse 00640092'. Prozess Service.exe (4896)

we encountered the Problem first using only SQLite as a db and got Disk I/O Errors.
we swtiched to FireDAC to get rid of the Disk I/O. But now we get Exception as shown above.

What can i do to help fix the Problem ? ATM i have no more Idea to find the Reason. The Place Exception arise are not the same everytime.

Concerning Disk I/O we got this if SQLite DB - File is locked by another Thread.

itSDS · 2015-11-26 16:08:28

To test i switched to UniDAC and got error Message that MySQL Commands are out of sync.
I think that there is a Problem calling multiple Queries parallel using 1 Connection.
Is there a Option which can fix it.

ATM i disabled UseCache. The Error is not gone, but there are less Exceptions.

danielkuettner · 2015-11-26 16:42:19

That's not a problem of MVC nor mORMot, but simple a database one.
Every time you click the button a db call is executed. If the first call is still in progress every other call raise the exception.
This sounds like a problem of transaction handling of firedac/unidac and your code using it.

itSDS · 2015-11-26 19:15:21

Hi Daniel I think it is a mORMot - Problem to execute all Database calls in the right order especially in a multi - threaded environment.

In my Code there is a single line of code selecting Data via Restmodel and TSQLRecord.
I have no option to exeute this in another way.
I have no option to tell the User of a browser to use it if all other user (he does not know of) are finished saving data - just a joke buts the truth

Please read
11.5.3. Proven behavior in the handbook

Last edited by itSDS (2015-11-26 19:16:35)

itSDS · 2015-11-26 19:45:12

ATM i try this option:

  aServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
  aServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;

found a few lines above 11.5.3 to place all database calls in 1 Single Thread.
It seems that FireDAC is not thread safe in my case.

danielkuettner · 2015-11-27 11:28:09

I never used firedac, but afair to use firedac with multi-threads you have to use TFDManager. Why not simple use a fast, perfectly tested, very fast and open source framework like ZEOS?

itSDS · 2015-11-27 11:55:34

with ZEOS i get this:

20151127 11491707 EXC                                   EZSQLException ("SQL Error: Lost connection to MySQL server during query") at 0091C5B7  stack trace API 00556F88 00556FB0 0040AEDC 776FD624 776EAE6F 0091C5B7 0092A0FC 0091B1FC 0091B408 00961375 00958C1A 0096EB30 005E10FD 005C9D9A 005C7581 005C79AC 005BA70F 00AC48AD 00ACC145 00606FFF
20151127 11491711 EXCOS                         EAccessViolation (C0000005) at 61666694  stack trace API 00556F88 00556FB0 0040B064
20151127 11491959 EXC                   ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":2,"Message":"Error SQLITE_ERROR (1) using 3.9.2 - 'SQL logic error or missing database' extended_errcode=1"} at 00638E13  stack trace API 00556F88 00556FB0 0040B064
20151127 11491961 ERROR                 mORMotSQLite3.TSQLRestServerDB(0284D9B0) {"ESQLite3Exception(02875350)":{"ErrorCode":1,"SQLite3ErrorCode":"ERROR","Message":"Error SQLITE_ERROR (1) using 3.9.2 - 'SQL logic error or missing database' extended_errcode=1"}} for SELECT RowID, ZaehlerNr, Energieart, EnergieartBezeichnung, WurdeAbgelesen, AlternativKundenNr, GUID, Plausibel FROM DFKARecord WHERE (KundenNr=:('123123'): OR AlternativKundenNr=:('123123'):) AND Mandant=:('testmandant'): ORDER BY RowID // SELECT RowID, ZaehlerNr, Energieart, EnergieartBezeichnung, WurdeAbgelesen, AlternativKundenNr, GUID, Plausibel FROM DFKARecord WHERE (KundenNr=? OR AlternativKundenNr=?) AND Mandant=? ORDER BY RowID stack trace API 00558BC2 005C633F 0063B202 0063D4B0 005E10E5 005C9D9A 005C7581 005BDD47 005BDDF6 00ACC3A9 00606FFF
20151127 11491961 EXC           EZSQLException ("SQL Error: Lost connection to MySQL server during query") at 0091C5B7  stack trace API 00556F88 00556FB0 0040B064
20151127 11492057 EXC                           EZSQLException ("SQL Error: MySQL server has gone away") at 0091C5B7  stack trace API 00556F88 00556FB0 0040B064
20151127 11492236 EXC                   ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":2,"Message":"Error SQLITE_ERROR (1) using 3.9.2 - 'SQL logic error or missing database' extended_errcode=1"} at 00638E13  stack trace API 00556F88 00556FB0 0040B064
20151127 11492237 ERROR                 mORMotSQLite3.TSQLRestServerDB(0284D9B0) {"ESQLite3Exception(02874FC0)":{"ErrorCode":1,"SQLite3ErrorCode":"ERROR","Message":"Error SQLITE_ERROR (1) using 3.9.2 - 'SQL logic error or missing database' extended_errcode=1"}} for SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKARecord WHERE Mandant=:('testmandant'): AND (KundenNr=:('123123'): OR AlternativKundenNr=:('123123'):) AND WurdeAbgelesen=:(0): // SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKundenAblesungRecord WHERE Mandant=? AND (KundenNr=? OR AlternativKundenNr=?) AND WurdeAbgelesen=? stack trace API 00558BC2 005C633F 0063B202 0063D4B0 005E10E5 005C9D9A 005C7581 005C79AC 005BA70F 00ACCFB1 00606FFF
20151127 11492316 EXC                                   EZSQLException ("SQL Error: MySQL server has gone away") at 0091C5B7  stack trace API 00556F88 00556FB0 0040AEDC 776FD624 776EAE6F 0091C5B7 0092A0FC 0091B1FC 0091B408 00961375 00958C1A 0096EB30 005E10FD 005C9D9A 005C7581 005C79AC 005BA70F 00AC48AD 00ACC145 00606FFF
20151127 11492317 EXC                           EZSQLException ("SQL Error: MySQL server has gone away") at 0091C5B7  stack trace API 00556F88 00556FB0 0040B064
20151127 11492318 EXC           EZSQLException ("SQL Error: MySQL server has gone away") at 0091C5B7  stack trace API 00556F88 00556FB0 0040B064
20151127 11492443 EXC   EHttpApiServer ("HttpSendHttpResponse failed: Der angegebene Netzwerkname ist nicht mehr verf?gbar (64)") at 0061C81F  stack trace API 00556F88 00556FB0 0040B064

UseCache = false and SingleThread mode is enabled

Last edited by itSDS (2015-11-27 11:56:20)

ab · 2015-11-27 12:18:05

What is the query which is executed?

danielkuettner · 2015-11-27 12:48:43

The query seems to be:
SELECT RowID, ZaehlerNr, Energieart, EnergieartBezeichnung, WurdeAbgelesen, AlternativKundenNr, GUID, Plausibel FROM DFKARecord WHERE (KundenNr=:('123123'): OR AlternativKundenNr=:('123123'):) AND Mandant=:('testmandant'):
ORDER BY RowID

The errors are:
("SQL Error: Lost connection to MySQL server during query")
("SQL Error: MySQL server has gone away")

But why are connection lost? What is the ConnectionString-Property?

This is not a problem of Zeos, btw. do you use actual Zeos-7.3?
(http://svn.code.sf.net/p/zeoslib/code-0 … esting-7.3).

I know multithreading is possible with Zeos and mysql, but mysql is not so easy to use like Firebird or mssql. You have some configuration options in Zeos to make the client-library multithread safe.

But EgonHugeist is the master of zeos here in this forum.

Last edited by danielkuettner (2015-11-27 13:39:46)

ab · 2015-11-27 13:07:52

This request has parenthesis, so is not handled directly by TSQLRestStorageExternal.AdaptSQLForEngineList.

It will use the SQLite3 virtual engine, which "may" return a lot of data (full scan) during its process.

I guess it may be the root cause of the issue.
Could you try to run the SELECT directly on the SynDB layer, e.g. using SynDBExplorer?

We may have to enhance TSQLRestStorageExternal.AdaptSQLForEngineList, so that it supports parenthesis...
Or you may try to run the request directly on the TSQLDBConnectionProperties, via a dedicated service...

danielkuettner · 2015-11-27 13:54:50

EgonHugeist supposed, that the query needs too long and therefore the connection breaks.
In mysql-server configuration are options for such timeout wich are support by zeos.
I hope this will help you a little bit.

ab · 2015-11-27 14:56:58

Yes, virtual tables may perform a full scan, which may let the query take too much time, so would break the connection.

EgonHugeist · 2015-11-27 18:08:38

@AB

stop thinking your framework is runing into an issue.. Nope! NOO multithreading problem...

@itSDS

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
please read documantions. All of us are trying to support you.

According using Zeos: add your expected TimeOut props to the Zeos connection strings see:
http://dev.mysql.com/doc/refman/5.0/en/ … tions.html

!!--->>> MYSQL_OPT_READ_TIMEOUT/MYSQL_OPT_WRITE_TIMEOUT

like 'MYSQL_OPT_WRITE_TIMEOUT=WhatEverYouWant_Seconds'
Have a look at ZDbcMySQL.pas -> procedure TZMySQLConnection.Open

Such props need to be set before a connection is opened. No idea how FireDac/UniDac is doing the job. Docs?

Hope it helps. Have fun comparing access rates of FireDac/UniDac vs. Zeos7.2+

Last edited by EgonHugeist (2015-11-27 18:20:18)

ab · 2015-11-27 20:36:27

I've let TSQLRestStorageExternal.AdaptSQLForEngineList (and therefore TSynTableStatement.Create) support parenthesis in the SQL exceptions.
See http://synopse.info/fossil/info/4fd0ce9bee

So I guess your query will now be passed directly to the external engine, and won't use a slow full scan via the SQlite3 virtual table.
I suppose it would help a lot in such cases.

itSDS · 2015-11-28 23:12:02

TYVM for your opinions on this case - i'll try and read your guesses. Just to notice: the Query was executed in 2 (or more) Threats parallel.

itSDS · 2015-11-29 17:40:41

First Testresult: Using only mORMot 1.18.2105 and FireDAC does not fix the Problem. Now i'll try with ZEOS 7.3 and EgonHugeist tips.

itSDS · 2015-11-29 18:11:19

Second Test with ZEOS 7.3, here arrise 3 errors until 4. error - connection lost

maybe it helps find the problem

1. Error: Lost Connection to MySQL Server during query:

:76c5d8a8 KERNELBASE.RaiseException + 0x48
ZDbcMySqlUtils.CheckMySQLError(TZMySQL5PlainDriver($29B0178) as IZMySQLPlainDriver,$3290148,lcExecute,'select ID,CreationDate,LastEditDate,Mandant,URLPraefix from DFMandantRecord where URLPraefix=''testmandant''',$2A4F900)
ZDbcMySqlStatement.TZMySQLStatement.ExecuteQuery('select ID,CreationDate,LastEditDate,Mandant,URLPraefix from DFMandantRecord where URLPraefix=''testmandant''')
ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQuery('select ID,CreationDate,LastEditDate,Mandant,URLPraefix from DFMandantRecord where URLPraefix=''testmandant''')
ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQueryPrepared
SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared
SynDB.TSQLDBStatement.ExecutePreparedAndFetchAllAsJSON(False,'')
mORMotDB.TSQLRestStorageExternal.EngineList('select ID,CreationDate,LastEditDate,Mandant,URLPraefix from DFMandantRecord where URLPraefix=:(''testmandant''):',False,nil)
mORMot.TSQLRestServer.EngineList('SELECT RowID,CreationDate,LastEditDate,Mandant,URLPraefix FROM DFMandantRecord WHERE URLPraefix=:(''testmandant''):',False,nil)
mORMot.TSQLRest.ExecuteList((...),'SELECT RowID,CreationDate,LastEditDate,Mandant,URLPraefix FROM DFMandantRecord WHERE URLPraefix=:(''testmandant''):')
mORMot.TSQLRest.MultiFieldValues(TSQLDFMandantRecord,'','URLPraefix=:(''testmandant''):')
mORMot.TSQLRest.Retrieve('URLPraefix=:(''testmandant''):',$35A2FD0,'')
mORMot.TSQLRecord.Create($2A6D9B0,'URLPraefix=?',(...))
KAService.ViewModel.TKAApplication.getCustomerSettings((...))
KAService.ViewModel.TKAApplication.checkZAEingaben('123123123','123123123123','30.11.2015',Nicht zugewiesen)
:006083d3 CallMethod + $27
mORMot.CallMethod(???)
mORMot.TServiceMethodExecute.RawExecute($58BF7F8,0)
mORMot.TServiceMethodExecute.ExecuteJson((...),'"zaehler_5632_1_GZ',$2ABF988,True)
mORMotMVC.TMVCRendererAbstract.ExecuteCommand(18)
mORMotMVC.TMVCRendererReturningData.ExecuteCommand(18)
mORMotMVC.TMVCRunOnRestServer.InternalRunOnRestServer($2BF1F70,'checkZAEingaben/')
mORMotMVC.TMVCRunOnRestServer.RunOnRestServerRoot($2BF1F70)
mORMot.TSQLRestServerURIContext.ExecuteSOAByMethod
mORMot.TSQLRestServerURIContext.ExecuteCommand
mORMot.TSQLRestServer.URI($58BFCD0)
mORMotHttpServer.TSQLHttpServer.Request($2ACD0F8)
SynCrtSock.THttpServerGeneric.Request($2ACD0F8)
SynCrtSock.THttpApiServer.Execute
:004c53a8 ThreadProc + $4C
:0040ba0a ThreadWrapper + $2A
:771f38f4 KERNEL32.BaseThreadInitThunk + 0x24
:77565663 ; 
:7756562e ;

2. Error Other Threat:

Cannot retrieve ResultSet data
:76c5d8a8 KERNELBASE.RaiseException + 0x48
ZDbcMySqlResultSet.TZAbstractMySQLResultSet.Open
ZDbcMySqlResultSet.TZAbstractMySQLResultSet.Create(TZMySQL5PlainDriver($29B0178) as IZMySQLPlainDriver,TZMySQLStatement($29A4030) as IZStatement,'select CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID,ID from DFKAServiceRecord where Mandant=''testmandant''',$3290148,nil)
ZDbcMySqlStatement.TZMySQLStatement.CreateResultSet('select CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID,ID from DFKAServiceRecord where Mandant=''testmandant''')
ZDbcMySqlStatement.TZMySQLStatement.ExecuteQuery('select CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID,ID from DFKAServiceRecord where Mandant=''testmandant''')
ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQuery('select CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID,ID from DFKAServiceRecord where Mandant=''testmandant''')
ZDbcStatement.TZEmulatedPreparedStatement.ExecuteQueryPrepared
SynDBZeos.TSQLDBZEOSStatement.ExecutePrepared
mORMotDB.TSQLVirtualTableCursorExternal.Search($35ACFB8)
mORMotSQLite3.vt_Filter(($2BDD8F8, $5E81810),0,#1,1,(...))
SynSQLite3Static.sqlite3_value_numeric_type(44098152)
:00b95423 sqlite3_value_numeric_type + $5EDB
SynSQLite3Static.sqlite3_result_value(44098152,4241888)
:00b8e2cb sqlite3_result_value + $23B
SynSQLite3Static.sqlite3_step(44098152)
:00b8e3c8 sqlite3_step + $74
SynSQLite3.TSQLRequest.Step
SynSQLite3.TSQLRequest.Execute(0,'',$34AB018,False)
mORMotSQLite3.TSQLRestServerDB.MainEngineList('SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKAServiceRecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):',False,nil)
mORMot.TSQLRestServer.EngineList('SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKAServiceRecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):',False,nil)
mORMot.TSQLRest.ExecuteList((...),'SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKAServiceRecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):')
mORMot.TSQLRest.MultiFieldValues(TSQLDFKAServiceRecord,'','(KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):')
mORMot.TSQLRest.Retrieve('(KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):',$2A48818,'')
mORMot.TSQLRecord.Create($2A6D9B0,'(KundenNr=? OR AlternativKundenNr=?) AND Mandant=?',(...))
KAService.ViewModel.TKAApplication.ZAEingabe(???,???,???,???,Null,'','',Nicht zugewiesen,Nicht zugewiesen,True,Nicht zugewiesen,Nicht zugewiesen,Nicht zugewiesen)
:006083d3 CallMethod + $27
mORMot.CallMethod(???)
mORMot.TServiceMethodExecute.RawExecute($59FF7F8,0)
mORMot.TServiceMethodExecute.ExecuteJson((...),'"AFormular',$2ABF618,True)
mORMotMVC.TMVCRendererAbstract.ExecuteCommand(18)
mORMotMVC.TMVCRendererReturningData.ExecuteCommand(18)
mORMotMVC.TMVCRunOnRestServer.InternalRunOnRestServer($2BF11B0,'checkZAEingaben/')
mORMotMVC.TMVCRunOnRestServer.RunOnRestServerRoot($2BF11B0)
mORMot.TSQLRestServerURIContext.ExecuteSOAByMethod
mORMot.TSQLRestServerURIContext.ExecuteCommand
mORMot.TSQLRestServer.URI($59FFCD0)
mORMotHttpServer.TSQLHttpServer.Request($2ACD0B0)
SynCrtSock.THttpServerGeneric.Request($2ACD0B0)
SynCrtSock.THttpApiServer.Execute
:004c53a8 ThreadProc + $4C
:0040ba0a ThreadWrapper + $2A
:771f38f4 KERNEL32.BaseThreadInitThunk + 0x24
:77565663 ; 
:7756562e ;

3. Error - ESQLite3Exception mit Meldung 'Error SQLITE_ERROR (1) using 3.9.2 - 'SQL logic error or missing database' extended_errcode=1'. Prozess KAService.Service.exe (312)

:76c5d8a8 KERNELBASE.RaiseException + 0x48
SynSQLite3.sqlite3_check(44052024,1)
SynSQLite3.TSQLRequest.Step
SynSQLite3.TSQLRequest.Execute(0,'',$34AB018,False)
mORMotSQLite3.TSQLRestServerDB.MainEngineList('SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKAServiceRecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):',False,nil)
mORMot.TSQLRestServer.EngineList('SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKAServiceRecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):',False,nil)
mORMot.TSQLRest.ExecuteList((...),'SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKAServiceRecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):')
mORMot.TSQLRest.MultiFieldValues(TSQLDFKAServiceRecord,'','(KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):')
mORMot.TSQLRest.Retrieve('(KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):',$2A48818,'')
mORMot.TSQLRecord.Create($2A6D9B0,'(KundenNr=? OR AlternativKundenNr=?) AND Mandant=?',(...))
KAService.ViewModel.TKAServiceApplication.ZAEingabe(???,???,???,???,Null,'','',Nicht zugewiesen,Nicht zugewiesen,True,Nicht zugewiesen,Nicht zugewiesen,Nicht zugewiesen)
:006083d3 CallMethod + $27
mORMot.CallMethod(???)
mORMot.TServiceMethodExecute.RawExecute($59FF7F8,0)
mORMot.TServiceMethodExecute.ExecuteJson((...),'"AFormular',$2ABF618,True)
mORMotMVC.TMVCRendererAbstract.ExecuteCommand(18)
mORMotMVC.TMVCRendererReturningData.ExecuteCommand(18)
mORMotMVC.TMVCRunOnRestServer.InternalRunOnRestServer($2BF11B0,'checkZAEingaben/')
mORMotMVC.TMVCRunOnRestServer.RunOnRestServerRoot($2BF11B0)
mORMot.TSQLRestServerURIContext.ExecuteSOAByMethod
mORMot.TSQLRestServerURIContext.ExecuteCommand
mORMot.TSQLRestServer.URI($59FFCD0)
mORMotHttpServer.TSQLHttpServer.Request($2ACD0B0)
SynCrtSock.THttpServerGeneric.Request($2ACD0B0)
SynCrtSock.THttpApiServer.Execute
:004c53a8 ThreadProc + $4C
:0040ba0a ThreadWrapper + $2A
:771f38f4 KERNEL32.BaseThreadInitThunk + 0x24
:77565663 ; 
:7756562e ;

4. ... error SQL Error: MySQL Server has gone away

ab · 2015-11-29 18:47:10

Are you sure you did make the test with the latest TSQLRestStorageExternal.AdaptSQLForEngineList modifications?
See http://synopse.info/fossil/info/4fd0ce9bee

'(KundenNr=? OR AlternativKundenNr=?) AND Mandant=?'
or
'(KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):'
should not use TSQLRestServerDB.MainEngineList, but directly TSQLRestStorageExternal.EngineList.

itSDS · 2015-11-29 19:17:24

Yes i'm Sure to use 1.18.2105 (except the 1 line bugfix i posted in other thread)

ab · 2015-11-29 19:20:30

Could you please try to see why TSQLRestStorageExternal.AdaptSQLForEngineList does not accept the incoming SQL?

itSDS · 2015-11-29 20:21:28

Hi AB here are the queries which fail in AdaptSQLForEngineList

'SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKARecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''): AND Energieart=:(''GZ''): AND ZaehlerNr LIKE :(''%4321''):'

'SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKARecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):'

'SELECT RowID, ZaehlerNr, Energieart, EnergieartBezeichnung, WurdeAbgelesen, AlternativKundenNr, GUID, Plausibel FROM DFKARecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''): ORDER BY RowID'



'SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKARecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND ZaehlerNr=:(''4321''): AND Energieart=:(''GZ''): AND Mandant=:(''testmandant''):'

'SELECT RowID,CreationDate,LastEditDate,KundenNr,ZaehlerNr,Energieart,EnergieartBezeichnung,Mandant,AufgabenID,Eingabedatum,Ablesedatum,Monteurablesung,WurdeAbgelesen,GueltigVon,GueltigBis,Plausibel,AlternativKundenNr,IP,Telefon,EMail,GUID FROM DFKARecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''):'

'SELECT RowID, ZaehlerNr, Energieart, EnergieartBezeichnung, WurdeAbgelesen, AlternativKundenNr, GUID, Plausibel FROM DFKARecord WHERE (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):) AND Mandant=:(''testmandant''): ORDER BY RowID'

mpv · 2015-11-29 20:43:00

How many rows are in the DFKARecord table? All your queries is always perform a table full scan - this is bad in any case.
IMHO it is better to rewrite query and remove this OR: (KundenNr=:(''123123''): OR AlternativKundenNr=:(''123123''):), for example by using union all
The second tip is to increase timeouts in the mysql server config: http://www.rackspace.com/knowledge_cent … n-a-server

Last edited by mpv (2015-11-29 20:47:11)

itSDS · 2015-11-29 23:30:51

ATM there are 150000 Records but i have index on select - fields

The queries are generated by mORMot !

EgonHugeist · 2015-11-30 04:50:28

@itSDS

maybe i've to explain a bit more even if it documented in the links i've posted before...

While the Server is collecting the data, your client library(libmysql.dll f.e.) gets a timeout because there is no responce from the Server. So the client lib raises the error "SQL Error: Lost connection to MySQL server during query", discards all handles and forces you to reconnect.

Continuing thinking about changing "something" after retrieving data is a nop.
it's not a problem of threads or anything else after quering the rows...
The error just indicates to many DATA or a bad/missing index or oversized field structures.

Advices to resolve the issue:
1. Find out why quering your select tooks so long. e.g. missing Index? / To many rows? / Field-Size to huge even if MySQL supports a Index?
2. if there are to many rows -> limit the data by quering the data in more than one step like using first/top skip/offset syntax
3. Again and like mpv mentioned: set the timeout options high enough to do not run into that known behavior.

From my pov you're using threads to have no wait timeouts in your main app. So you know about the huge responce time. The reason is?
Did you increase the TimeOut options? Did you?

ab · 2015-11-30 07:23:38

Please try http://synopse.info/fossil/info/1429e698cf

If you had enabled the logs, and checked what occurred, you may have seen and reported here directly the problem, reported as "AdaptSQLForEngineList: Unhandled mixed AND/OR for..." in the logs.

itSDS · 2015-11-30 13:49:35

I tested the new release since now - this morning and here are my Results:

1. FireDAC - Connector works without exception now - Seems that the main Problem ist solved with your fixes in AdaptSQLForEngineList
2. ZEOS 7.3 reports connection lost...

Seems that FireDAC Connector uses other Connection/Open Settings than ZEOS. Sry that i can not figure out cause i do not have FireDAC Source...

@EgonHugeist
My Problems is not mysql nor the timeout. The multi Threading that happens is the MultiThreading invoked by the mORMot Framework as desired.
The Timouts mentioned in your links a general MySQL TimeOuts which last about 8h
I think there is no real timeout Problem with ZEOS but a multi threading one. But i'm no ZEOS enthusiast so i cannot say whats wrong there.

just to say what i mean:

uses
  SynDBZeos,
...

  FConnection := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL,host + ':' + port), databasename, username, password);

for FireDAC its nearly the same:

uses
  SynDBFireDAC,
  FireDAC.Phys.MySQL,
...
  FConnection := TSQLDBFireDACConnectionProperties.Create(FIREDAC_PROVIDER[dMySQL] + '?Server=' + host + ';Port=' + port, databasename, username, password);

this 2 line of code is from me concerning ZEOS integration.

In my opinion this is enough. All other Settings to run MVC with ZEOS or FireDAC should be done in the Framework ! And i think Arnaud thinks the same.
So if you have any promises i would appreciate that you implement them as Option in SynDBZeos or make a code sample i can test.
Cause i do not really know where to set in in the Framework !
Sry for my bad english buts not my mother language

Last edited by itSDS (2015-11-30 13:50:18)

danielkuettner · 2015-11-30 17:09:23

With actual mORMot and Zeos 7.3 just a small test (32bit) under XE6 with libmysql.dll from EMBA and mysql-server on a freebsd 10.2 and an old little sample db:

  Testprops := TMySQLDBZEOSConnectionProperties.Create
    (TSQLDBZEOSConnectionProperties.URI(dMysql, '192.168.1.10:3306',
    'libmysql.dll', false), 'phpgroupware', 'username', 'password');
  TestProps.Execute('select * from phpgw_lang', []);

  TThread.CreateAnonymousThread(procedure
    var
      r: ISQLDBRows;
      s: RawUTF8;
    begin
     r:= TestProps.Execute('select * from phpgw_lang', []);
     while r.Step do begin
       s:= r.ColumnUTF8('message_id');
     end;
  end).Start;

  TThread.CreateAnonymousThread(procedure
    var
      r: ISQLDBRows;
      s: RawUTF8;
    begin
     r:= TestProps.Execute('select * from phpgw_lang', []);
     while r.Step do begin
       s:= r.ColumnUTF8('message_id');
     end;
  end).Start;

   TThread.CreateAnonymousThread(procedure
    var
      r: ISQLDBRows;
      s: RawUTF8;
    begin
     r:= TestProps.Execute('select * from phpgw_lang', []);
     while r.Step do begin
       s:= r.ColumnUTF8('message_id');
     end;
  end).Start;

No exception, all works as expected.

Last edited by danielkuettner (2015-11-30 17:11:21)

itSDS · 2015-11-30 19:12:49

Hi Daniel nice sample but it isn't the same as we do in our MVC Service.

We use another DB-Interface in our MVC and AB has already fixed the Multi Threading Problem for us.
(In your samle you use the Interface from TDBConnectionProperties whereas we use CreateAndFilPrepare from TSQLRecord)
Why it doesn't work with ZEOS is generally another Problem and i think if i need it i would have to make another Thread for this.
It was just a try for me to see if zeos would do the job - but the DB Interface was not really the Problem of this Thread.

For me this Thread is finished.

itSDS · 2015-12-03 11:09:14

Hi AB,

i re read this Thread an now i understand your notice:

If you had enabled the logs, and checked what occurred, you may have seen and reported here directly the problem, reported as "AdaptSQLForEngineList: Unhandled mixed AND/OR for..." in the logs.

sry - i have enabled full logs all the time and the line AdaptSQLForEngineList... did not occur.

mORMot Open Source

#1 2015-11-26 15:25:41

Multithreading Problem

#2 2015-11-26 16:08:28

Re: Multithreading Problem

#3 2015-11-26 16:42:19

Re: Multithreading Problem

#4 2015-11-26 19:15:21

Re: Multithreading Problem

#5 2015-11-26 19:45:12

Re: Multithreading Problem

#6 2015-11-27 11:28:09

Re: Multithreading Problem

#7 2015-11-27 11:55:34

Re: Multithreading Problem

#8 2015-11-27 12:18:05

Re: Multithreading Problem

#9 2015-11-27 12:48:43

Re: Multithreading Problem

#10 2015-11-27 13:07:52

Re: Multithreading Problem

#11 2015-11-27 13:54:50

Re: Multithreading Problem

#12 2015-11-27 14:56:58

Re: Multithreading Problem

#13 2015-11-27 18:08:38

Re: Multithreading Problem

#14 2015-11-27 20:36:27

Re: Multithreading Problem

#15 2015-11-28 23:12:02

Re: Multithreading Problem

#16 2015-11-29 17:40:41

Re: Multithreading Problem

#17 2015-11-29 18:11:19

Re: Multithreading Problem

#18 2015-11-29 18:47:10

Re: Multithreading Problem

#19 2015-11-29 19:17:24

Re: Multithreading Problem

#20 2015-11-29 19:20:30

Re: Multithreading Problem

#21 2015-11-29 20:21:28

Re: Multithreading Problem

#22 2015-11-29 20:43:00

Re: Multithreading Problem

#23 2015-11-29 23:30:51

Re: Multithreading Problem

#24 2015-11-30 04:50:28

Re: Multithreading Problem

#25 2015-11-30 07:23:38

Re: Multithreading Problem

#26 2015-11-30 13:49:35

Re: Multithreading Problem

#27 2015-11-30 17:09:23

Re: Multithreading Problem

#28 2015-11-30 19:12:49

Re: Multithreading Problem

#29 2015-12-03 11:09:14

Re: Multithreading Problem

Board footer