Transferring Users from old DB -> New DB

[BrianDelShasta]

Has anyone successfully transferred users from one installation to another? I've been upgrading since like 2.x and I'd like to finally start over clean--but I want to keep all my user accounts and passwords. I don't care about anything else like stats, players, etc.

Is this possible?

[BKKKPewsey]

My 4.6 to 4.7 upgrade seemed to transfer users players and virtually everything else without problems.
The only issues I had was problem with top rated "locking up" SS for 5 minutes (now fixed)
and it wouldn't import any of my 4.6 playlists.
Oh! and the 12 hour initial scan

[BrianDelShasta]

My mistake, I meant manually transfer database user tables. I'd like to do a fresh install and manually export user data and import to the fresh database.

[hakko]

This is a question that get asked sometimes. Since it has such a good title, I'd suggest keeping just the original post, and add this answer for future reference (if it works):

To do this, log in to your old Subsonic setup. Go to http://_yourserver_/db.view.

Type this, press OK and copy the output:

Code: Select all

select 'insert into user(username, password, bytes_streamed, bytes_downloaded, bytes_uploaded, ldap_authenticated, email) values (''' + username + ''',''' + password + ''',''' + bytes_streamed + ''',''' + bytes_downloaded + ''',''' + bytes_uploaded + ''',''' + ldap_authenticated + ''',''' + email  + ''');' from user where username != 'admin'

Then type this, press OK and copy the output:

Code: Select all

select 'insert into user_role(username, role_id) values (''' + username + ''',''' + role_id + ''');' from user_role where username != 'admin'

Then type this, press OK and copy the output:

Code: Select all

select 'insert into user_settings(USERNAME, LOCALE, THEME_ID, FINAL_VERSION_NOTIFICATION, BETA_VERSION_NOTIFICATION, MAIN_CAPTION_CUTOFF, MAIN_TRACK_NUMBER, MAIN_ARTIST, MAIN_ALBUM, MAIN_GENRE, MAIN_YEAR, MAIN_BIT_RATE, MAIN_DURATION, MAIN_FORMAT, MAIN_FILE_SIZE, PLAYLIST_CAPTION_CUTOFF, PLAYLIST_TRACK_NUMBER, PLAYLIST_ARTIST, PLAYLIST_ALBUM, PLAYLIST_GENRE, PLAYLIST_YEAR, PLAYLIST_BIT_RATE, PLAYLIST_DURATION, PLAYLIST_FORMAT, PLAYLIST_FILE_SIZE, LAST_FM_ENABLED, LAST_FM_USERNAME, LAST_FM_PASSWORD, TRANSCODE_SCHEME, SHOW_NOW_PLAYING, SELECTED_MUSIC_FOLDER_ID, PARTY_MODE_ENABLED, NOW_PLAYING_ALLOWED, WEB_PLAYER_DEFAULT, AVATAR_SCHEME, SYSTEM_AVATAR_ID, CHANGED, SHOW_CHAT) values (''' + USERNAME + ''',' + ifnull('''' +LOCALE+'''', 'NULL') +',' + ifnull('''' +THEME_ID+'''', 'NULL') +',' + ifnull('''' +FINAL_VERSION_NOTIFICATION+'''', 'NULL') +',' + ifnull('''' +BETA_VERSION_NOTIFICATION+'''', 'NULL') +',' + ifnull('''' +MAIN_CAPTION_CUTOFF+'''', 'NULL') +',' + ifnull('''' +MAIN_TRACK_NUMBER+'''', 'NULL') +',' + ifnull('''' +MAIN_ARTIST+'''', 'NULL') +',' + ifnull('''' +MAIN_ALBUM+'''', 'NULL') +',' + ifnull('''' +MAIN_GENRE+'''', 'NULL') +',' + ifnull('''' +MAIN_YEAR+'''', 'NULL') +',' + ifnull('''' +MAIN_BIT_RATE+'''', 'NULL') +',' + ifnull('''' +MAIN_DURATION+'''', 'NULL') +',' + ifnull('''' +MAIN_FORMAT+'''', 'NULL') +',' + ifnull('''' +MAIN_FILE_SIZE+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_CAPTION_CUTOFF+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_TRACK_NUMBER+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_ARTIST+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_ALBUM+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_GENRE+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_YEAR+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_BIT_RATE+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_DURATION+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_FORMAT+'''', 'NULL') +',' + ifnull('''' +PLAYLIST_FILE_SIZE+'''', 'NULL') +',' + ifnull('''' +LAST_FM_ENABLED+'''', 'NULL') +',' + ifnull('''' +LAST_FM_USERNAME+'''', 'NULL') +',' + ifnull('''' +LAST_FM_PASSWORD+'''', 'NULL') +',' + ifnull('''' +TRANSCODE_SCHEME+'''', 'NULL') +',' + ifnull('''' +SHOW_NOW_PLAYING+'''', 'NULL') +',' + ifnull('''' +SELECTED_MUSIC_FOLDER_ID+'''', 'NULL') +',' + ifnull('''' +PARTY_MODE_ENABLED+'''', 'NULL') +',' + ifnull('''' +NOW_PLAYING_ALLOWED+'''', 'NULL') +',' + ifnull('''' +WEB_PLAYER_DEFAULT+'''', 'NULL') +',' + ifnull('''' +AVATAR_SCHEME+'''', 'NULL') +',' + ifnull('''' +SYSTEM_AVATAR_ID+'''', 'NULL') +',' + ifnull('''' +CHANGED+'''', 'NULL') +',' + ifnull('''' +SHOW_CHAT+'''', 'NULL') +');' from user_settings where username != 'admin'

Make a backup of your settings folder, and remove it to start off with a fresh database.


Install your new Subsonic version, and go to db.view again.

Type in the output from the previous three commands and press OK. Your users and their roles should now have been copied.

[BrianDelShasta]

Oh wow, great! I was just coming back on here to say I manually copied over a few users by stumbling my way through some sql. Next I was going to work on a more automated solution, but you've already presented it. The only thing I could add would be that the user_settings table might be useful to transfer as well.

Thanks!

[hakko]

I updated my previous post with a command to copy user settings aswell. Do you wanna try it out?

[BrianDelShasta]

I noticed that any row that has a null column entry is omitted entirely from the results of the query performed on the original database. For example, running the query on the 'user' table did not return results for those users that had no email specified. Once I added an email to each user that didn't already have one, the results returned for all users. No big deal.

However, for the user settings table there are a significant amount of columns in each row that are not populated, at least for my users. In my case it was quicker to manually update each user row.

[hakko]

You can also use the same technique (ifnull) that I used in the sql command that copies table user_settings, if you have a lot of users and don't wanna do it manually.

[Nico]

if you have blank email addresses for users you can replace step 1 with this query;

Code: Select all

select 'insert into user(username, password, bytes_streamed, bytes_downloaded, bytes_uploaded, ldap_authenticated, email) values (''' + username + ''',''' + password + ''',''' + bytes_streamed + ''',''' + bytes_downloaded + ''',''' + bytes_uploaded + ''',''' + ldap_authenticated + ''',''' + NVL(email,'Notset@nothing.com') + ''');'
from user
where username != 'admin'
;

Thanks hakko, this really helped me

[andvaranaut]

Just chiming in to say a huge THANK YOU to hakko for that useful tip. My database had become nastily corrupted for some misterious reason and there was no way I was able to repair it, so I decided to move away my db folder. That did the trick, but I had a lot of users in my other Subsonic installation, and this has saved me from having to recreate them all. Brilliant!

A few comments:

• I found an error in the middle of the output of the second command, the offending bit being a role for user 'guest'. Manually deleting that line, or (I guess) adding " and username != 'guest' " to the end of the command should be enough. This took me a bit to figure out, so I hope it's useful for somebody else
• Not that it is too important, but I guess that with a REPLACE statement instead of INSERT in the second and third commands that kind of problem might be solved - and (again, I guess) it would also allow the admin settings to be carried over.
• Is there a document with the database structure of Subsonic anywhere around? I would much rather have tinkered a bit with the tables to see whether emptying them solved the problem rather than starting from a clean database, since I have lost quite a few comments in the ordeal. (In the bright side, being able to carry over the users qualifies as "good enough" in my book, however)

Thank you again!

[hakko]

Actually, jonnymnemo posted a much better solution than mine for this situation here: viewtopic.php?f=6&t=10338

So if anyone runs into trouble with corrupted databases again, try that first! (or stop relying on hsqldb, I've seen too many error reports on the forum to trust it)

Good that your situation got sorted out, though.