How I fixed my GlassWire database

For the last 40 hours, I was trying to save my data from the glasswire’s evil twin monster, and I finally managed to save it, so I want to write what I did here, in case it helps someone.

First of all, I would like to thank to the glasswire team for using standard sqlite databases and not a custom thing, otherwise I would just uninstall glasswire and try to find an alternative. (there aren’t. No other program can show how many data an application transferred to a certain host.)

Story time

I woke up yesterday and glasswire was crying in the bottom saying it cannot connect to the local server. I enabled logging in the config file but I didn’t get any clear error. (I don’t fully remember, but it was something like statistics manager failed to start.)

I don’t know much about databases, but I had a program, “DB Browser for SQLite”, I opened each of the databases, selected “Integrity check” from the Tools menu, and all of them returned “ok”, so none of my databases were corrupted. (I was wrong.)

Fortunately I had a backup, unfortunately from 2 months ago. After lots of trying, backing up, restoring and changing things around, I found that if I copy the old glasswire.db file from my backup, replace the current one in C:\ProgramData\GlassWire\service, (yes I haven’t upgraded to 2.3 yet, it should be service-full now.), restart the service from Windows services, the service now starts. (It would quickly close before.)

After starting the service, if I open the Glasswire app, press connect again, it works, and then I can quit the glasswire app, stop the service, replace the old glasswire.db (from 2 monhts ago) with the current one (that I backed up), I can restart the service and Glasswire app can run fine now. Except that now it lost last 1 month of data.
(Opening the glasswire after changing the .db file was a requirement, without it, after restoring the current database, the service would crash instantly again.)

End of story time.

Glasswire stores internet usage for every second, at C:\ProgramData\GlassWire\service\stats\glasswire_stats_1sec_16xxxxxxxx.db file. After some time, it groups those usages for each 30 second, at the 30sec_16xxxxx in the same folder, and after some more time, it groups those at glasswire_stats_600sec databases.

I looked at the Glasswire app, and found that I have no data after June 8. One of the 30sec databases starts just that day. So I initially tried looking that file to see if there is anything wrong with it, didn’t see anything unusual, tried partially deleting the data in that file (in case there is a corrupt record), creating a new database and copying all of the records to the new one and changing that file with the new one (in case the data is fine but the database is corrupt), and even merging that file with another 30sec database i had in that folder, (that started 3 days ago, coincidentally I guess.), nothing worked.

After some more careful looking around, I realized that the problem wasn’t in the 30 sec database, but the 600 second one. I didn’t realize that there was a new 600 second database created at June 8 at the same time, and I didn’t realize it was corrupt.

An empty glasswire database file, with the SQL tables created, weighs around 28 KB, while that 600sec file was only 4 KB. Same as an empty SQLite database without any tables. And the db browser program was returning “ok” at the integrity check, because even though there were no tables, there were no errors either. (DB Browser was not showing any tables for the corrupt 600sec database, but I didn’t notice that earlier.)

Solution

So it was clear that the corrupt database was the 600sec database created a month ago. I still had last month’s data at a 30sec database, and I had some free time.

I duplicated another 600sec database (from the same folder), deleted all of the records in it from DB Browser program, compacted the database and saved it with the name of the corrupt 600sec database, now I had an empty 28 KB sqlite file.
(I tried replacing the old corrupt 600sec database with this new one, glasswire worked again. There was no need to restore and old glasswire.db and delete it back. The old glasswire.db was simply creating a new 600sec database (because it was before June 8), therefore fixing the Glasswire service from crashing.)

Then I wrote a little python script that reads all the records from the 30sec database, groups those into 600 seconds, and inserts new records to the empty 600sec database I just created. After that, I had all of my history back again in GlassWire.

2 Likes

@thro65

I’m sorry to hear your database was corrupted. Thanks for taking the time to make this detailed post that explains how you solved the issue. I hope it will help others in the future!

Also, for those who wish to back up and protect your GlassWire history we have made a guide here: GlassWire user guide and manual

Meanwhile we will work to improve GlassWire so this issue is less likely to happen for anyone in the future.

Thanks Ken.

If anyone has this very specific case, I can try to help some more, and share the script I made for creating 600sec database, although I was very lucky in catching the 30sec db before it got deleted (they last for about one month, based on what I saw), and even luckier to have the corrupt 600sec database start at the same time 30sec started. (And some more luck that this happened one month after those files got created.)

After this, I also made a PowerShell script that launches on startup and takes a backup of GlassWire databases, but I want to test it some more before sharing it in the forums. Hope I don’t forget it.

2 Likes