Subsonic database bloat/corruption
I use a Subsonic media streamer install to stream my music library on my mobile phone, media center, laptop etc. Recently, my web UI and other client apps stopped working and I noticed the subsonic server was spewing database errors.
Ugly things like:
87431 [btpool0-6] WARN org.mortbay.log - Nested in org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [ <snip> ]:
java.sql.SQLException: java.io.IOException: S1000 Data file size limit is reached in statement [update media_file set <snip>]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(Unknown Source)
...With hundreds and hundreds of lines in each stack trace. Subsonc is written in Java and uses a database called HSQLDB which has some sort of size-limiting upper limit that's being hit. Since it is similar to SQLite, we can check the size of the database files on disk. Inspecting these revealed it had grown to 2 gigabytes! Considering it holds only a list of media files, a few playlists and some settings, there's no reason it should be that large.
Burried in HSQLDB's docs I found a description of it's startup procedure, here. It turns out by simply changing the "modified" setting to "yes" in the database's property file, the engine triggers a database recovery routine (which actually restores an internal backup). On ubuntu linux, the database property file is /var/subsonic/db/subsonic.properties. After starting subsonic and letting it grind for a few minutes, I just needed to subsonic re-scan my media files and it was back in business! Not one setting or play count was lost. After the cleanup, the database is all of 44 MB. Nice!
I thought this was a pretty easy fix, but tricky to find. If anyone finds this helpful, shoot me a comment below!