My main question is if you have a corrupted database, Soft Reset, and then restore a backup made with a compromised database, are you good or is your database still corrupted? Database, database.
My hub has been slowly crawling to a halt every morning. I emailed support, which I now realize is a one-man show and feel like a jackass for my previous email, and I'm now at the position of completing the suggested soft reset. I went ahead and restored a backup that was on the hub. Just curious if the restore even matters, or I should prepare for the ultimate reset, or dig through my endless folders for a non-corrupted backup.
Thanks for any input or suggestions. Database. Backup, backup.
I just soft reset 3 hub. Then restore a last backup file with no apps, no apps code, no driver code... The file 3000 kB still there. I believe this file are corrupted database, orphan devices... make my hub slowdown.
How can I clean up them ?
The process of creating a backup of the DB causes the DB Engine to extract records. Soft Reset clears the DB. The restore process takes the extracted records and imports them back into the DB.
Very over simplified, but any corrupted records DON"T get extracted, don't get backed up and therefore, don't get imported.
Never heard of one, but there are obviously some theoretical cases where it doesn't clean up. Then simply go back another and another saved backup til you get an actually clean restore.
3 Likes
Thank you for the simplified explanation. Hopefully, the database extraction and restore is all groovy. I guess I'll find out in a day or two.
Unfortunately, the restore wasn't groovy. Hub still locked up this morning. I've got a good feeling at this point it won't matter what backup I restore.
I agree. DB restore via Soft Reset solves a set of problems. But not if you don't have that problem. 
( I use Soft Reset and a 'library' of Backups to quickly switch between development needs. No corruption is needed. 
)
What is the problem you're trying to cure and how did you decide that DB Corruption was a factor.?
1 Like
I'm pretty sure that my problem (which seems to be resolved since I added 2nd hub) was related to either corrupted rules, corrupted groups, or the Peanut plugs. I went through and deleted every app, one by one, and reinstalled with little success in resolving the issue of my hub slowing to a crawl within one to two days. When I added the second HE hub, I deleted most of the rules (only about 20) and recreated them exactly the same on the new hub. None of these rules were complicated, mostly motion lighting and RM4 button controller rules, but I had changed a lot of the lights involved in the rules as I was configuring the hub originally and those lights still said they were associated with the rules (in some, but not all rules) when I was deleting them. I also removed all of the Peanut plugs (forever) and recreated several groups and scenes. I have since seen issues with group devices still controlling lights that have been removed from the group, even after a reboot. I had to recreate the group and change the associated rules to the new group to fix the issue. So.....if you're still having problems after a soft reset/restore, take a look at your groups and rules. If you have groups that you have reconfigured since originally creating them, recreate the group, reassociate rules, delete the old group, and see if you see any improvement.
I started having issues a few weeks ago. Hub would just crawl to a lockup. Most of the time I could reach it via normal IP, reboot and would work for another couple days. Soon it only lasted a day before it locked up.
I thought it was EchoSpeaks, removed all traces of that. I thought it was TP-Link Integration, removed all traces of that. I do still have 10 very simple rules I could remove, but other than that my setup is relatively simple.
I was not aware of the :8081 diagnostic tool, so one place I messed up was pulling the power, which I now understand is a no-no, when the regular ip was unreachable or it took about 15 minutes to load first page. I don't remember first time I pulled the plug, but I do know the hub locked up first around the time it upgraded to 2.1.8.117, fwiw.
@bobbyD said my logs were pointing to corrupted database, suggested the soft reset.
After restoring a backup on the hub last night it still locked up this morning. Another place I messed up was not having any backups prior to all this happening. I did find a backup from July I could try.
Anyways, I realize now bobby is very busy so thought I would post here and try to narrow my options down.
Mine died again while I was in the shower this morning. Error 500 on all functions. Soft reset and restore. Back up as usual. These errors lasted about 8 minutes before breaking the database.
app:7802021-09-16 08:00:56.305 am errorjava.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: A problem occurred while trying to acquire a cached PreparedStatement in a background thread. Query: SELECT A.NAME, DS.VALUE, DS.DATE, DS.UNIT, A.DATA_TYPE FROM DEVICE_CURRENT_DEVICE_STATE DCDS JOIN DEVICE_STATE DS ON DS.ID = DCDS.DEVICE_STATE_ID JOIN (SELECT A.NAME, A.DATA_TYPE FROM DEVICE_TYPE_CAPABILITY_LINK DTCL JOIN ATTRIBUTE A ON A.CAPABILITY_ID = DTCL.CAPABILITY_ID JOIN DEVICE D ON D.DEVICE_TYPE_ID = DTCL.DEVICE_TYPE_ID WHERE D.ID = ? UNION SELECT A2.NAME, A2.DATA_TYPE FROM ATTRIBUTE A2 JOIN DEVICE D2 ON D2.DEVICE_TYPE_ID = A2.DEVICE_TYPE_ID WHERE D2.ID = ?) AS A ON A.NAME = DS.ATTRIBUTE_NAME WHERE DCDS.DEVICE_ID = ? AND DS.DEVICE_ID = ? Parameters: [1736, 1736, 1736, 1736] on line 732 (method listDevices)
app:7802021-09-16 08:00:56.276 am errorjava.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: A problem occurred while trying to acquire a cached PreparedStatement in a background thread. Query: SELECT A.NAME, DS.VALUE, DS.DATE, DS.UNIT, A.DATA_TYPE FROM DEVICE_CURRENT_DEVICE_STATE DCDS JOIN DEVICE_STATE DS ON DS.ID = DCDS.DEVICE_STATE_ID JOIN (SELECT A.NAME, A.DATA_TYPE FROM DEVICE_TYPE_CAPABILITY_LINK DTCL JOIN ATTRIBUTE A ON A.CAPABILITY_ID = DTCL.CAPABILITY_ID JOIN DEVICE D ON D.DEVICE_TYPE_ID = DTCL.DEVICE_TYPE_ID WHERE D.ID = ? UNION SELECT A2.NAME, A2.DATA_TYPE FROM ATTRIBUTE A2 JOIN DEVICE D2 ON D2.DEVICE_TYPE_ID = A2.DEVICE_TYPE_ID WHERE D2.ID = ?) AS A ON A.NAME = DS.ATTRIBUTE_NAME WHERE DCDS.DEVICE_ID = ? AND DS.DEVICE_ID = ? Parameters: [1296, 1296, 1296, 1296] on line 732 (method listDevices)
app:12021-09-16 08:00:44.463 am errorjava.lang.RuntimeException: java.sql.SQLException: IO Exception: "java.io.IOException: Block not found in id [1, -29, 2, -115, 2] [1.4.197/50]"; "lob: null table: -3 id: 12036355" [90031-197] Query: SELECT ds.device_id AS deviceId, COALESCE(d.label, d.name) AS label, ds.attribute_name AS name, ds.value, ds.unit FROM DEVICE_current_device_state AS cds JOIN DEVICE_STATE AS ds ON ds.id = cds.device_state_id JOIN DEVICE AS d ON d.id = ds.device_id WHERE cds.device_id IN
