Issue with update operation in the database table
I am facing an issue with update operation in the database table. Sometimes update query is not getting success and the MySql library (MySQL_MariaDB_Generic) on the gateway side is showing the error “MySQL_Packet::read_packet: ERROR: Timeout waiting for client".
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 Wi-Fi module.
From my gateway application, I am inserting the data in 3 steps for 3 data fields as data size is large (around 10k). First I am inserting a record and then adding more data through the update query. The Insert query is successful every time but the update query fails sometimes even though I retry 10 times continuously.
The issue is not happening always. it is happening randomly. Sometimes it is working fine for around 7 to 8 hours before it starts misbehaving again.
It seems like the server is not responding. For debugging purpose I checked the process list on the Database server by executing the query “show processlist;" and I found that there are multiple processes for the same query with state “updating”. It could be due to multiple retries from the gateway. I am not able to follow that whether it is expected behavior (multiple same queries with state “updating”) or not.
We would like to understand why this is happening only sometimes and not always, and how does it recover? Are there any contributing factors from the server-side for this behavior? Are there any configurations that we need to do on our side? Any guidance is appreciated on this.
For your information:
I have used the MySQL_MariaDB_Generic library on the ESP32 Wi-Fi 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 data fields 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 and 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.