Hi,
var
Connection: IDBConnection;
Mgr: TObjectManager;
LScanndata : TScanndata;
NewScanndata : TScanndata;
begin
Connection := TFireDacMySqlConnection.CreateConnection;
Mgr := TObjectManager.Create(Connection);
NewScanndata:= TScanndata.Create;
try
LScanndata := Mgr.Find<TScanndata>(index);
NewScanndata.Idx := index.ToInteger;
NewScanndata.Ackkops :=1;
NewScanndata.Valid := 3;
LScanndata := Mgr.Merge<TScanndata>(NewScanndata);
Mgr.SaveOrUpdate(LScanndata);
Mgr.Flush; //(LScanndata);
finally
NewScanndata.Free;
if Mgr <> nil then
Mgr.Free;
Connection.Disconnect;
end;
Why does this code snippet not work in multi threaded environment?
It should update LScanndata, a new record is written to Entity scanndata in an other thread some seconds before and now it should be updated with the result from a client operation. If I do like this
NewScanndata.Idx := 100+ index.ToInteger; it works. (I.E it is a new record)
Database : MariaDB 10.0.2 (MySQL)
I have many other write and read from DB that works very well, and I have really start liking Aurelius very much now after just a few weeks of work with it.
I think it have some thing to do with threading, the thread there the new record is created I do
Mgr.Save(LScanndata);
Mgr.Flush; //(LScanndata);
finally
LScanndata.Free;
if Mgr <> nil then
Mgr.Free;
Connection.Disconnect;
and then the thread is terminated.
Best regards
Ake Pettersson
What is the error you get? I don't see any problem with threading in this code - the issues you might have might be that you are dealing with same record in both databases and then you can't be sure which thread modified the record first.
In summary, your code is thread-safe from the pascal/Aurelius point of view - but I'm not sure if it is from the point of view of your business logic.
Note that you might not need some calls there: Flush is not needed after Save, if you have just inserted a single record in the manager.
Also, in first code, I'm not sure you need Merge there. Why not simply retrieving LScanndata, changing its properties, and then calling Flush? You don't need to call SaveOrUpdate in either scenario (your existing code or in my suggestion).
Hi,
I get no error, it is happen nothing, the DB record is just not updated.
This was my first atempt.
Connection := TFireDacMySqlConnection.CreateConnection;
Mgr := TObjectManager.Create(Connection);
NewScanndata:= TScanndata.Create;
try
LScanndata := Mgr.Find<TScanndata>(index);
LScanndata.Ackkops :=1;
LScanndata.Valid := 3;
Mgr.Flush(LScanndata);
finally
NewScanndata.Free;
if Mgr <> nil then
Mgr.Free;
Connection.Disconnect;
end;
And it works in many other threads in my code. But not in this one.
I am very shure that this task is the last to update the entity.
Can it be something with the MariaDB database driver ?
This last code is better, and should work just fine. I think it's safe to check if LScandata is coming nil.
The only situation see the code above (last one) would not update data in the database is if LScandata is nil, or if the properties you are setting are not being modified (you are setting same values already existing in the object).
Also, try to use OnSqlExecuting event to check the SQL statements generated by the managers, this way you have a better idea of what's exactly happening.
Hi,
Do you have any example of how to use OnSqlExecuting event ?
Sorry I forgot to search in your demos, I found it there in MusicLibrary
I have two tasks the first runs a insert of a new record and after that it is update the same record in the same task. (works well) in the second task created as a result form an answer from a client the same record is updated , but nothing happens in the DB, I get a feeling of that the first task is delaying the save so it comes after the second task, how to control that?
SELECT LAST_INSERT_ID();
================================================
SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0
p_0 = "97355" (ftInteger)
================================================
UPDATE scanndata SET
Sent = :A_Sent,
Valid = :A_Valid,
TimeStr = :A_TimeStr
WHERE Idx = :p_3
A_Sent = "1" (ftInteger)
A_Valid = "1" (ftInteger)
A_TimeStr = " 2017-05-03 22:31:38" (ftString)
p_3 = "97355" (ftInteger)
================================================
UPDATE scanndata SET
Json = :A_Json,
SentToKOPS = :A_SentToKOPS
WHERE Idx = :p_2
A_Json = "{"index":"97355","scanned":"2017-05-03 22:31:37","pid":"PORTAL02","mcid":"ID4455", "barcodes" : [ { "barcode" : "00002058"}, { "barcode" : "00004481"}, { "barcode" : "00005671"}, { "barcode" : "00007491"}, { "barcode" : "00007772"}]}" (ftString)
A_SentToKOPS = "2017-05-03 22:31:38" (ftDateTime)
p_2 = "97355" (ftInteger)
================================================
From here in the second thread.
SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0
p_0 = "97355" (ftInteger)
================================================
UPDATE scanndata SET
Valid = :A_Valid,
AckKOPS = :A_AckKOPS
WHERE Idx = :p_2
A_Valid = "3" (ftInteger)
A_AckKOPS = "1" (ftInteger)
p_2 = "97355" (ftInteger)
================================================
From the SQL statements you posted here, it looks everything is ok. The first thread performs two updates, the second thread performs one update.
What is the symptom you see to claim that the second update is not happening?
The database has not the changed values to Valid = 3,AckKOPS = 1 when I look at with HeidiSQL (the db manager for MariaDB)
It seams to be a more general problem, I can't update any table(object) if it not is created by the same ObjectManager, It works as I described earlier because the update is done in the same thread with the same manager. When I do a update from another thread or manager that create the object (record) it fails,I think that the DB detect some error in the sql and don't do the job and the error is not reported back. How to reproduce, setup a table use MariaDB ver 10.0.2 create a record change it with the same Manager it works, change with a new manager and it will fail.
Output from FireDac Monitor running the query above on a MySQL 5.5.56 DB but the result is the same, update of a record is not working
1 12:44:17:276 ***** Client [P: C:\DelphiProjekt\MyTest\Server\Win32\Debug\PPP_1.exe] activated
2 12:44:17:042 . CreateConnection [ConnectionDef="MyTestportal"]
3 12:44:17:058 . CreateTransaction [ConnectionDef="MyTestportal"]
4 12:44:17:058 . Adapter FireDacMySqlConnection_1.Connection registered with client
5 12:44:17:073 >> FireDacMySqlConnection_1.Connection.Open [ConnectionDef="MyTestportal"]
6 12:44:17:073 >> Definition [Name="MyTestportal"]
7 12:44:17:073 . Port=3304
8 12:44:17:073 . Password=*****
9 12:44:17:073 . User_Name=User
10 12:44:17:073 . Database=MyTestportal
11 12:44:17:073 . DriverID=MySQL
12 12:44:17:073 . Server=192.168.1.57
13 12:44:17:073 . Name=MyTestportal
14 12:44:17:073 . MonitorBy=Remote
15 12:44:17:073 . Port=3306
16 12:44:17:073 . ConnectionDef=MyTestportal
17 12:44:17:073 << Definition [Name="MyTestportal"]
18 12:44:17:073 >> FireDAC info
19 12:44:17:089 . Tool=RAD Studio 10.2
20 12:44:17:089 . FireDAC=16.0.0 (Build 88974)
21 12:44:17:089 . Platform=Windows 32 bit
22 12:44:17:089 . Defines=FireDAC_NOLOCALE_META;FireDAC_MONITOR
23 12:44:17:089 << FireDAC info
24 12:44:17:089 . mysql_get_client_info [Ver="6.1.6"]
25 12:44:17:089 . mysql_init
26 12:44:17:089 . mysql_options [option=1, arg=0]
27 12:44:17:105 . mysql_real_connect [host="192.168.1.57", user="User", passwd="***", db="MyTestportal", port=3306, clientflag=198158]
28 12:44:17:120 . mysql_get_server_info [Ver="5.5.56"]
29 12:44:17:120 . mysql_real_query [q="SET SQL_AUTO_IS_NULL = 0#0"]
30 12:44:17:120 . mysql_insert_id [res=0]
31 12:44:17:120 . mysql_real_query [q="SHOW VARIABLES LIKE 'lower_case_table_names'#0"]
32 12:44:17:136 . mysql_store_result
33 12:44:17:136 . mysql_fetch_row [res=$09C50998]
34 12:44:17:136 . mysql_fetch_lengths [res=$09C50998]
35 12:44:17:136 >> Fetched [Row=1]
36 12:44:17:136 . Column [N=0, Len=22, Data='lower_case_table_names']
37 12:44:17:136 . Column [N=1, Len=1, Data='1']
38 12:44:17:136 << Fetched [Row=1]
39 12:44:17:136 . mysql_free_result [res=$09C50998]
40 12:44:17:152 >> Driver log
41 12:44:17:152 . Loading driver MySQL ...
42 12:44:17:152 << Driver log
43 12:44:17:152 . mysql_get_server_info [Ver="5.5.56"]
44 12:44:17:152 . mysql_get_client_info [Ver="6.1.6"]
45 12:44:17:152 . mysql_character_set_name [res="latin1"]
46 12:44:17:152 . mysql_get_host_info [res="192.168.1.57 via TCP/IP"]
47 12:44:17:152 >> Client info
48 12:44:17:152 . DLL=C:\DelphiProjekt\MyTest\Server\Win32\Debug\LibMySQL.DLL
49 12:44:17:152 . Client version=601060000
50 12:44:17:152 << Client info
51 12:44:17:152 . mysql_get_server_info [Ver="5.5.56"]
52 12:44:17:152 . mysql_get_client_info [Ver="6.1.6"]
53 12:44:17:152 . mysql_character_set_name [res="latin1"]
54 12:44:17:152 . mysql_get_host_info [res="192.168.1.57 via TCP/IP"]
55 12:44:17:152 . mysql_get_server_info [Ver="5.5.56"]
56 12:44:17:152 . mysql_get_client_info [Ver="6.1.6"]
57 12:44:17:152 . mysql_character_set_name [res="latin1"]
58 12:44:17:152 . mysql_get_host_info [res="192.168.1.57 via TCP/IP"]
59 12:44:17:152 >> Session info
60 12:44:17:152 . Current catalog=
61 12:44:17:152 . Current schema=
62 12:44:17:152 . Server info=5.5.56
63 12:44:17:152 . Client info=6.1.6
64 12:44:17:152 . Characterset name=latin1
65 12:44:17:152 . Host info=192.168.1.57 via TCP/IP
66 12:44:17:152 . Name modes=CILCTD
67 12:44:17:152 . SSL Cipher=
68 12:44:17:152 << Session info
69 12:44:17:152 << Open [ConnectionDef="MyTestportal"]
70 12:44:17:152 . CreateCommand [ConnectionDef="MyTestportal"]
71 12:44:17:152 . Adapter TFDQuery($09AE7430) registered with client
72 12:44:17:152 >> TFDQuery($09AE7430).Prepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
73 12:44:17:183 . Preprocessed [CMD="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = ?", FROM="scanndata", VP=0, VPE=0, OBP=0, CK=1]
74 12:44:17:183 . mysql_stmt_init [mysql=$09C46540, Result=$00000000]
75 12:44:17:183 . mysql_stmt_prepare [stmt=$09C4E998, q="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = ?"]
76 12:44:17:214 . mysql_stmt_param_count [stmt=$09C4E998, Result=1]
77 12:44:17:214 . mysql_stmt_field_count [stmt=$09C4E998, Result=14]
78 12:44:17:214 . mysql_stmt_attr_set [stmt=$09C4E998, option=0, arg=0]
79 12:44:17:214 . mysql_stmt_attr_set [stmt=$09C4E998, option=2, arg=50]
80 12:44:17:214 . mysql_stmt_bind_param [stmt=$09C4E998, count=1]
81 12:44:17:214 << Prepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
82 12:44:17:214 << TFDCustomCommand.Prepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
83 12:44:17:214 >> Define(TFDDatSManager) [ADatSManager="Manager", ATable="<nil>", Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
84 12:44:17:230 . CreateMetaInfoCommand [ConnectionDef="MyTestportal"]
85 12:44:17:230 . Adapter FireDacMySqlConnection_1.Connection.TFDPhysMySQLCommand($09E1EEA0) registered with client
86 12:44:17:230 >> FireDacMySqlConnection_1.Connection.TFDPhysMySQLCommand($09E1EEA0).Prepare [Command="Table PKey Fields (scanndata.scanndata)"]
87 12:44:17:230 . Adapter FireDacMySqlConnection_1.Connection.TFDPhysMySQLCommand($09E1EEA0) unregistered with client
88 12:44:17:230 << Prepare [Command="Table PKey Fields (scanndata.scanndata)"]
89 12:44:17:230 >> Open [Command="Table PKey Fields (scanndata.scanndata)"]
90 12:44:17:230 . mysql_real_query [q="SHOW INDEX FROM `MyTestportal`.`scanndata`#0"]
91 12:44:17:245 . mysql_store_result
92 12:44:17:245 . mysql_more_results
93 12:44:17:245 . mysql_num_fields [res=$09C52D08]
94 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=0]
95 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=1]
96 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=2]
97 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=3]
98 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=4]
99 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=5]
100 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=6]
101 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=7]
102 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=8]
103 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=9]
104 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=10]
105 12:44:17:245 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=11]
106 12:44:17:261 . mysql_fetch_field_direct [res=$09C52D08, fieldnr=12]
107 12:44:17:261 << Open [Command="Table PKey Fields (scanndata.scanndata)"]
108 12:44:17:261 >> Fetch [ATable="Table", Command="Table PKey Fields (scanndata.scanndata)"]
109 12:44:17:261 . mysql_fetch_row [res=$09C52D08]
110 12:44:17:261 . mysql_fetch_lengths [res=$09C52D08]
111 12:44:17:261 >> FireDacMySqlConnection_1.Connection.Fetched [Row=1]
112 12:44:17:261 . Column [N=0, Len=9, Data='scanndata']
113 12:44:17:261 . Column [N=1, Len=1, Data='0']
114 12:44:17:261 . Column [N=2, Len=7, Data='PRIMARY']
115 12:44:17:261 . Column [N=3, Len=1, Data='1']
116 12:44:17:261 . Column [N=4, Len=3, Data='Idx']
117 12:44:17:261 . Column [N=5, Len=1, Data='A']
118 12:44:17:261 . Column [N=6, Len=1, Data='0']
119 12:44:17:261 . Column [N=7, Len=0, Data=NULL]
120 12:44:17:261 . Column [N=8, Len=0, Data=NULL]
121 12:44:17:261 . Column [N=9, Len=0, Data='']
122 12:44:17:261 . Column [N=10, Len=5, Data='BTREE']
123 12:44:17:261 . Column [N=11, Len=0, Data='']
124 12:44:17:261 . Column [N=12, Len=0, Data='']
125 12:44:17:261 << Fetched [Row=1]
126 12:44:17:261 . mysql_fetch_row [res=$09C52D08]
127 12:44:17:261 . EOF
128 12:44:17:261 << Fetch [ATable="Table", Command="Table PKey Fields (scanndata.scanndata)", RowsAffected=1]
129 12:44:17:261 . Eof reached [ATable="Table", Command="Table PKey Fields (scanndata.scanndata)"]
130 12:44:17:261 >> Close [Command="Table PKey Fields (scanndata.scanndata)"]
131 12:44:17:261 . mysql_more_results
132 12:44:17:261 . mysql_free_result [res=$09C52D08]
133 12:44:17:261 << Close [Command="Table PKey Fields (scanndata.scanndata)"]
134 12:44:17:261 >> Unprepare [Command="Table PKey Fields (scanndata.scanndata)"]
135 12:44:17:261 . mysql_more_results
136 12:44:17:261 << Unprepare [Command="Table PKey Fields (scanndata.scanndata)"]
137 12:44:17:261 . Destroy [Command="Table PKey Fields (scanndata.scanndata)"]
138 12:44:17:261 >> TFDQuery($09AE7430).Open [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
139 12:44:17:261 . mysql_stmt_execute [stmt=$09C4E998]
140 12:44:17:277 . mysql_stmt_field_count [stmt=$09C4E998, Result=14]
141 12:44:17:277 . mysql_stmt_store_result [stmt=$09C4E998]
142 12:44:17:277 . mysql_stmt_result_metadata [stmt=$09C4E998]
143 12:44:17:277 . mysql_more_results
144 12:44:17:277 . mysql_num_fields [res=$09C52D28]
145 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=0]
146 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=1]
147 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=2]
148 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=3]
149 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=4]
150 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=5]
151 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=6]
152 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=7]
153 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=8]
154 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=9]
155 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=10]
156 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=11]
157 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=12]
158 12:44:17:277 . mysql_fetch_field_direct [res=$09C52D28, fieldnr=13]
159 12:44:17:277 . mysql_stmt_bind_result [stmt=$09C4E998, count=14]
160 12:44:17:277 << Open [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
161 12:44:17:277 . Col add [Index=1, SrcName="A_Idx", SrcType=Int32, SrcSize=0, SrcPrec=11, SrcScale=0, Type=Int32, Size=0, Prec=11, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="Idx"]
162 12:44:17:277 . Col add [Index=2, SrcName="A_ScannTime", SrcType=DateTimeStamp, SrcSize=0, SrcPrec=0, SrcScale=0, Type=DateTimeStamp, Size=0, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="ScannTime"]
163 12:44:17:277 . Col add [Index=3, SrcName="A_PID", SrcType=AnsiString, SrcSize=30, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=30, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="PID"]
164 12:44:17:277 . Col add [Index=4, SrcName="A_MCID", SrcType=AnsiString, SrcSize=30, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=30, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="MCID"]
165 12:44:17:277 . Col add [Index=5, SrcName="A_Sent", SrcType=Int32, SrcSize=0, SrcPrec=11, SrcScale=0, Type=Int32, Size=0, Prec=11, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="Sent"]
166 12:44:17:277 . Col add [Index=6, SrcName="A_Valid", SrcType=Int32, SrcSize=0, SrcPrec=11, SrcScale=0, Type=Int32, Size=0, Prec=11, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="Valid"]
167 12:44:17:277 . Col add [Index=7, SrcName="A_RawStr", SrcType=AnsiString, SrcSize=300, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=300, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="RawStr"]
168 12:44:17:277 . Col add [Index=8, SrcName="A_ITwoFive", SrcType=AnsiString, SrcSize=300, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=300, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="ITwoFive"]
169 12:44:17:277 . Col add [Index=9, SrcName="A_LoadOrderNr", SrcType=AnsiString, SrcSize=30, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=30, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="LoadOrderNr"]
170 12:44:17:277 . Col add [Index=10, SrcName="A_TimeStr", SrcType=AnsiString, SrcSize=300, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=300, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="TimeStr"]
171 12:44:17:277 . Col add [Index=11, SrcName="A_Json", SrcType=AnsiString, SrcSize=400, SrcPrec=0, SrcScale=0, Type=AnsiString, Size=400, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="Json"]
172 12:44:17:277 . Col add [Index=12, SrcName="A_AckKOPS", SrcType=Int32, SrcSize=0, SrcPrec=11, SrcScale=0, Type=Int32, Size=0, Prec=11, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="AckKOPS"]
173 12:44:17:277 . Col add [Index=13, SrcName="A_SentToKOPS", SrcType=DateTime, SrcSize=0, SrcPrec=0, SrcScale=0, Type=DateTime, Size=0, Prec=0, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="SentToKOPS"]
174 12:44:17:277 . Col add [Index=14, SrcName="A_ReTry", SrcType=Int32, SrcSize=0, SrcPrec=11, SrcScale=0, Type=Int32, Size=0, Prec=11, Scale=0, OrigTabName="MyTestportal.scanndata", OrigColName="ReTry"]
175 12:44:17:277 . Primary key [Cols="Idx"]
176 12:44:17:277 << Define(TFDDatSManager) [ADatSManager="Manager", ATable="<nil>", Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
177 12:44:17:292 >> TFDCustomCommand.Fetch [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0", AAll=False, ABlocked=True]
178 12:44:17:292 >> Fetch [ATable="scanndata", Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
179 12:44:17:292 . mysql_stmt_fetch [stmt=$09C4E998, Result=100]
180 12:44:17:292 << Fetch [ATable="scanndata", Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0", RowsAffected=0]
181 12:44:17:292 . Eof reached [ATable="scanndata", Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
182 12:44:17:292 >> Close [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
183 12:44:17:292 . mysql_free_result [res=$09C52D28]
184 12:44:17:292 << FireDacMySqlConnection_1.Connection.Close [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
185 12:44:17:292 . TFDCustomCommand.FetchFinished [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0", AState=2, FRowsAffected=0]
186 12:44:17:292 << TFDCustomCommand.Fetch [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0", AAll=False, ABlocked=True]
187 12:44:17:292 >> TFDCustomCommand.Unprepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
188 12:44:17:292 >> TFDQuery($09AE7430).Unprepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
189 12:44:17:292 . mysql_stmt_close [stmt=$09C4E998]
190 12:44:17:292 << Unprepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
191 12:44:17:292 . Destroy [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
192 12:44:17:292 . Adapter TFDQuery($09AE7430) unregistered with client
193 12:44:17:292 << TFDCustomCommand.Unprepare [Command="SELECT A.Idx AS A_Idx, A.ScannTime AS A_ScannTime, A.PID AS A_PID, A.MCID AS A_MCID, A.Sent AS A_Sent, A.Valid AS A_Valid, A.RawStr AS A_RawStr, A.ITwoFive AS A_ITwoFive, A.LoadOrderNr AS A_LoadOrderNr, A.TimeStr AS A_TimeStr, A.Json AS A_Json, A.AckKOPS AS A_AckKOPS, A.SentToKOPS AS A_SentToKOPS, A.ReTry AS A_ReTry
FROM scanndata A
WHERE A.Idx = :p_0"]
194 12:44:17:292 . Adapter FireDacMySqlConnection_1.Connection.TFDPhysMySQLTransaction($09E1E780) registered with client
195 12:44:17:292 >> FireDacMySqlConnection_1.Connection.StartTransaction [ConnectionDef="MyTestportal"]
196 12:44:17:292 . mysql_real_query [q="BEGIN#0"]
197 12:44:17:308 . mysql_insert_id [res=0]
198 12:44:17:308 << StartTransaction [ConnectionDef="MyTestportal"]
199 12:44:17:308 >> Commit [ConnectionDef="MyTestportal", Retaining=False]
200 12:44:17:308 . mysql_real_query [q="COMMIT#0"]
201 12:44:17:308 . mysql_insert_id [res=0]
202 12:44:17:308 << Commit [ConnectionDef="MyTestportal", Retaining=False]
203 12:44:17:308 >> Close [ConnectionDef="MyTestportal"]
204 12:44:17:308 . mysql_close
205 12:44:17:323 << Close [ConnectionDef="MyTestportal"]
206 06:24:00:652 . Adapter FireDacMySqlConnection_1.Connection unregistered with client
207 06:24:00:652 . Adapter FireDacMySqlConnection_1.Connection.TFDPhysMySQLTransaction($09E1E780) unregistered with client
208 12:44:19:292 ***** Client [P: C:\DelphiProjekt\MyTest\Server\Win32\Debug\PPP_1.exe] disconnected
You are creating a new connection in each thread. Isn't this has to do with transaction isolation? When you say a more general problem you mean when using threads, right? When two different threads, using two different connections, in two different transactions, are trying to update the same data. Then of course it depends on some FireDac configuration to determine if one transaction will be able to see data modified by the other. If the commands are done sequentially, even if using different managers, it should be no problem at all.
You will have to synchronize your threads. Also, try to use the same connection in both threads, at least they will be under the same transaction.
It is worse than that! I think that this has nothing to do with multi threading on mysql /mariaDB. The Updating is not working, if you do update with another manager/connection than that create the record it will fail. My logs above from today is single thread, even if I do an update on any of may tables it does not work, but I can create new records so therefore I have not seen any problem until I start doing update of existing records. But if the record is created and later updated with the same manger/connection it works, that was why I thought i was related to problems with multi-threading but it is not, t is a general failure in Aurelius for MySql/MariaDB, the lastest test I did was with MySQL 5.5.56 to be sure it not is a problem related just to MariaDB It it easy to replicate the problem: setup a table use MariaDB /MySql create a record change it with the same Manager/connection it works, change with a new manager/connection and it will fail.
Could you please reduce that test application that reproduces the problem to a very minimal one and send it to us through e-mail? We will investigate what's going on.