Restarting deCONZ can cause data (network map) corruption

[kulmegil]

Describe the bug

Restarting deconz.exe app can cause network map to corrupt. All node positions are lost and deconz will start re-adding every node in random position as if they were just paired. No device related issues were observed thou.

Steps to reproduce the behavior

1. Close deconz.exe
2. Start deconz.exe immidietly

Expected behavior

No data corruption? Network map should look exactly like it was when app was closed.

Screenshots

https://user-images.githubusercontent.com/570575/213864042-67b703c2-c596-4bfb-b70b-52658988db1a.mp4

Environment

• Host system: mini PC
• Running method: Windows 10 22H2
• Firmware version: 26780700
• deCONZ version: v2.20.1
• Device: ConBee II
• Do you use an USB extension cable: yes
• Is there any other USB or serial devices connected to the host system? keyboard/mouse for local control

deCONZ Logs

12:02:27:012 sqlite3_exec SELECT uuid, route_order, hops FROM source_routes, error: database is locked, line: 836
12:02:27:940 UPNP socket not bound, state: 0

Additional context

[BabaIsYou]

Perhapss a locked database state when reading the information.

Don't know why but as I begin to "play" with zll.db for searching about other issues, and perhaps developping a simple db test script, I find it's locked very often, even just for reading.

Does someone already tried to put it in WAL mode ? Or is it that way by default ? Just tried a few minutes ago on my db by enabling wal mode ...

$ sqlite3 zll.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode=WAL;
wal
sqlite> 

wait and see if it's worse.

[BabaIsYou]

Since modifying the commit/rollback to my zll.db, I didn't have any "database is locked" error, even thru sqlbrowser or my 2 times /day db check external script. And no other side effect in usual use of DeCONZ.

@kulmegil did you still have trouble ?

BTW to get back to genuine format it's also easy :

$ sqlite3 zll.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode=DELETE;
wal
sqlite> .quit

[kulmegil]

@BabaIsYou Sorry, I was a bit busy last week.

I did some manual testing and was not able to reproduce the error with WAL journal mode. By default new db's are created with DELETE mode (at least on windows). Just as was my old db was since created in v2.05.75.

[kulmegil]

This is probably not related to journaling mode but I keep sing those errors occasionally. Usually on on startup:

20:21:39:079 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:a9:7c:5a:f1:5e-01-0702' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:080 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:a9:7c:5a:f1:5e-01-0b04' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:086 SQL exec failed: SELECT config FROM sensors WHERE uniqueid = '54:ef:44:10:00:25:55:59-01-0500' AND deletedState = 'normal', error: query aborted (4) 20:21:39:086 SQL exec failed: SELECT groups FROM nodes WHERE mac = 'a4:c1:38:8f:1b:f9:3e:1b-01', error: query aborted (4) 20:21:39:088 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:8f:1b:f9:3e:1b-01-0702' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:088 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:8f:1b:f9:3e:1b-01-0b04' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:091 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = '54:ef:44:10:00:28:11:8d-01-0500' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:092 SQL exec failed: SELECT config FROM sensors WHERE uniqueid = '54:ef:44:10:00:28:11:8d-01-0500' AND deletedState = 'normal', error: query aborted (4) 20:21:39:097 SQL exec failed: SELECT groups FROM nodes WHERE mac = 'a4:c1:38:e7:9b:9b:49:17-01', error: query aborted (4) 20:21:39:098 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:e7:9b:9b:49:17-01-0702' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:099 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:e7:9b:9b:49:17-01-0b04' AND deletedState = 'normal', error: no such column: attr (1) 20:21:39:710 UPNP socket not bound, state: 0 20:22:00:498 [Master] APS-DATA.indication seq: 216, failed-status: APP_ERROR (0x05) 20:22:00:498 [Master] APS-DATA.confirm seq: 215, id: 0, failed-status: APP_ERROR (0x05) 20:22:01:530 [Master] APS-DATA.confirm seq: 223, id: 2, failed-status: APP_ERROR (0x05) 20:22:01:530 [Master] APS-DATA.indication seq: 225, failed-status: APP_ERROR (0x05) 20:22:13:185 unhandled APS-DATA.confirm id: 157 status 0xE1 20:22:15:576 SQL exec failed: SELECT groups FROM nodes WHERE mac = 'a4:c1:38:4f:3c:c0:26:a8-01', error: query aborted (4) 20:22:15:578 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:4f:3c:c0:26:a8-01-0702' AND deletedState = 'normal', error: no such column: attr (1) 20:22:15:578 SQL exec failed: SELECT attr FROM sensors WHERE uniqueid = 'a4:c1:38:4f:3c:c0:26:a8-01-0b04' AND deletedState = 'normal', error: no such column: attr (1)

Is there some way I can fix those?

[Mimiix]

The 0xE1 is interference and not something in the code.

I am currently not sure if this issue is conform #5113. @BabaIsYou Can you clearify?

[BabaIsYou]

@BabaIsYou Can you clearify

I think you're right, until there is some proof that it's a code issue these questions would be better asked in https://forum.phoscon.de/c/deconz/general-support/12.

And the last query has no link with the original issue that is more a trouble with lock (i presume) and not the second .... until further investigation.

[github-actions[bot]]

As there has not been any response in 21 days, this issue has been automatically marked as stale. At OP: Please either close this issue or keep it active It will be closed in 7 days if no further activity occurs.

[github-actions[bot]]

As there has not been any response in 28 days, this issue will be closed. @ OP: If this issue is solved post what fixed it for you. If it is not solved, request to get this opened again.