update query is getting failed sometime randomly

[Ramesh Damor]

Hello,

I am facing the issue of update the data in the database table. The issue is not coming always.it comes randomly. Sometimes it is working ok(around 7 to 8 hours).

Sometimes update query is starting to give an error.

I am using the AWS RDS server for the MYSQL database. MYSQL version is 8.0.20. And the query is executed from the ESP32 WiFi module.

To save memory, I am inserting the data in 3 steps as data size is large (around 10k) for 3 data fields

First I am inserting the record and then update it using the update query.

Insert query is executed successfully every time but the update query is getting failed sometimes continuously 10 times retry of it.


For your information:

I have used the MySQL_MariaDB_Generic library on the ESP32 Wifi module to send data to the database.

Database table contains 7 data fields like Id,Timestamp,P1,P2,P3,P4,and P5. Here Id is the primary key and Autoincrement attribute.

Here datatypes for the data fields P3, P4, and P5 are MEDIUMBLOB.

query to insert datafields Timestamp, P1, P2, P3: INSERT INTO TabelXYZ(Timestamp, P1, P2, P3)VALUES ('TimestampValue','P1Value','P2Value','P3Value');

query to insert data field P4: UPDATE TabelXYZ SET P4 = 'P4Value' WHERE Timestamp = 'TimestampValue' ORDER BY Timestamp

query to insert data field P4: UPDATE TabelXYZ SET P5 = 'P5Value' WHERE Timestamp = 'TimestampValue' ORDER BY Timestamp

Please note that there is no internet connectivity issue here as the insert query is executing OK every time. an only the update query is getting failed.

I am not able to find any root cause behind it.

Let me know if anyone has suggestions/idea about what I am doing wrong.