core db gets corrupted

[mbodbg]

We configured an in-memory filesystem for the freeswitch core db. (according to https://freeswitch.org/confluence/display/FREESWITCH/Performance+Testing+and+Configurations#PerformanceTestingandConfigurations-FreeSWITCH'score.dbI/OBottleneck)

We have the situation that Freeswitch is running about 2 weeks properly and then suddenly the core db gets corrupted. The log fills up with the following error messages:

2020-02-25 20:25:30.151902 [ERR] switch_core_sqldb.c:732 [db="/dev/shm/core.db",type="core_db"] NATIVE SQL ERR [file is encrypted or is not a database]
BEGIN EXCLUSIVE
2020-02-25 20:25:30.151902 [CRIT] switch_core_sqldb.c:2163 ERROR [file is encrypted or is not a database], [db="/dev/shm/core.db",type="core_db"]
2020-02-25 20:25:30.151902 [ERR] switch_core_sqldb.c:732 [db="/dev/shm/core.db",type="core_db"] NATIVE SQL ERR [cannot commit - no transaction is active]
COMMIT

We use the following version:

FreeSWITCH Version 1.10.2-release-13-f7bdd3845a~64bit (-release-13-f7bdd3845a 64bit)
on
4.9.0-11-amd64 #1 SMP Debian 4.9.189-3+deb9u2 (2019-11-11) x86_64 GNU/Linux

Is there something we can check, or is it better to use a local postgres db for the core db to avoid such error?

[huongbn]

Using postgresl to improve this problem

[presidentpatel]

Hello,
We are also facing similar issue.. SQLlite is getting corrupted and locked.. so we have to stop freeswitch and delete existing file and restart then only it is working and it is intermittent issue .. so what could be reason behind this issue ? can any one faced or facing such issue... ADVANCE THANX>... Vipul

[outruner]

hello same issue here. It appears only with wss connections.

[huongbn]

You can use: - Postgres instead of SQLite in the core - Using SSD disk instead of HDD - Customize configuration and disable logfile if it unnecessary

…

On Thu, Sep 24, 2020 at 3:32 PM outruner ***@***.***> wrote: hello same issue here. It appears only with wss connections. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#420 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH5QL7TJ2WAOM7ALQTYP27DSHL73PANCNFSM4K3RNRQA> .

[themsley-voiceflex]

I have this problem on freeswitch 1.10.10 using wss and so far I have looked at 2 copies of the corrupted database and both of them have what looks like 47 or 48 bytes of overlaid storage at the start of the file. In both cases the first 4 bytes of the file are 0x1703030012. I was hunting around and found a link to https://engineering.fb.com/2014/08/12/ios/debugging-file-corruption-on-ios/ from https://www.sqlite.org/howtocorrupt.html and the similarities are very strong. They state that they had an overlay and "Using a hex analyzer, we found a common prefix across the attachments: 17 03 03 00 28.". In our case it is 0x1703030012 so not identical but close enough to make me think it will be a very similar problem.

[themsley-voiceflex]

Seems it happened again while I was looking. Again, od -X /dev/shm/core.db starts with

0000000 00030317 cdc06712 26b9d175 b91379ec

[andywolk]

Try updating sqlite to the latest.

[themsley-voiceflex]

It is already the latest available for RHEL 9:
[root@fstrtc01 tmp]# rpm -q sqlite
sqlite-3.34.1-6.el9_1.x86_64

[andywolk]

3.34 is too old

[themsley-voiceflex]

Could you please read the Facebook engineering post I linked to and see the similarities there, most especially the fact that the overwrite is at the start of the file where the "SQLite format 3" eyecatcher has been overwritten completely and the data there starts 0x1703030012. They blame a race condition in their code that writes to a filehandle that has been closed and then an SSL_write to that reused filehandle overwrites their SQLite database file.

I now have 3 separate copies of a corrupt core.db and in each of them the first 5 bytes are always that.

[andywolk]

The latest sqlite is 3.43.2 at the moment https://www.sqlite.org/index.html

[themsley-voiceflex]

I do not think an update to SQLite is possible on a RHEL system without breaking lots of stuff. The package manager and half the distro tools are written to use the version of SQLite that ships with the distro. I am also completely unconvinced that it has anything to do with the problem.

The FB overwrite was with 0x1703030028 and ours is 0x1703030012 and the comment they make about how they fixed this is "The SSL layer was writing to a socket that was already closed and subsequently reassigned to our database file.". It is also the #1 reason mentioned in the SQLite "How To Corrupt An SQLite Database File" link.

This seems like too coincidental to be a coincidence especially since it is identical across 3 corrupted core.db files so far, 2 of them today and one from last week. Unfortunately since they reside in /dev/shm I am unable to pull older corrupted versions. However given the regularity with which freeswitch eats its own core.db when using wss I am sure I'll have some examples in the days to come.

[themsley-voiceflex]

Third corrupt db today also starts

0000000 00030317 0298eb12 10f4a06d a38d8a70

[andywolk]

You need to update sqlite to the latest. Make sure you are using a version containing a fix described in this article:
https://www.philipotoole.com/how-i-found-a-bug-in-sqlite/
The version you are using is behind the fix.