Recover database info

[plablo09]

Hi,

I got hit by the infamouns 503 error and got to recover by deleting my db folder and restarting subsonic. Of course, I lost all my settings!

I made a copy of the original db folder and tried to restore from the subsonic.backup but I had no luck, it seems it is also corrupted!

My question is: is there any way I can try to open that subsonic.data and recover my stared songs? That's the only thing I'm realy interested in. I'm quite knowledgeable in Postgresql and sql in general, but I have no idea what backend subsonic uses.

I'm using subsonic 5.3 on a ubuntu 14.04 box

Any help is appreciated

[frnx]

You might be able to use some CLI tools to export the DB to a CSV file, like described here.

Since you're running Ubuntu, the package you're looking for is (I think) hsqldb-utils.

[plablo09]

Great info frnx, will try that!

Would you happen to know which table holds the stared songs info?

[frnx]

You can list all tables with \dt inside the SQLTool shell ; STARRED_MEDIA_FILE is what you're looking for. I don't know how to export the results of a query though, you will have to look into HSQLDB's docs.

Code: Select all

[subsonic@xxxx ~]$ java -jar /usr/share/java/sqltool.jar subsonic
SqlTool v. 5337.
...
sql> \dt
TABLE_SCHEM  TABLE_NAME
-----------  -------------------
...
PUBLIC       MEDIA_FILE
...
PUBLIC       STARRED_ALBUM
PUBLIC       STARRED_ARTIST
PUBLIC       STARRED_MEDIA_FILE
...
PUBLIC       USER_RATING
...

Fetched 35 rows.
sql> select * from starred_media_file limit 10;
ID  MEDIA_FILE_ID  USERNAME  CREATED
---  -------------  --------  -----------------------
616          14126  *        2015-08-15 18:26:55.353
617          14127  *        2015-08-15 18:26:56.792
624           8491  *        2015-08-15 18:27:43.536
625           8493  *        2015-08-15 18:27:44.92
626           8494  *        2015-08-15 18:27:46.548
627           5519  *        2015-08-15 18:28:01.971
628           5520  *        2015-08-15 18:28:03.497
629           5521  *        2015-08-15 18:28:07.709
630           5522  *        2015-08-15 18:28:09.588
631           5523  *        2015-08-15 18:28:11.802

Fetched 10 rows.
sql> select * from starred_media_file join media_file on media_file_id = media_file.id limit 10;
...


[plablo09]

Sadly, I think everything is lost

I can login to the database but it seems to be empty

Code: Select all

java -jar /usr/share/java/hsqldbutil.jar subsonic_db
sql> \dt
TABLE_SCHEM  TABLE_NAME
-----------  ----------

Fetched 0 rows.


The .data file is 250M though, but all structure seems to be lost and I can't seem to find a way to recover from current state.
Also the ,script and log files are empty! son no recovery chance through those.

I'll keep on looking for a while, but itś not looking god

[frnx]

Sorry to hear that...

I'm interested in updates if you do manage to find a solution! I had similar troubles once and didn't manage to recover anything out of the borked db, only got saved because I had daily filesystem snapshots.

[insann]

I would like to know some things about you're issues !

Backing up the DB is great but what do you guys really need in the DB ?
Playlists ?
Users ?
Rights ?
Settings ?

Because for all the pictures and others stuff like this,
with a new DB you only have to scan and everything is nice and clean !

I only backup some things : Users settings, Playlists, ...(some other little things )
and when i need to reintroduce those settings i use the db.view page and copy paste the lines in it and done everything is fine !

Insann

[plablo09]

Hello insann!

I'm not sure I understand you question. In my case the issue was that both, the original subsonic.data and subsonic.backup where broken! So I didn't have a chance to recover (or use the db.view at all), my only option was a fresh start

But, all things considered, the important things in a backup (at least for me) would be the stared songs/albums, playlists and user settings (privileges, passwords and that kind of stuff).

And of course, better documentation on how to restore the database from a backup!

Cheers

[insann]

Yeah ok i got it,
well the user settings are in the subsonic.script file, so its easy to manage it !
for the playlists you may need to make backups before every upgrade or master changes !
for the rated /top songs i don't know but i think that is the difficult part !
you maybe need to search in the subosnic.data file like you sayd !
but i don't know much in this area i only know how to keep the settings safe !

So to migrate to a new fresh install but keep settings you need to save the subsonic script file, and the playlists files !
then you can restore them by importing the playlists and using the db page to add the user settings !

i'll post a little link to a little topic about managing user settings

INsann

[plablo09]

Well, I didn't got to fiddle much with mydatabase, it seems it got corrupted beyond repair. Even the backup and script were corrupted!

For now, I'm making daily snapshots of the whole subsonic folder, hoping that'll be enough the next time the database gets corrupted (also, really hoping it doesn't happen again)

Cheers!