How to clean up a corrupted Subsonic database

These instructions apply to Subsonic 4.7 running on a RedHat variant Linux

If, in the course of using Subsonic, the DB becomes corrupted and you begin seeing problems, you can retain your settings and clear the database to resolve the issue.

I was getting errors like "org.springframework.dao.TransientDataAccessResourceException" and "java.sql.SQLException: S1000 General error java.lang.NullPointerException in statement" because my "media_file" table had somehow gone missing.

  1. Stop Subsonic
    • "service subsonic stop"
  2. Backup your /var/subsonic/db/subsonic.script file
    • This file contains a handful of SQL statements that will be used later to recreate your users, media directory settings, etc.
    • "cp /var/subsonic/db/subsonic.script /root/"
  3. Delete the contents of your /var/subsonic/db/ directory
    • "rm /var/subsonic/db/*"
  4. Restore your subsonic.script file
    • "cp /root/subsonic.script /var/subsonic/db/"
  5. Start Subsonic
    • "service subsonic start"
  6. In that your media library will now be empty, you'll want to go to "Settings..." "Media Folders..." and click "Scan media folders now"

Troubleshooting

  1. To validate that there was something wrong with the database, I went to the direct database web UI for subsonic by browsing to http://example.subsonic.org/db.view
  2. From within the db.view interface you can run queries directly. I ran "select * from media_file" and found that there was no media_file table.
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.