Transferring Users from old DB -> New DB

Need help? Post your questions here.

Moderator: moderators

Transferring Users from old DB -> New DB

Postby BrianDelShasta » Thu Jul 05, 2012 11:08 pm

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?
BrianDelShasta
 
Posts: 116
Joined: Tue Jun 03, 2008 4:45 am
Location: USA

Re: Transferring Users from old DB -> New DB

Postby BKKKPewsey » Thu Jul 05, 2012 11:45 pm

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 :lol:
Everyone is entitled to be stupid, Image but some abuse the privilege!

Due to the confusion from too many genres of music, we have decided to put both country music and rap music into the genre of Crap music.
User avatar
BKKKPewsey
 
Posts: 2080
Joined: Mon May 23, 2011 12:16 pm
Location: United Kingdom

Re: Transferring Users from old DB -> New DB

Postby BrianDelShasta » Fri Jul 06, 2012 12:02 am

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.
BrianDelShasta
 
Posts: 116
Joined: Tue Jun 03, 2008 4:45 am
Location: USA

Re: Transferring Users from old DB -> New DB

Postby hakko » Fri Jul 06, 2012 5:06 am

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.
Last edited by hakko on Fri Jul 06, 2012 7:03 am, edited 1 time in total.
MusicCabinet developer
hakko
 
Posts: 1416
Joined: Tue Apr 17, 2012 7:05 pm
Location: Sweden

Re: Transferring Users from old DB -> New DB

Postby BrianDelShasta » Fri Jul 06, 2012 6:31 am

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!
BrianDelShasta
 
Posts: 116
Joined: Tue Jun 03, 2008 4:45 am
Location: USA

Re: Transferring Users from old DB -> New DB

Postby hakko » Fri Jul 06, 2012 7:03 am

I updated my previous post with a command to copy user settings aswell. Do you wanna try it out?
MusicCabinet developer
hakko
 
Posts: 1416
Joined: Tue Apr 17, 2012 7:05 pm
Location: Sweden

Re: Transferring Users from old DB -> New DB

Postby BrianDelShasta » Sun Jul 08, 2012 5:54 pm

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.
BrianDelShasta
 
Posts: 116
Joined: Tue Jun 03, 2008 4:45 am
Location: USA

Re: Transferring Users from old DB -> New DB

Postby hakko » Sun Jul 08, 2012 6:36 pm

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.
MusicCabinet developer
hakko
 
Posts: 1416
Joined: Tue Apr 17, 2012 7:05 pm
Location: Sweden

Re: Transferring Users from old DB -> New DB

Postby Nico » Mon Jul 09, 2012 2:18 pm

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 :D
Nico
 
Posts: 23
Joined: Sat May 19, 2012 12:14 pm

Re: Transferring Users from old DB -> New DB

Postby andvaranaut » Mon Nov 12, 2012 8:03 pm

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! :D

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!
andvaranaut
 
Posts: 18
Joined: Tue May 15, 2012 8:29 pm

Re: Transferring Users from old DB -> New DB

Postby hakko » Mon Nov 12, 2012 8:08 pm

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.
MusicCabinet developer
hakko
 
Posts: 1416
Joined: Tue Apr 17, 2012 7:05 pm
Location: Sweden


Return to Help

Who is online

Users browsing this forum: No registered users and 6 guests