exposure.db takes up huge amounts of space

[RalfJung]

After less than a week of exposure tracking (and less than 1000 collected IDs total), my phone is now unusable because microG used up all of the free space on the internal storage. By far the biggest part of this is /data/data/com.google.android.gms/databases/exposure.db, which takes 952M.

I am not sure what microG is doing there, but 1MB per ID seems like a huge amount of storage for a short string, some timestamps and signal strength, which should all easily fit in 1KB. I will probably have to disable exposure tracking because if there is no way to compress this a bit.

The entire storage partition on my phone has 5GB (out of 8GB total internal storage), so a single app consuming 1GB is way over the top... even Signal, which is notorious for how much space it needs, is at ~600MB.

[RalfJung]

Turns out it's not actually the exposure events that take up all the space. It's the "diagnosis" table that has grown to 6.7 mllion entries.

[Romern]

I had this problem before ( #1115 (comment) ), and it appears to happen when the tracing app itself has trouble with interacting with the EN API ( See #1171 ), then it generated for each try an entry in the diagnosis table. Currently though I do not have a problem with this (6000 IDs/125MB) (though I fumbled a bit with the DB some weeks ago and purged the diagnosis table, so it might not be representative).

[dazzzl]

I have the same problem.
@Romern How do you fixed it? My database grows after 1.5 day to 1.2 GB and on the disk was no space left. I could not drop the table, so i had to delete the database. If I remove the table before, microG will crash.

[Romern]

@dazzzl I used the sqlite3 command line and executed

DELETE FROM diagnosis;

Though I do not recommend running it as is, because the table is involved in risk calculation as far as I understand.
The table does not seem to be cleared though in the daily cleanup ( https://github.com/microg/android_packages_apps_GmsCore/blob/master/play-services-nearby-core/src/main/kotlin/org/microg/gms/nearby/exposurenotification/ExposureDatabase.kt#L76 ), even though the tek_check table gets cleared, so that should probably be added.

[mar-v-in]

@Romern the diagnosis table has a reference with on delete cascade, thus when elements from tek_check are removed, all relevant diagnosis entries are removed as well.

Deleting diagnosis entries usually has no effect as most apps don't reuse diagnosis tokens. The Austrian app was the only one that I experienced issues with. Also, I don't think we need to keep more than one diagnosis token per app, as currently the only diagnosis is for COVID-19. However, if the API was used to keep track of other diseases, a single app might be having multiple diagnosis tokens actively used.

I plan for some optimizations on how we do the database that will reduce storage consumption and improve performance (which apparently is a problem for low-end devices).

[jugendhacker]

My device collected only 362 IDs but the service core consumes 1,42 GB of internal storage which basically renders my 8GB device useless, what could I do about it to not lose the collected IDs, but free storage space?

[Tentos]

My phone ran into a storage problem because of this (i.e., no free storage left).

Fortunately, it seems to be possible to delete the database by only using the microg UI:

I deleted the German Corona App and deactivated Exposure Notification in microg. After some crashes of the microg UI (I guess), microg managed to delete the database so that the used storage was back to normal. I obviously lost the exposure notification keys in the process, so this solution is only a "last resort".

[mar-v-in]

Current git already contains a fix for this problem, which seems to mostly affect "slower" devices and probably also only the German CWA.

The problem seems to be caused by CWA stopping and restarting risk calculation after 1 minute which may be too short on some devices, Google has a rate limit to make sure there are not too many risk calculation requests per day, but microG currently does not enforce this rate limit, assuming devs would have taken care of not doing to many requests already. CWA devs announced to increase the timeout to 3 minutes in v1.4.0 (due next week) and to further look into this issue for v1.5.0.

You can try a version built from git master from here: https://microg.org/dl/core-en.apk
Note that if your database is already very large, it may take a few minutes after installation to start collecting ids as it has to clean up the database. On a test device Fairphone 2 this took about 7-10 minutes for a ~5GB database file.

[cryptogeek13]

I installed your suggested patched version from master, but microG still takes up 6GB space, still rising. Are there extra steps to take to trigger database cleanup? I waited a few hours now and restarted my phone a few times

[mar-v-in]

@cryptowarrior-de Can you check logcat for any exposurenotification and cleanup related log entries?

[cryptogeek13]

I will look into this tomorrow and post log entries if I find some

[dueringa]

I'm currently trying out the git-master version as well.

The messages do appear in adb logcat, but multiple times. (Are there different databases being updated?)

10-04 18:27:18.021  6849  6849 D ExposureNotification: Upgrading database from 4 to 5
10-04 18:27:18.021  6849  6849 D ExposureNotification: Dropping legacy tables
10-04 18:28:44.597  7361  7361 D ExposureNotification: Upgrading database from 4 to 5
10-04 18:28:44.597  7361  7361 D ExposureNotification: Dropping legacy tables
10-04 18:30:09.389  7480  7480 D ExposureNotification: Upgrading database from 4 to 5
10-04 18:30:09.389  7480  7480 D ExposureNotification: Dropping legacy tables

However, on my Fairphone 2 and a 7.6 GB database file, after 50 minutes, the database size is still the same. Also there doesn't seem to be a success message?

[cryptogeek13]

Same behavior for me. It starts dropping tables a few times. Looking at the pid the process seems to die or be killed before database cleanup finished.

2020-10-05 10:10:23.434 13538-13561/com.google.android.gms I/SQLiteConnection: /data/user/0/com.google.android.gms/databases/exposure.db-wal 1611088952 bytes: Bigger than 1048576; truncating
2020-10-05 10:10:23.551 13538-13561/com.google.android.gms D/ExposureNotification: Upgrading database from 4 to 5
2020-10-05 10:10:23.551 13538-13561/com.google.android.gms D/ExposureNotification: Dropping legacy tables
2020-10-05 10:11:29.510 13538-13538/com.google.android.gms I/.android.gms:u: Starting a blocking GC NativeAlloc
2020-10-05 10:12:01.546 13538-13538/com.google.android.gms I/.android.gms:u: Starting a blocking GC NativeAlloc
2020-10-05 10:12:32.578 13538-13538/com.google.android.gms I/.android.gms:u: Starting a blocking GC NativeAlloc
2020-10-05 10:12:38.952 2945-8196/system_process I/ActivityManager: Process com.google.android.gms (pid 10766) has died: vis TRNB

[tommes0815]

I have the same behavior. I tried to run the sqlite cleanup by myself on the shell of the phone and got a sqlite error Error: database or disk is full when dropping the diagnosis table (which is the table blowing up the database). So apparently dropping needs at least the same amount of free space as the to be dropped table requires. Since my storage is kind of full (partly due to the database file) it fails.

I solved the problem for me pulling the database to my PC, dropping the diagnosis table and pushing it back to the phone. I am not sure if it is a safe operation. I at least tried to make sure the EN microg part was disabled and not trying to upgrade the database itself during my access. After pushing the database back and opening the microg settings the upgrade process went through in a second and converted the table to the new format.

Afterwards I opened CWA which provided the diagnosis keys again. That was again slow since it had to load all keys of the last two weeks. During the key import I got twice a ANR of microg:

10-05 11:58:06.421  1470  2137 E ActivityManager: ANR in com.google.android.gms
10-05 11:58:06.421  1470  2137 E ActivityManager: PID: 3254
10-05 11:58:06.421  1470  2137 E ActivityManager: Reason: executing service com.google.android.gms/org.microg.gms.nearby.exposurenotification.CleanupService

and

10-05 12:06:30.399  1133  2135 E ActivityManager: ANR in com.google.android.gms
10-05 12:06:30.399  1133  2135 E ActivityManager: PID: 3257
10-05 12:06:30.399  1133  2135 E ActivityManager: Reason: executing service com.google.android.gms/org.microg.gms.nearby.exposurenotification.ScannerService

Each ANR stopped the import process and left some key files pending. The third import process finished and imported all key files. Now everything looks like it is running successfully. Maybe those ANRs are bugs in the microg implementation (trying to access the database while it is locked)?

[dueringa]

I'm going with a less rigorous DELETE FROM diagnosis WHERE rowid NOT IN (SELECT min(rowid) FROM diagnosis GROUP BY token); (manually loaded on the PC via adb, because it would have taken too long on the phone).

[cryptogeek13]

@dueringa @tommes0815 Since this seems to be working for both of you, could you provide more detailed instructions how to do those database operations on pc? I think a lot of people (including me) won't have enough free space on their device to do it locally and having a solution tracked in this ticket would be great for those who don't really know how to do this (also including me xD)

[dueringa]

Here's what I did: 1. Check on the phone that developer settings are accessible 2. Check that device is rooted 3. Check in the developer settings that Root is enabled for adb 4. On the PC, you need the adb command (e.g. from the Android SDK / platform tools) 5. You need the sqlite executable (e.g. from repositories if you're on Linux, from https://sqlite.org/index.html if you're on Windows) 6. Further up in the thread, there was a suggestion to disable Exposure Notification in the microg settings before continuing (to avoid writes to the database while you're modifying it) 7. Execute the commands ``` adb root adb pull /data/data/com.google.android.gms/databases/exposure.db sqlite3 exposure.db # modify the database as mentioned above # quit the sqlite shell (.quit should work on all OS) # check file size adb push exposure.db /data/data/com.google.android.gms/databases/exposure.db adb unroot ``` 8. Re-enable Exposure Notification by starting CWA, or your local app Note that I don't know exactly what the tcid column in the diagnosis table means, dropping everything but the lowest tcid of each token was merely a guess.

[cryptogeek13]

I just followed your instructions and it worked like a charm. Thank you so much, now I finally have some space on my phone again :D shrunk from 7.8GB to 72MB and db-upgrade finally went through successfully.

The process even works with an unrooted phone. Enabling ADB with root seems to be enough.

[real-or-random]

I also had to drop the table manually on my laptop because the database upgrade never finished. Initially, it stopped due to "disk full" but when I ensured there's more free space, I couldn't figure out from the logs why it continued to fail...

By the way DELETE FROM diagnosis; is safe, at least if you run the master build because the database upgrade will drop that table anyway without looking at it.

[jw243]

Current git already contains a fix for this problem, which seems to mostly affect "slower" devices and probably also only the German CWA.

The problem seems to be caused by CWA stopping and restarting risk calculation after 1 minute which may be too short on some devices, Google has a rate limit to make sure there are not too many risk calculation requests per day, but microG currently does not enforce this rate limit, assuming devs would have taken care of not doing to many requests already. CWA devs announced to increase the timeout to 3 minutes in v1.4.0 (due next week) and to further look into this issue for v1.5.0.

You can try a version built from git master from here: https://microg.org/dl/core-en.apk
Note that if your database is already very large, it may take a few minutes after installation to start collecting ids as it has to clean up the database. On a test device Fairphone 2 this took about 7-10 minutes for a ~5GB database file.

@ mar-v-in

Could you release a MicroG update on Fdroid soon, that includes this fix, please? Infection rates are increaing dramatically all over Europe right now, so it would be great if we could start using CWA with the updated MicroG without the need to patch ourselves.

Thanks a lot!

[Tentos]

@ mar-v-in

Could you release a MicroG update on Fdroid soon, that includes this fix, please? Infection rates are increaing dramatically all over Europe right now, so it would be great if we could start using CWA with the updated MicroG without the need to patch ourselves.

Thanks a lot!

@jw243 You can try to directly install the linked apk. I have downloaded it several days ago (before the most recent commits) and could install it by

1. allowing installation from unknown sources
2. opening the download folder with the Amaze file manager
3. selecting the apk
4. disabling the installation from unknown sources after the successful update.

However, I had some problems first. I think that microg worked without problems only after a second installation. It was not necessary to reset microg. Note however that I had deleted my exposure notification keys before the manual installation, so your "mileage may vary."

Moreover, the linked apk is the current snapshot and not an official release (as you have implicitly pointed out), so bugs may be present.

[nidico]

You can try a version built from git master from here: https://microg.org/dl/core-en.apk

Unfortunately, the new version doesn't seem to clean up my database (running it since ~24h and restarted phone and Corona app a couple of times). It's still around 20 GB...

I must confess that I haven't looked for logs yet due to lack of time.

Will now try to run "delete from diagnosis" on the smartphone over night... However this seems to require even more space, I guess it will abort again. And I guess that copying the file to a more powerful machine and running the command there would work, but I don't really have 20 GB of free space at the moment.....

[mar-v-in]

I just uploaded a new build to https://microg.org/dl/core-en.apk which now includes the commits 1deeb45 and 0eb75ba which hopefully solve issues with large database files needing even more storage to be cleaned up and thus fail to do so. The database migration still needs a few MB of available storage, but afterwards you should end up with less than 10MB database size :)

[jw243]

Hi mar-v-in,

have you got an anticipated date, when the next regular F-Droid release will come out, including these improvements?

Thanks.

[mar-v-in]

@jw243 After I got a few confirmations that the upgrade to this version works smooth.

[kurt-by]

Updated from 6afcca0 to 0eb75ba. Storage reduced from 1.4 MB to 826 kB. Had to switch local gsm backend off and on to get network location after update.

[jounathaen]

Next confirmation incoming: For me, the new version reduced the app size from 12.6GB to 74MB. (Guess that was all the database). It also seems to fix #1227 but that's only the first observation.
Thanks for your effort by providing this framework. Can't be honoured enough! 👍

[mase76]

My DB was shrunken from over 10GB to a few hundred kB. I also had communication problems between the API and the CWA, which seems to be fixed now.

[GitteHuber]

@jw243 After I got a few confirmations that the upgrade to this version works smooth.

It works partially on my Moto G4 Plus (athene), exposure.db is now a few 100 KByte instead of the initial 6.5 GByte and stopped growing unreasonably.

But I had to do the shrinking process described above on my Laptop because microG (0.2.12.203315-12) kept crashing while trying to clean up the database. After the manual shrinking microG was stable again and stopped crashing.

[ou-li]

Thx @mar-v-in ! 👍🏽
I can confirm:
Your fix has reduced microg's memory use from over 3 GByte to 74 MByte within a matter of seconds.
Switching exposure notification off and on has made CWA work again, too.

[nidico]

I can also confirm that the new fix has reduced the size of the database dramatically and that CWA is now working again. Thanks!!

However, my phone is now partially broken, though this is probably unrelated, or caused by the temporary full storage: Wifi isn't working anymore, as I cannot store passphrases and the old ones seem to be deleted. I guess it's unrelated, but I'm posting it, in case anybody now has the same issue.

[nidico]

Wifi isn't working anymore, as I cannot store passphrases and the old ones seem to be deleted.

Just in case anybody else encounters this issue, e.g. due to a full disk partition: Deleting/renaming /data/misc/wifi/WifiConfigStore.xml has solved the problem for me (via). (This requires a rooted phone though.)

[eike-fokken]

I just tried it and up to now everything seems to work.
exposure.db shrunk from 13GB to 26MB.
I went the way of pulling the file to my computer. As I didn't have 26GB space left, I also ran into the problem of needing double the size.
The following makes sqlite3 use no additional space but disables journaling, which will destroy the database incase the sqlite command is interrupted (so be careful not to have scheduled shutdowns or anything that might crash the computer).

sqlite3 exposure.db
PRAGMA journal_mode = OFF;
DELETE FROM diagnosis;

see sqlite docs for what this means.
Note that you should pull a fresh copy from the phone, if you already tried with journaling activated and aborted the sqlite-run, because the db-file will be corrupted.

The command still takes a considerable amount of time, in my case half an hour on a thinkpad x260 with i7, so not the slowest notebook. During this time the file didn't shrink. Only in the end did it magically lose about 13GB of disk usage.

I guess this can also be done directly on the phone (via rooted adb shell), but bear in mind that in that case you don't have a backup of exposure.db. I'm not sure if that is a problem apart from you losing all contact tracing information.
If you insist on trying this, maybe activate airplane mode, so nothing coming over the internet disturbs the sqlite run (also of course make sure, your computer is connected to a wall plug).

[mar-v-in]

@eike-fokken you needed to manually shrink the file when using the latest version I uploaded at #1190 (comment) ?

Also for everyone that migrated to the latest version from a large database (using the automated approach by the latest test version): Does the collected id history in microG Exposure Notification still show your history of the last days - same as it was before the upgrade?

[rai42]

Another confirmation: Working well now with German Corona Warn App!

I had a very similar problem as described above with MicroG v0.2.12.203315 - the database would grow to several 100 MB (sometimes up to 1.7GB) after 1-2 days of use and in addition, it would slow down the phone to the point of being almost unusable - even though the data partition was not completely full. I am using a very old Galaxy SII so that would probably count as very slow by today's standards.

Before Installation of the new version I had uninstalled MicroG and reset the CWA. This completely removed the database (which I had been planning to do anyway).

After installation of the new version and resetting CWA (using 1.3.0) I initially got an error (see screenshot below).

However after rebooting the phone and resseting CWA again it worked fine. Has been running for 7 days now with the DB size roughly constant at ~48MB.
Since I reset everything I cannot say anything about the DB cleanup mechanism.

Thanks a lot for your work on this and MicroG in general!

[eike-fokken]

Oh, I'm not sure, I didnt try it without the manual shrink, because of @GitteHuber s experience.

…

On October 15, 2020 6:23:20 PM GMT+02:00, Marvin W ***@***.***> wrote: @eike-fokken you needed to manually shrink the file when using the latest version I uploaded at #1190 (comment) ? -- You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub: #1190 (comment)

[jounathaen]

@eike-fokken you needed to manually shrink the file when using the latest version I uploaded at #1190 (comment) ?

Also for everyone that migrated to the latest version from a large database (using the automated approach by the latest test version): Does the collected id history in microG Exposure Notification still show your history of the last days - same as it was before the upgrade?

Well, I didn't took a screenshot before, but there is data for the last weeks and it looks like nothing was changed. (Still peaks matching the days where I had some events where more people were present)

[Diapolo]

@jw243 After I got a few confirmations that the upgrade to this version works smooth.

The pre-release version also fixed the german CWA issue for me. I've been using the app just for 2 days and the gathered data is still there ;).

[Axolord]

Updated from the fdroid release to the current beta. Storage usage went down from 1.6GB to 1.5MB.
After a minute or so (redmi note 5 pro) the collection of IDs seemed to work again, showing history, the last broadcast ID, ect. CWA also working without a problem.

[Schyrsivochter]

I just tried it and up to now everything seems to work.
exposure.db shrunk from 13GB to 26MB.
I went the way of pulling the file to my computer. As I didn't have 26GB space left, I also ran into the problem of needing double the size.
The following makes sqlite3 use no additional space but disables journaling, which will destroy the database incase the sqlite command is interrupted (so be careful not to have scheduled shutdowns or anything that might crash the computer).

sqlite3 exposure.db
PRAGMA journal_mode = OFF;
DELETE FROM diagnosis;

see sqlite docs for what this means.
Note that you should pull a fresh copy from the phone, if you already tried with journaling activated and aborted the sqlite-run, because the db-file will be corrupted.

The command still takes a considerable amount of time, in my case half an hour on a thinkpad x260 with i7, so not the slowest notebook. During this time the file didn't shrink. Only in the end did it magically lose about 13GB of disk usage.

I guess this can also be done directly on the phone (via rooted adb shell), but bear in mind that in that case you don't have a backup of exposure.db. I'm not sure if that is a problem apart from you losing all contact tracing information.
If you insist on trying this, maybe activate airplane mode, so nothing coming over the internet disturbs the sqlite run (also of course make sure, your computer is connected to a wall plug).

This would not work on my phone and probably many others because the database uses a write-ahead log instead of a rollback journal. The WAL was my problem; I had a db file at ~7.5G and a WAL file that had grown to ~9G, filling up my storage, and it would not disappear.

What I did was: force-stop microG GmsCore (otherwise I’d get a ‘database locked’ error) and run the following:

> adb root
> adb shell
# sqlite3 /data/data/com.google.android.gms/databases/exposure.db
sqlite> PRAGMA wal_checkpoint(TRUNCATE)

This gets rid of exposure.db-wal while maintaining database integrity. Afterwards I was able to adb-pull exposure.db on my laptop and run the partial delete command dueringa posted, which did need ~7.5G for the WAL but succeeded in shrinking the database file down to ~34M.

[jplitza]

My database crossed the 10 GB threshold today, so I found this issue, deleted almost everything from my phone (no need to have it there anyway, what's Nextcloud for?) to have another 10 GB of space available, and installed the beta from above. Almost instantly, the free space doubled and a laughable 240 KB were left of the database. I don't think the free space was needed, because writing another 10 GB surely would have taken more time.

The graph in the microG settings still shows the right amount of stored keys. CWA took endlessly to update the keys (didn't work before either, c.f. #1227), then complained about a timeout, but then probably tried again and now both CWA and microG say it just updated the keys.

So everything is fine with the new update. :)

[mar-v-in]

@jplitza

CWA took endlessly to update the keys

This is expected behavior after the database upgrade as the key matching cache is lost in the process. For subsequent checks that happen once a day, only the newly published diagnosis keys have to be checked and not a full 14 day history, thus it should be about 14 times faster ;)
This doesn't happen for completely new installations, as key matching is near instant when you don't have any IDs collected yet.