How To Clean Up Your Database

Tutorials, tips and tricks.

Moderator: moderators

How To Clean Up Your Database

Postby manwithaplan » Wed May 27, 2015 2:12 am

[Posting here from previously posted external sites which may or may not continue to exist]

****WARNING: THE FOLLOWING INSTRUCTIONAL CAN AND WILL PERMANENTLY DELETE KEY ELEMENTS OF YOUR SUBSONIC DATABASE, INCLUDING BUT NOT LIMITED TO YOUR TRANSCODINGS, YOUR PLAYLISTS, YOUR SHARED FILES, YOUR STARRED FILES, ETC. PROCEED WITH EXTREME CAUTION AND WHEN IN DOUBT, TEST THE RESULTS FIRST ON A MIRRORED INSTANCE OF YOUR "MAIN" SUBSONIC SERVER TO MITIGATE THE RISKS INVOLVED; THESE ARE RAW DATABASE COMMANDS AND THEIR ACTIONS ARE TERMINAL - YOU HAVE BEEN WARNED!*****

From time to time you’re likely to run into issues with the internal database that Subsonic uses to store metadata and configuration. This article contains a couple of quick tips that should help fix issues with files in your media folders not being indexed correctly. Consider this like a forced re-indexing of the entire database.

Go to your Subsonic instance’s database configuration page — http://yoursubsonicinstall/db.view. Once there, paste the following into the text box:

Code: Select all
DELETE FROM ALBUM;
DELETE FROM ARTIST;
DELETE FROM BOOKMARK;
DELETE FROM MEDIA_FILE;
DELETE FROM MUSIC_FILE_INFO;
DELETE FROM PLAYER;
DELETE FROM PLAYER_TRANSCODING;
DELETE FROM PLAYER_TRANSCODING2;
DELETE FROM PLAYLIST;
DELETE FROM PLAYLIST_FILE;
DELETE FROM PLAYLIST_USER;
DELETE FROM SHARE;
DELETE FROM SHARE_FILE;
DELETE FROM STARRED_ARTIST;
DELETE FROM STARRED_ALBUM;
DELETE FROM STARRED_MEDIA_FILE;
DELETE FROM TRANSCODING;
DELETE FROM TRANSCODING2;
DELETE FROM USER_RATING;


Click the Submit button and wait patiently. When this operation is complete, go back to the index page of your Subsonic instance, click Settings, Media Folders, then Clean-up database. Once complete, re-scan the library; all of your issues should now be solved without losing your user settings.

Ostensibly, one could also use segments of the above, not necessarily every line, to clean out just one element.
Last edited by manwithaplan on Thu Jun 18, 2015 2:47 am, edited 1 time in total.
Subsonic 5.2.1 on 2009 Apple XServe w/ Yosemite Server 10.10.5; 96GB RAM. Lots of Music - High Rez, native DSD streaming, and otherwise.
manwithaplan
 
Posts: 151
Joined: Sun Nov 28, 2010 5:01 pm

Re: How To Clean Up Your Database

Postby manwithaplan » Wed May 27, 2015 2:24 am

Just a reference placeholder - Subsonic uses HSQLDB, which I have no background with whatsoever, but here's their home page...feel free to contribute nuggets of wisdom in this thread on its usage or other jujitsu....http://hsqldb.org
Subsonic 5.2.1 on 2009 Apple XServe w/ Yosemite Server 10.10.5; 96GB RAM. Lots of Music - High Rez, native DSD streaming, and otherwise.
manwithaplan
 
Posts: 151
Joined: Sun Nov 28, 2010 5:01 pm

Re: How To Clean Up Your Database

Postby mystery666 » Wed Jun 17, 2015 3:45 pm

manwithaplan wrote:[Posting here from previously posted external sites which may or may not continue to exist]

all of your issues should now be solved without losing your user settings.



I it depends on what you consider "losing your user settings." I wished I'd slowed down because I did this and had I known it would delete my playlists and play counts I wouldn't have.

You need a big fat bold disclaimer on this.
mystery666
 
Posts: 31
Joined: Sat Apr 02, 2011 10:18 pm

Re: How To Clean Up Your Database

Postby manwithaplan » Thu Jun 18, 2015 2:52 am

Fair point I suppose, but wow, okay, so the part where it says "DELETE FROM PLAYLIST" didn't tip you off? Do you make a habit of copying and pasting raw database commands without testing on a mirrored instance or similar test-bed environment before doing this on your production Subsonic instance? I will put a big disclaimer on here for sure, but I take no responsibility for what you did to yourself.
Subsonic 5.2.1 on 2009 Apple XServe w/ Yosemite Server 10.10.5; 96GB RAM. Lots of Music - High Rez, native DSD streaming, and otherwise.
manwithaplan
 
Posts: 151
Joined: Sun Nov 28, 2010 5:01 pm

Re: How To Clean Up Your Database

Postby mystery666 » Thu Jun 18, 2015 6:07 pm

manwithaplan wrote:I take no responsibility for what you did to yourself.


By no means was I passing blame, sorry if I came off that way.
mystery666
 
Posts: 31
Joined: Sat Apr 02, 2011 10:18 pm


Return to Tutorials

Who is online

Users browsing this forum: No registered users and 1 guest