SQL errors on .139 update for Owntracks

⚙️ Custom Apps and Drivers
1

C8 2.3.8.139
I had these errors on .128 and they disappeared on .129 but now they are back on .139.
Is this a DB isssue or Owntracks sending a malformed Delete request?
Only happens once or twice a day. Different times on 2 devices but the same app.
Some seem to indicate it might be an HE SQL issue.

dev:21422024-04-15 06:08:07.539errorjava.lang.RuntimeException: java.sql.SQLException: Referential integrity constraint violation: "FK_DCDS_TO_DEVICESTATE: PUBLIC.DEVICE_CURRENT_DEVICE_STATE FOREIGN KEY(DEVICE_STATE_ID) REFERENCES PUBLIC.DEVICE_STATE(ID) (CAST(25995599 AS BIGINT))"; SQL statement: DELETE FROM device_state WHERE device_id = ? AND attribute_name = ? [23503-224] Query: DELETE FROM device_state WHERE device_id = ? AND attribute_name = ? Parameters: [2142, battery] on line 253 (method generatePresenceEvent)

dev:21382024-04-15 01:52:37.221errorjava.lang.RuntimeException: java.sql.SQLException: Referential integrity constraint violation: "FK_DCDS_TO_DEVICESTATE: PUBLIC.DEVICE_CURRENT_DEVICE_STATE FOREIGN KEY(DEVICE_STATE_ID) REFERENCES PUBLIC.DEVICE_STATE(ID) (CAST(25993608 AS BIGINT))"; SQL statement: DELETE FROM device_state WHERE device_id = ? AND attribute_name = ? [23503-224] Query: DELETE FROM device_state WHERE device_id = ? AND attribute_name = ? Parameters: [2138, since] on line 252 (method generatePresenceEvent)

2

Any sql error is hub related not caused by apps or drivers since they don’t direct access to the database. They use an API. I recommend you perform a soft reset. Download a backup and restore it.

2 Likes
3

You can also just do a reboot > check advanced > check rebuild database.
This does the same thing and is much easier (and less scary) to do.

4

Good suggestion though last time I tried it on my hubs it didn’t work. I’ve seen other reports of same issue in the community as well. Will admit I haven’t tried it in a while though.

5

True, there is no way to know for sure if it worked or not.
Another option is also to just download a backup and then restore it right away. This also does a soft reset as part of the restore process. The soft reset method is the only way where you can 100% confirm the database was cleared though, because when you reboot it will give you the welcome screens.

6

I'll give a Soft reset a try. Not a big deal just strange one app throws that error sometimes.

7

Full agree with this being a hub specific issue, for context, the area that is causing this is doing a bunch of device.deleteCurrentState('xxx') type calls in sequence to remove states (about 20 different states). In previous testing, HE handles this without issue.

Thoughts on that?

8

Yes, I think it's SQL. I used to use SQL in my "old" programmer days.
Just strange it's only OT that pulls the errors.