[HowTo] Database broken : Import old settings

[jonnymnemo]

I posted thit tip in another thread, but I tought it should belong here.
Sometimes, especially when you upgrade or tweak subsonic, you can break the database.

Before upgrading, always backup your subsonic folder.

If the database breaks, you can recover some settings :

Users (with their password) and their settings:

They are kept in the subsonic.script file. (steps 1 to 3 are only needed if you didn't remove the subsonic folder before reinstalling)

1. stop subsonic service
2. rename the subsonic/db directory
3. start subsonic service : a new database is created.

4. open the old subsonic.script file (in the directory you renamed)
5. select the lines you want to keep (INSERT INTO MUSIC_FOLDER VALUES... INSERT INTO USER VALUES... INSERT INTO USER_ROLE VALUES ... INSERT INTO USER_SETTINGS VALUES ...)
6. in your subsonic webpage, replace "index.view" with "db.view"
7. paste the selected lines

Now you should have your old user settings

Starred songs or albums :

You can't import the starred albums or songs from subsonic.script, because when you recreate the database, each song will have a new ID !!!

If you want to backup these BEFORE upgrading, there is a workaround :

- create a playlist with all your starred songs
- export the playlist

When you have to recreate the database, you will just have to import this playlist and star each song.

Same thing for the starred albums : you create a separate playlist, and put one song of each album, then star the album !

Playlists :

Old playlists (up to 4.6) are stored in a folder, so there's no problem to recover them.
New playlists (since 4.7beta1) are stored in the database. If you want to keep them just in case, you have to export them.


If you have some more tips for other settings, don't hesitate to add them !

[Boutros]

God bless you for this - upgrading to 4.7 has turned into a nightmare (I've since given up and reverted to 4.6), so I've had to do fresh installs 4 times in the last 2 weeks. I found this post after the first one, so thankfully didn't have to send out yet another shame-faced email to all of my users telling them I'd reset their passwords...

[alienbob]

Thanks!

[tmwsiy]

I am trying to do this and recover all my users and passwords.

I copied just the lines Insert Into User Values for all the users.

When I switch to db.view, I see "Database Query" and paste those lines in.

I then get this error:

Violation of unique constraint $$: duplicate value(s) for column(s) $$: SYS_PK_50


Any ideas?

So frustrating. This is the fourth or fifth time my DB somehow got corrupted in the last couple months and so grossly inconvenient to keep recreating it.

[jonnymnemo]

could you put an example of the lines you want to paste ? (either in PM, or modify the password and mail address)

[tmwsiy]

thanks for the help:


INSERT INTO USER VALUES('Ace','enc:xx179736f6e',1903433102,799000441,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('Grant','enc:xx56166',0,0,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('PhilH','enc:7xx656261',7443256914,8751638948,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('admin','enc:746dxx77369793939',0,0,0,FALSE,'parkerjh@gmail.com')
INSERT INTO USER VALUES('bertolet','enc:70686xx6879333237',1450683886,692836436,0,FALSE,'xx@gmail. com')
INSERT INTO USER VALUES('chop','enc:676c7573xxb6f',4773974930,0,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('chrisa','enc:65737xx79',142589322,54390745,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('diggdugg','enc:6b656xxc6572',0,0,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('driver','enc:666f726xx696e',48310448,2679238273,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('ericwyman','enc:7369xx706c65',239190644,0,0,FALSE,'xx@Gmail.com')
INSERT INTO USER VALUES('geoff','enc:706179xxf6e',686850024,10908587021,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('guest','enc:6f457359xx61305874347a6c5058634d5a445a35635267326f7a68356e39',969821091,0,0,FALSE,NULL)
INSERT INTO USER VALUES('jam','enc:6c697374',xx491812,41402593368,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('jeremyw','enc:7068697xx8',664074032,0,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('john','enc:6d65746xx72',8401606092,42106161,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('parker','enc:746d7773xx793939',15852019312,655360,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('phillip','enc:68616e6xx8',504520506,0,0,FALSE,'xx@Gmail.com')
INSERT INTO USER VALUES('scott','enc:6869xx64656e',0,0,0,FALSE,'xx@gmail.com')
INSERT INTO USER VALUES('tim','enc:776164xx5',2336047091,3658074886,0,FALSE,'xx@gmail.com')

[tmwsiy]

And I should add, I tried simply copying the same lines from the backup script file to the new DB file.
But then when I tried accessing Subsonic I got an HTTP error with something about Jetty too.

[jonnymnemo]

Are you on subsonic or madsonic ?
If you are on madsonic, I found the problem : simply add a ",0" defore the ")". (0 for user group)

[tmwsiy]

Subsonic.
I'll have to Google Madsonic- never heard of it.

thanks.

[jonnymnemo]

madsonic is a fork of subsonic. You can find it in the "Mods" section of this forum.

Could you put this line in db.view, and copy-paste the result, including the column names ? (as usual, modify the mail addresses ans passwords)

Code: Select all

SELECT * FROM user

[Pessoa]

when I past the lines into db.index I get the following error:

Violation of unique constraint $$: duplicate value(s) for column(s) $$: SYS_PK_48

any ideas? the last 2 digits change when I try pasting in different lines

[hakko]

Some values need to be unique in the database. For example, if you've set up a user called "admin", you can't paste another user called "admin". If you've added a media folder that's been assigned id 3, you can't paste another media folder that also has id 3 etc. Best thing is probably not to start configuring your newly installed server (adding users etc) before attempting to use this tutorial.

[Pessoa]

I deleted everything and am starting out fresh and still get that same error. I've tried omitting the INSERT INTO USER VALUES('admin' line but I still get the error. If I'm just starting out from scratch are there any other user, user role or music folder lines that I need to omit to avoid getting this error? thank you for the help

[Pessoa]

hmmmm, I just notice I still get the error even when just pasting one line. any ideas?

here's the error again:
Violation of unique constraint $$: duplicate value(s) for column(s) $$: SYS_PK_50

[hakko]

Best way to understand what's going wrong is doing it in steps. Start with the users. If you can't run the line INSERT INTO USER VALUES('..., then check what's already in that table by running select * from user in the db.view text box.

Knowing what's already in the database + knowing what you're trying to insert, plus the knowledge that the error message means that you're trying to insert duplicate identical values in a column that needs to be unique, should be enough information to solve this.