Deadlocks and database connection issues 7.8.3 version

Hey!

I have problem with customers project where connection to database faults every few days. This causes ignition to crash after memory buffer gets full. Ive also been getting some deadlock notifications to console. We have 7.8.3 version running, where taghistory is separated to another MySQL database and then there is local database for rest like sqlt_core (which was table which had deadlock on).

191015 8:59:47
*** (1) TRANSACTION:
TRANSACTION 27ED6F0B9, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 320507, OS thread handle 0x7f95bd82e700, query id 1723937232 localhost 127.0.0.1 cromi Updating
UPDATE sqlt_core SET intvalue=80,floatvalue=null,stringvalue=null,datevalue=null,dataintegrity=192,valuechange=CURRENT_TIMESTAMP WHERE id=41010 AND configchange<=‘2019-10-15 08:59:46’
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 826 n bits 192 index PRIMARY of table cromilocal.sqlt_core trx id 27ED6F0B9 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 27ED6ED3A, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
87 lock struct(s), heap size 14776, 440 row lock(s), undo log entries 439
MySQL thread id 320506, OS thread handle 0x7f95bd3d8700, query id 1723937272 localhost 127.0.0.1 cromi Updating
UPDATE sqlt_core SET intvalue=0,floatvalue=null,stringvalue=null,datevalue=null,dataintegrity=192,valuechange=CURRENT_TIMESTAMP WHERE id=38541 AND configchange<=‘2019-10-15 08:59:26’
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 826 n bits 192 index PRIMARY of table cromilocal.sqlt_core trx id 27ED6ED3A lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 805 n bits 192 index PRIMARY of table cromilocal.sqlt_core trx id 27ED6ED3A lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION

was deadlock log earlier.

Also had this log earlier

9:27:45 [Warning] Aborted connection 119 to db: ‘cromilocal’ user: ‘’ host: ‘localhost’ (Got an error reading communication packets)

root@localhost:information_schema MySQL>SHOW VARIABLES LIKE ‘%max_allowed_packet%‘;±-------------------------±-----------+| Variable_name | Value |±-------------------------±-----------+| max_allowed_packet | 16777216 || slave_max_allowed_packet | 1073741824 |±-------------------------±-----------+

also getting clockdrift detection

Clock drift, degraded performance, or pause-the-world detected. lastTime=Wed Oct 16 09:40:13 EEST 2019 currentTime=Wed Oct 16 09:40:21 EEST 2019 deviation from expected 1000ms delta=6325ms.

Any insight to this would be appreciated

“pause-the-world”. 6.3 seconds. That kind of garbage collector pause breaks lots of things in Ignition. You might have other problems (since you aren’t running the newest available in the 7.8.x series), but GC problems can both hide and exacerbate those others. Search this forum for G1GC, first.

(Note: 7.8 is not an LTS version, so any official support will likely require upgrading to v7.9.12.)