You are not logged in.
- Topics: Active | Unanswered
Pages: 1
#1 2015-12-01 23:24:14
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Unpredictable DB Failures
Hi AB - may be this is a reopen of the Multi Thread - Test or a new Problem.
But it deals with the same Service.
Let me explain:
The Service has a REST Interface which is triggered from several automatic Clients every 10 min. The Clients execute ~ 100-200 Queries.
At the same Time the MVC - Interface mentioned in the former Multi Thread Problem is called.
I testet again with FireDAC/ZEOS on MySQL (Wait_Timeout = 1000s in my.ini)
Suddenly one of the REST - Calls fail - here is log output for first error an following errors
ZEOS - Test (after 40min):
20:24:00 Enter 12 mORMotSQLite3.TSQLRestServerDB(011FD9B0).URI(GET myservice?session_signature=0A320086004695A2068E125C inlen=222)
20:24:00 User auth 12 mORMot.TSQLRestRoutingREST(01861CB0) mORMotUser/171049094
20:24:00 Enter 12 SynDBZeos.TSQLDBZEOSStatement(011E1910).00A0998F
20:24:00 SQL 12 SynDBZeos.TSQLDBZEOSStatement(011E1910) select ID,AufgabenID,KundenNr,ZaehlerNr,Energieart,Eingabedatum,Plausibel,Telefon,EMail from DFKARecord where Mandant='testmandant' and WurdeAbgelesen=1 and LastEditDate>=42339
20:24:00 Enter 20 mORMotSQLite3.TSQLRestServerDB(011FD9B0).URI(GET myservice?session_signature=0A3200870003786B8456DB58 inlen=89)
20:24:00 User auth 20 mORMot.TSQLRestRoutingREST(01863830) mORMotUser/171049095
20:24:00 Enter 20 SynDBZeos.TSQLDBZEOSStatement(011E19C0).00A0998F
20:24:00 SQL 20 SynDBZeos.TSQLDBZEOSStatement(011E19C0) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=124758
20:24:00 Exception 12 EZSQLException ("Cannot retrieve Resultset data") at 0091F5B1 stack trace API 0054ACDA 0040AF5C 77A80083 77A807FF 0091F5B1 00927AD6 00927C0A 008BB9CF 00A09CE9 00A02638 00A164E7 005BFDE9 005BEBDD 005C4272 00609E1C 005F9B17 004C53A8 0040BA0A 75357C04 77A9AD1F 77A9ACEA
20:24:00 Exception 12 EZSQLException ("Cannot retrieve Resultset data") at 0091F5B1 stack trace API 0054ACDA 0040B0E4
20:24:00 Leave 12 00.011.699
20:24:00 Debug 12 mORMotSQLite3.TSQLRestServerDB(011FD9B0) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EZSQLException":{"EZSQLException":"Cannot retrieve Resultset data"}} }
20:24:00 Server 12 mORMotSQLite3.TSQLRestServerDB(011FD9B0) mORMotUser 194.9.125.254 GET myservice/ ORM-Get -> 500 with outlen=104 in 12057 us
20:24:00 Leave 12 00.012.063
20:24:00 Exception 20 EZSQLException ("SQL Error: Lost connection to MySQL server during query") at 0091B814 stack trace API 0054ACDA 0040B0E4
20:24:00 Leave 20 00.002.702
20:24:00 Debug 20 mORMotSQLite3.TSQLRestServerDB(011FD9B0) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EZSQLException":{"EZSQLException":"SQL Error: Lost connection to MySQL server during query"}} }
20:24:00 Server 20 mORMotSQLite3.TSQLRestServerDB(011FD9B0) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 500 with outlen=129 in 2893 us
20:24:00 Leave 20 00.002.899
20:24:00 Enter 25 mORMotSQLite3.TSQLRestServerDB(011FD9B0).URI(GET myservice?session_signature=0A3200870003786B8456DB58 inlen=89)
20:24:00 User auth 25 mORMot.TSQLRestRoutingREST(01861890) mORMotUser/171049095
20:24:00 Enter 25 SynDBZeos.TSQLDBZEOSStatement(011E19C0).00A0998F
20:24:00 SQL 25 SynDBZeos.TSQLDBZEOSStatement(011E19C0) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=110729
20:24:00 Exception 25 EZSQLException ("SQL Error: MySQL server has gone away") at 0091B814 stack trace API 0054ACDA 0040B0E4
20:24:00 Leave 25 00.000.828
20:24:00 Debug 25 mORMotSQLite3.TSQLRestServerDB(011FD9B0) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EZSQLException":{"EZSQLException":"SQL Error: MySQL server has gone away"}} }
20:24:00 Server 25 mORMotSQLite3.TSQLRestServerDB(011FD9B0) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 500 with outlen=111 in 1020 us
20:24:00 Leave 25 00.001.027
20:24:00 Enter 17 mORMotSQLite3.TSQLRestServerDB(011FD9B0).URI(GET myservice?session_signature=0A3200870003786B8456DB58 inlen=89)
20:24:00 User auth 17 mORMot.TSQLRestRoutingREST(01863F10) mORMotUser/171049095
20:24:00 Enter 17 SynDBZeos.TSQLDBZEOSStatement(011E19C0).00A0998F
20:24:00 SQL 17 SynDBZeos.TSQLDBZEOSStatement(011E19C0) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=122673
20:24:00 Exception 17 EZSQLException ("SQL Error: MySQL server has gone away") at 0091B814 stack trace API 0054ACDA 0040B0E4
20:24:00 Leave 17 00.000.710
20:24:00 Debug 17 mORMotSQLite3.TSQLRestServerDB(011FD9B0) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EZSQLException":{"EZSQLException":"SQL Error: MySQL server has gone away"}} }
20:24:00 Server 17 mORMotSQLite3.TSQLRestServerDB(011FD9B0) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 500 with outlen=111 in 889 us
20:24:00 Leave 17 00.000.895
20:24:00 Enter 3 mORMotSQLite3.TSQLRestServerDB(011FD9B0).URI(GET myservice?session_signature=0A3200870003786C3DAD00B0 inlen=89)
20:24:00 User auth 3 mORMot.TSQLRestRoutingREST(01862910) mORMotUser/171049095
20:24:00 Enter 3 SynDBZeos.TSQLDBZEOSStatement(011E19C0).00A0998F
20:24:00 SQL 3 SynDBZeos.TSQLDBZEOSStatement(011E19C0) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=125384
20:24:00 Exception 3 EZSQLException ("SQL Error: MySQL server has gone away") at 0091B814 stack trace API 0054ACDA 0040B0E4
20:24:00 Leave 3 00.000.689
20:24:00 Debug 3 mORMotSQLite3.TSQLRestServerDB(011FD9B0) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EZSQLException":{"EZSQLException":"SQL Error: MySQL server has gone away"}} }FireDAC (after 13min):
22:58:44 Enter 32 mORMotSQLite3.TSQLRestServerDB(0120DC20).URI(GET myservice?session_signature=0AD2B7F500040616AD5BD2EF inlen=89)
22:58:44 User auth 32 mORMot.TSQLRestRoutingREST(01A06920) mORMotUser/181581813
22:58:44 Exception 32 EInvalidPointer ("Ungültige Zeigeroperation") at 008A5473 stack trace API 00551092 0040AD98
22:58:44 Debug 32 mORMotSQLite3.TSQLRestServerDB(0120DC20) TSQLRestRoutingREST.Error: { "errorCode":400, "errorText":"Bad Request" }
22:58:44 Server 32 mORMotSQLite3.TSQLRestServerDB(0120DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 400 with outlen=49 in 627 us
22:58:44 Leave 32 00.000.633
22:58:44 Enter 33 mORMotSQLite3.TSQLRestServerDB(0120DC20).URI(GET myservice?session_signature=0AD2B7F500040616AD5BD2EF inlen=89)
22:58:44 User auth 33 mORMot.TSQLRestRoutingREST(01A07580) mORMotUser/181581813
22:58:44 Enter 33 SynDBFireDAC.TSQLDBFireDACStatement(04B84880).00876981
22:58:44 SQL 33 SynDBFireDAC.TSQLDBFireDACStatement(04B84880) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=113424
22:58:44 Exception OS 33 EAccessViolation (C0000005) at 00899088 stack trace 009565CD 00956C1C 00928D5C 00916758 0091A2AD 008DC365 008DF5DE 0091C22F 0066914F 008DC798 00668F8B 00876A58 00406FF2 00871334 0097D88B 005C1DBE 005C61A1 005C4F95 00552911 005C4B1A 0040AFD2 005C4AE2 005CA62A 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE
22:58:44 Exception OS 33 EAccessViolation (C0000005) at 00899088 stack trace 0066914F 008DC798 00668F8B 00876A58 00406FF2 00871334 0097D88B 005C1DBE 005C61A1 005C4F95 00552911 005C4B1A 0040AFD2 005C4AE2 005CA62A 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE 75357C04 77A9AD1F 77A9ACEA
22:58:44 Exception OS 33 EAccessViolation (C0000005) at 00899088 stack trace 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE 75357C04 77A9AD1F 77A9ACEA
22:58:44 Leave 33 00.002.964
22:58:44 Debug 33 mORMotSQLite3.TSQLRestServerDB(0120DC20) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EAccessViolation":{"EAccessViolation":"Zugriffsverletzung bei Adresse 00899088 in Modul 'myservice.Service.exe'. Lesen von Adresse 00000024"}} }
22:58:44 Server 33 mORMotSQLite3.TSQLRestServerDB(0120DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 500 with outlen=183 in 3149 us
22:58:44 Leave 33 00.003.156
22:58:44 Enter 18 mORMotSQLite3.TSQLRestServerDB(0120DC20).URI(GET myservice?session_signature=0AD2B7FA0047234EA952DA13 inlen=89)
22:58:44 User auth 18 mORMot.TSQLRestRoutingREST(01A06EA0) mORMotUser/181581818
22:58:44 Enter 18 SynDBFireDAC.TSQLDBFireDACStatement(04B84880).00876981
22:58:44 SQL 18 SynDBFireDAC.TSQLDBFireDACStatement(04B84880) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=100362
22:58:44 Exception OS 18 EAccessViolation (C0000005) at 00899088 stack trace 009565CD 00956C1C 00928D5C 00916758 0091A2AD 008DC365 008DF5DE 0091C22F 0066914F 008DC798 00668F8B 00876A58 00406FF2 00871334 0097D88B 005C1DBE 005C61A1 005C4F95 00552911 005C4B1A 0040AFD2 005C4AE2 005CA62A 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE
22:58:44 Exception OS 18 EAccessViolation (C0000005) at 00899088 stack trace 0066914F 008DC798 00668F8B 00876A58 00406FF2 00871334 0097D88B 005C1DBE 005C61A1 005C4F95 00552911 005C4B1A 0040AFD2 005C4AE2 005CA62A 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE 75357C04 77A9AD1F 77A9ACEA
22:58:44 Exception OS 18 EAccessViolation (C0000005) at 00899088 stack trace 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE 75357C04 77A9AD1F 77A9ACEA
22:58:44 Leave 18 00.002.419
22:58:44 Debug 18 mORMotSQLite3.TSQLRestServerDB(0120DC20) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EAccessViolation":{"EAccessViolation":"Zugriffsverletzung bei Adresse 00899088 in Modul 'myservice.Service.exe'. Lesen von Adresse 00000024"}} }
22:58:44 Server 18 mORMotSQLite3.TSQLRestServerDB(0120DC20) mORMotUser 194.9.125.254 GET myservice/ ORM-Get -> 500 with outlen=183 in 2593 us
22:58:44 Leave 18 00.002.598
22:58:44 Enter 22 mORMotSQLite3.TSQLRestServerDB(0120DC20).URI(GET myservice?session_signature=0AD2B7F500040616AD5BD2EF inlen=89)
22:58:44 User auth 22 mORMot.TSQLRestRoutingREST(01A04AE0) mORMotUser/181581813
22:58:44 Enter 22 SynDBFireDAC.TSQLDBFireDACStatement(04B84880).00876981
22:58:44 SQL 22 SynDBFireDAC.TSQLDBFireDACStatement(04B84880) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=122505
22:58:44 Exception OS 22 EAccessViolation (C0000005) at 00899088 stack trace 009565CD 00956C1C 00928D5C 00916758 0091A2AD 008DC365 008DF5DE 0091C22F 0066914F 008DC798 00668F8B 00876A58 00406FF2 00871334 0097D88B 005C1DBE 005C61A1 005C4F95 00552911 005C4B1A 0040AFD2 005C4AE2 005CA62A 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE
22:58:44 Exception OS 22 EAccessViolation (C0000005) at 00899088 stack trace 0066914F 008DC798 00668F8B 00876A58 00406FF2 00871334 0097D88B 005C1DBE 005C61A1 005C4F95 00552911 005C4B1A 0040AFD2 005C4AE2 005CA62A 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE 75357C04 77A9AD1F 77A9ACEA
22:58:44 Exception OS 22 EAccessViolation (C0000005) at 00899088 stack trace 0040B7B4 777FF9D9 006101D4 005FFECF 006049E9 004C9014 0040B6BE 75357C04 77A9AD1F 77A9ACEA
22:58:44 Leave 22 00.002.399
22:58:44 Debug 22 mORMotSQLite3.TSQLRestServerDB(0120DC20) TSQLRestRoutingREST.Error: { "errorCode":500, "error": {"EAccessViolation":{"EAccessViolation":"Zugriffsverletzung bei Adresse 00899088 in Modul 'myservice.Service.exe'. Lesen von Adresse 00000024"}} }What can i do now ? Any Idea ? ATM i restart the service every 60min
Is it possible to restart / reconnect the DB if such error occurs ?
Rad Studio 12.1 Santorini
Offline
#2 2015-12-02 06:37:49
- AOG
- Member
- Registered: 2014-02-24
- Posts: 490
Re: Unpredictable DB Failures
I had the same problem. Slow or unreliable MySQL or whatever, causing connection problems.
All problem gone with:
try
Traw:=Self.Props.ExecuteInlined(aSQL,True).FetchAllAsJSON(Ctxt.ClientKind<>ckFramework);
except
on E: Exception do
//ZDbcIntfs:EZSQLException
begin
TSQLLog.Add.Log(sllError,'Re-connecting because of: '+E.Message);
Self.Props.MainConnection.Disconnect;
Self.Props.MainConnection.Connect;
Traw:=Self.Props.ExecuteInlined(aSQL,True).FetchAllAsJSON(Ctxt.ClientKind<>ckFramework);
end else Raise;
end; Last edited by AOG (2015-12-02 06:42:15)
Offline
#3 2015-12-02 07:57:12
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
ty AOG,
i had the same thoughts to implement a "Reconnect on Exception" cause my primary goal is to let my service run.
But where should i implement this in the REST - Calls executed from the remote clients.
As first step i wrote a litte SOA interface for the massively called remote request.
It would ever be a workaround. The Problem lies somewhere in the deep...
I think that under some special case some of the database - memory conflicts with two or more thread.
Rad Studio 12.1 Santorini
Offline
#4 2015-12-02 08:17:11
- danielkuettner
- Member
- From: Germany
- Registered: 2014-08-06
- Posts: 330
Re: Unpredictable DB Failures
Like EgonHiergeist,mpv and I said, your problem is the long exec time of your queries.
If your clients execute 100-200 queries (that's a lot), every query have to execute in a minimal time.
But your query last much longer.
In mysql you have several timeouts:
- interactive_timeout : interactive time out for mysql shell sessions in seconds like mysqldump or mysql command line tools.
- wait_timeout” : the amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection in seconds.
- connect_timeout, net_read_timeout and net_write_timeout would: help to skip the timeout errors when lengthy queries are being executed.
In FireDac or Zeos you have to set higher values for connect_timeout.
You can test this simple with this query: "SELECT SLEEP(120)".
But better you optimize your queries and check, why the exec time is so long.
Last edited by danielkuettner (2015-12-02 08:18:23)
Offline
#5 2015-12-02 18:08:33
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
@Daniel - i testet it my query take about max 0.5 ms as you can also see in the log - i think that is not the Problem and should not be the problem
@AB - I have a new log from this evening where you can see to concurrent thread doing the same and causing a new exception (no time out )
Everything fine :
17:18:40 Enter 13 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257A0007F510B069B51F inlen=89)
17:18:40 User auth 13 mORMot.TSQLRestRoutingREST(01A25E20) mORMotUser/246162810
17:18:40 Enter 13 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380).00876981
17:18:40 SQL 13 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=113203
>> QUERY Time 00.000.518
17:18:40 Leave 13 00.000.518
17:18:40 Server 13 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 200 with outlen=73 in 584 us
17:18:40 Leave 13 00.000.591
17:18:40 Enter 6 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257B004B124CC7ABE8D3 inlen=88)
17:18:40 User auth 6 mORMot.TSQLRestRoutingREST(01A23D20) mORMotUser/246162811
17:18:40 Enter 6 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380).00876981
17:18:40 SQL 6 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=58987
17:18:40 Leave 6 00.000.496
17:18:40 Server 6 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 194.9.125.254 GET myservice/ ORM-Get -> 200 with outlen=73 in 585 us
17:18:40 Leave 6 00.000.592
17:18:40 Enter 23 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257A0007F510B069B51F inlen=89)
17:18:40 User auth 23 mORMot.TSQLRestRoutingREST(01A26920) mORMotUser/246162810
17:18:40 Enter 23 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380).00876981
17:18:40 SQL 23 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=114205
17:18:40 Leave 23 00.000.527
17:18:40 Server 23 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 200 with outlen=73 in 602 us
17:18:40 Leave 23 00.000.609
till here Thread 16 and 27 doing the same...
17:18:41 Enter 16 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257B004B124D5CAAE7C9 inlen=88)
17:18:41 Enter 27 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257A0007F51109926EF7 inlen=89)
17:18:41 User auth 16 mORMot.TSQLRestRoutingREST(01A284A0) mORMotUser/246162811
17:18:41 User auth 27 mORMot.TSQLRestRoutingREST(01A24F00) mORMotUser/246162810
17:18:41 Enter 27 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380).00876981
17:18:41 SQL 27 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=114267
>> EXCEPTION Starting (Invalid Pointer)
17:18:41 Exception 16 EInvalidPointer ("Ungültige Zeigeroperation") at 0040FD5C stack trace API 00551092 0040AD98
17:18:41 Debug 16 mORMotSQLite3.TSQLRestServerDB(0113DC20) TSQLRestRoutingREST.Error: { "errorCode":400, "errorText":"Bad Request" }
17:18:41 Server 16 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 194.9.125.254 GET myservice/ ORM-Get -> 400 with outlen=49 in 718 us
17:18:41 Leave 16 00.000.726
17:18:41 Leave 27 00.000.833
17:18:41 Server 27 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 200 with outlen=73 in 972 us
17:18:41 Leave 27 00.000.979
17:18:41 Enter 21 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257A0007F51109926EF7 inlen=89)
17:18:41 User auth 21 mORMot.TSQLRestRoutingREST(01A25320) mORMotUser/246162810
>> EXCEPTION Continueing (Invalid Pointer)
17:18:41 Exception 21 EInvalidPointer ("Ungültige Zeigeroperation") at 008E0A2E stack trace API 00551092 0040AD98
17:18:41 Debug 21 mORMotSQLite3.TSQLRestServerDB(0113DC20) TSQLRestRoutingREST.Error: { "errorCode":400, "errorText":"Bad Request" }
17:18:41 Server 21 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 400 with outlen=49 in 553 us
17:18:41 Leave 21 00.000.560
17:18:41 Enter 17 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257B004B124D5CAAE7C9 inlen=88)
17:18:41 User auth 17 mORMot.TSQLRestRoutingREST(01A28760) mORMotUser/246162811
17:18:41 Enter 17 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380).00876981
17:18:41 SQL 17 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=84347
17:18:41 Leave 17 00.000.595
17:18:41 Server 17 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 194.9.125.254 GET myservice/ ORM-Get -> 200 with outlen=68 in 653 us
17:18:41 Leave 17 00.000.661
17:18:41 Enter 22 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257A0007F51109926EF7 inlen=89)
17:18:41 User auth 22 mORMot.TSQLRestRoutingREST(01A23E80) mORMotUser/246162810
>> EXCEPTION Continueing (Invalid Pointer)
17:18:41 Exception 22 EInvalidPointer ("Ungültige Zeigeroperation") at 008E0A2E stack trace API 00551092 0040AD98
17:18:41 Debug 22 mORMotSQLite3.TSQLRestServerDB(0113DC20) TSQLRestRoutingREST.Error: { "errorCode":400, "errorText":"Bad Request" }
17:18:41 Server 22 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 212.23.144.2 GET myservice/ ORM-Get -> 400 with outlen=49 in 393 us
17:18:41 Leave 22 00.000.400
17:18:41 Enter 28 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257B004B124D5CAAE7C9 inlen=88)
17:18:41 User auth 28 mORMot.TSQLRestRoutingREST(01A25A00) mORMotUser/246162811
17:18:41 Enter 28 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380).00876981
17:18:41 SQL 28 SynDBFireDAC.TSQLDBFireDACStatement(04B3F380) select Zaehlwerk,Zaehlerstand from DFZWRecord where myserviceID=84350
17:18:41 Leave 28 00.000.059
17:18:41 Server 28 mORMotSQLite3.TSQLRestServerDB(0113DC20) mORMotUser 194.9.125.254 GET myservice/ ORM-Get -> 200 with outlen=68 in 86 us
17:18:41 Leave 28 00.000.091
17:18:41 Enter 34 mORMotSQLite3.TSQLRestServerDB(0113DC20).URI(GET myservice?session_signature=0EAC257A0007F51109926EF7 inlen=89)After restart of service everything is fine again...
Rad Studio 12.1 Santorini
Offline
#6 2015-12-02 20:09:49
- danielkuettner
- Member
- From: Germany
- Registered: 2014-08-06
- Posts: 330
Re: Unpredictable DB Failures
Although your query exec time is very quick, we wasn't so far with our guess, I think.
The error is: "SQL Error: Lost connection to MySQL server during query"
Please read this: http://dev.mysql.com/doc/refman/5.7/en/ … ction.html
The recommendation sounds like ours.
If your connection crashes, although the exec time is not too long, than you have perhaps simpler problems (network e.g., ip-address conflicts...).
Have you already try my little test? You could use 1 thread in a loop over a time (then you haven't a multithreading issue if it also crashes). When not you can test it with several threads in a loop an so on.
Offline
#7 2015-12-03 10:58:47
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
I made a few changes to the Code - it works - but is not finished.
SynDBZeos.pas line 1065 ff
if fExpectResults then begin
fColumnCount := 0;
fColumn.ReHash;
fCurrentRow := -1;
try
fResultSet := fStatement.ExecuteQueryPrepared;
except
on e : Exception do begin
// Try ReConnect - by itSDS
Log.Log(sllError,'Re-connecting because of: '+E.Message);
Props := fConnection.Properties as TSQLDBZEOSConnectionProperties;
Props.MainConnection.DisConnect;
Props.MainConnection.Connect;
raise;
// itSDS Does not execute right Query fResultSet := fStatement.ExecuteQueryPrepared;
end;
end;My Idea was to adapt the code Snippet from AOG. The Positive: the Service continues running and the Connection is restored !
The actual Problem:
I tried to reexecute the Query which caused the failure but fStatement contains a new query generated in Connect .....
so my second choice is to raise the error causing a error 500 on the Connection.
@Daniel as i stated already the Server "is gone away" is not the real and only error and the link you posted was already posted by egonhugeist - i read it and it does really not help !
Rad Studio 12.1 Santorini
Offline
#8 2015-12-03 11:20:42
- danielkuettner
- Member
- From: Germany
- Registered: 2014-08-06
- Posts: 330
Re: Unpredictable DB Failures
@itSDS
What you now try is to fight against a symptom, but you should better find out the reason for your connection aborts.
If you want a reconnect there is already an option in mORMot: TSQLDBConnectionProperties.ConnectionTimeOutMinutes.
(Don't forget, everybody here only want to help you)
Btw, what does the mysql error log say?
Have you already read this?
http://dev.mysql.com/doc/refman/5.7/en/ … rrors.html
Last edited by danielkuettner (2015-12-03 11:34:58)
Offline
#9 2015-12-03 15:50:49
- AOG
- Member
- Registered: 2014-02-24
- Posts: 490
Re: Unpredictable DB Failures
As stated before (Zeos, MySQL):
I had exactly the same connection problem.
All time-out settings stretching for hours.
All reconnect settings enabled.
Queries within (m)seconds.
And still: "SQL Error: Lost connection to MySQL server during query".
None of this: http://dev.mysql.com/doc/refman/5.7/en/ … ction.html
applicable in my situation.
Only work-around: disconnect / connect in case of exception.
Offline
#10 2015-12-03 16:31:59
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
Just to make it clear again this Thread's name is not "MySQL Connection has gone" it is about Exceptions that arise if 2 or more REST Clients make concurrent Selects if Database is MySQL.
This Exception arise with FireDAC, ZEOS, UniDAC, .. others not testet.
The Client just use Connect and TSQLRecord.CreateAndFillPrepare, followes by FillOnes.
The Tables in Database are created by mORMot - i just added some additional Indexes
There are 2 Tables triggered the First gets only one Select then the Second is selected for an amount of approx. 100 trivial selects (Select 2 Fields from table where id (indexed) = <number>)
The Select is generated by mORMot CreateAndFillPrepare.
Obviously and cause of the different Exception i think that here is a Problem with 2 Threat colliding.
One Exeption was that a tablename was double in the Query - The Statement was generated by mORMot !!
Rad Studio 12.1 Santorini
Offline
#11 2015-12-03 18:44:12
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
I have one idea which may be worth looking for:
Before using mORMot i wrote a webserver which also managed a lot of pages. As Connection i used a TUniConnection (cause i normally use UniDAC)
For every Thread i created one TUniConnection - Object, as stated in the UniDAC - Forum / Manual
Re: Multithread Unidac UniConnection
Postby MaximG » Tue 18 Aug 2015 08:29When using UniDAC for creating multithreaded applications, you can use approaches similar to the ones described in the "Multithreaded Delphi Database Queries with dbGo (ADO)" article.
At this, you should take into account, that the correct solution will be to use a separate connection (the TUniConnection component) in each thread. In this case, your application will be thread-safe. You can have infinite amount of queries running through one connection in one thread, but you must not run several queries from several threads through one connection.
I think this could be the case not only for UniDAC, also for FireDAC / ZEOS cause generally the Connection - Object stores shared Data which may not thread Safe.
Rad Studio 12.1 Santorini
Offline
#12 2015-12-03 19:34:53
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
I debugged my thoughts from last post and found the follwing:
TUniConnection / IZConnection is only created once.
But there are more than 1 Thread working parallel
After a little deeper DEBUG i found out that TSQLDBUniDACConnectionProperties(FConnection).ThreadingMode = tmMainConnection
But I never set this Variable to that Value directly, checking the Value after
FConnection := TSQLDBZEOSConnectionProperties.Create(TSQLDBZEOSConnectionProperties.URI(dMySQL,host + ':' + port), databasename, username, password);
it shows that it is tmThreadPool ! As required !
I debugged and found out that activating the TSQLDBServerHttpApi with
if ARemoteDBPort > 0 then
FHttpServer := TSQLDBServerHttpApi.Create(FConnection, FModel.Root, Int32ToUtf8(ARemoteDBPort), 'username', 'Password');changes ThreadingMode to tmMainConnection !!!
@AB is this a bug ? Or can we not use TSQLDBServerHttpApi with MVC/ORM ?
Rad Studio 12.1 Santorini
Offline
#13 2015-12-03 19:48:35
- itSDS
- Member
- From: Germany
- Registered: 2014-04-24
- Posts: 506
Re: Unpredictable DB Failures
i removed all my prior changes in SynDBZEOS and added this to SynDBRemote.pas:
constructor TSQLDBServerAbstract.Create(aProperties: TSQLDBConnectionProperties;
const aDatabaseName, aPort, aUserName,aPassword: RawUTF8; aHttps: boolean;
aThreadPoolCount: integer; aProtocol: TSQLDBProxyConnectionProtocolClass);
begin
fProperties := aProperties;
{ itSDS
if fProperties.InheritsFrom(TSQLDBConnectionPropertiesThreadSafe) then
TSQLDBConnectionPropertiesThreadSafe(fProperties).ThreadingMode := tmMainConnection;
}
fDatabaseName := aDatabaseName;
fPort := aPort;
fHttps := aHttps;
fThreadPoolCount := aThreadPoolCount;
if aProtocol=nil then
aProtocol := TSQLDBRemoteConnectionProtocol;
fProtocol := aProtocol.Create(TSynAuthentication.Create(aUserName,aPassword));
end;I see a Variable called ThreadPoolCount here. Can you please check if tmMainConnection is mandatory here or if it also would run with tmThreadPool. In my First test it seems to work with tmThreadPool.
Rad Studio 12.1 Santorini
Offline
Pages: 1