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)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:798)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:792)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:850)

...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!

Add new comment