Upgrade&migrate from standalone to Tomcat (RESOLVED!)

[supra92]

Longtime Subsonic user/donor here, and facing a challenge when upgrading from v3.8beta to v3.8.

Until now, I've run the standalone version with the Jetty engine, on port 8080. However, I'm now wanting to run additional java apps on port 8080 as well, and so am switching over to the Tomcat appserver.

I've successfully gotten v3.8 installed and running, but as expected all of the albums are gone, all the users are gone, and especially all the album comments and album ratings that all my users spent so much time contributing.

Jigsaw's tutorial on how to preserve users whilst upgrading from one standalone version to a newer one has always worked well for me -- but now that I'm migrating to Tomcat/WAR, I'm not sure what needs to be copied from my old standalone installation, over to the /var/lib/tomcat5/webapp/subsonic tree.

Any advice on this one? I really can't stand the idea of losing all those users and album comments, but definitely need to move from standalone Jetty to Tomcat/WAR.

Cheers,
Supra92

[sgx1]

I'm not familiar with HSQLDB, however, I've developed for SQLite, which seems very similar.

I think you should be able to export the data from your existing database (if still online) and create a script to insert that data into the new database.

You can get to the query screen by going to http://<YourSubsonicIP:Port>/db.view

If you run a query for users such as:

Code: Select all

SELECT *
FROM USER

You should have a list you can copy to notepad and can be used to create an INSERT command on the new database. You should be able to do this for each of the tables in the database.

I'm still on the 3.8 beta, so the available tables listed are:

CUSTOM_AVATAR
INTERNET_RADIO
MUSIC_FILE_INFO
MUSIC_FOLDER
PLAYER
PLAYER_TRANSCODING
PODCAST_CHANNEL
PODCAST_EPISODE
ROLE
SYSTEM_AVATAR
TRANSCODING
USER
USER_RATING
USER_ROLE
USER_SETTINGS
VERSION

I think once you have the data, you can INSERT to update the other databases:

Code: Select all

INSERT INTO <TABLE NAME> VALUES ('data',...);

As an example, to update USER:

Code: Select all

INSERT INTO USER VALUES ('<username1>','<password1>','0','0','0','false');
INSERT INTO USER VALUES ('<username2>','<password2>','0','0','0','false');
INSERT INTO USER VALUES ('<username3>','<password3>','0','0','0','false');
INSERT INTO USER VALUES ('<username4>','<password4>','0','0','0','false');

There may be easier ways to accomplish this, but maybe this will help somewhat.

[sgx1]

Actually, I just tested the INSERT command on my database to add a new user, and it appears to have worked fine!

Code: Select all

INSERT INTO USER VALUES ('testusername','enc:654646464654','4654646464656446','4654654646','48949499','false');

I fudged the Streamed, Uploaded and Downloaded fields with random numbers, and the graphs pick up the numbers just like it actually happened.

The USER_SETTINGS table should control all of the tweaks done to each user, so since I didn't insert any new data there, the user doesn't have anything selected on the Settings page.

The password field on the database shows "enc:" with a string of numbers representing the encoded password. I copied the same value from my account to the test user, and I was able to log in as the new user with my password...so that should prevent having to reset passwords as well post-INSERT.

[supra92]

Hmm, I will give this a try! Worst case scenario, I simply wipe it (ie, the new tomcat version install) and try again.

One thing though -- are the users' passwords in cleartext in the database? Securitywise that wouldn't be an ideal practice, but OTOH it would at least make it so that i could recreate the users without having everyone go in and reset/redo their passwords. Ideally, they'll never know any changes were made when they hit the site....

Supra92

[sgx1]

The passwords are encoded. I updated my previous post with information about the password field.

[supra92]

First issue that has cropped up --- for the Tomcat WAR version... attempting to go to http://<YourSubsonicIP:Port>/db.view simply sends me to the subsonic login screen. I've ensured I'm logged in to subsonic as admin (to ensure security isn't the issue), and then go to db.view. Bam, right back at the login page.

Is that a feature specific to the standalone Jetty-based one? Will search around to see if it's hiding under a different name in the Tomcat one --- hopefully it's accessible, otherwise i'd be SOL...

[supra92]

Also, doing more extensive searching, I did find this thread that in theory explains how to do it:

http://forum.subsonic.org/forum/viewtopic.php?t=1635

Apparently, all that's needed is to copy over the subsonic.properties file, plus the db folder.

I tried that, however, and I get this error when I go to the subsonic URL:

-------------------
HTTP Status 404 -

type Status report

message

description The requested resource () is not available.

Apache Tomcat/5.5.23
--------------------


Any idea why that would be happening? Jigsaw mentioned that this worked for him even as he was migrating from tomcat WAR to the jetty standalone, so presumably it should work when going the other direction

Supra92

[supra92]

Update: the issue with the above was linux permissions. The old standalone stuff was owned by root. Chown'd to tomcat, and presto. I also needed to copy over the "thumbs" directory as well.

Indeed, Subsonic now comes up, and all the users/pwds and user stats and album rankings/comments are migrated. Awesome!!


For some reason, however, subsonic can't load the album art in the main frame (ie where the Random or Newest images are). And subsonic continues to try to load this for what seems like an eternity, and yet cannot. In IE, at least i can see the list of artists on the left, and when i click on an artist the albums AND artwork show up on the right. However, Chrome won't even show the artists on the left. Neither browser is able to show the Random album art, and thus both browsers hang in the "loading...." state for awhile.

Looks like we're mostly there, but with one or two bugs hanging around... it's major progress at least!

[supra92]

Resolved the issue of Subsonic hanging on a perpetual "page loading" and not displaying the left pane artist list in Chrome/Firefox...

I mistakenly assumed that by migrating the data/database over, that there would be no need to incur a Reindexing. Wrong! A simple manual reindexing by going to Settings->Search->UpdateSearchIndexNow resolved the issue, and now all the album cover art are visible, album art appears in the main frame (Random), and the Subsonic page now loads in full without hanging.

So.... to recap for those who may want to do the same, re: updating but also migrating from standalone Jetty over to Tomcat/WAR:

1. Install the new Subsonic (.WAR Tomcat module) per the standard instructions.

2. Copy over the subsonic.properties file, the db directory, and the thumbs directory from the old installation's homedir over to the new one's.

3. chmod all of the above items to ensure user 'tomcat' is the owner as per all the other files in the new install

4. Reindex the database by doing Settings->Search->UpdateSearchIndexNow.


Thanks to sgx1 for looking at this with me tonight, and also to Jigsaw (per usual!) as his prior post re: copying over the .properties file & db dir were key to getting this working.

Rockin' with Subsonic once again, via Tomcat!

Cheers,
Supra92

[3R3]

Thanks for the tip! i upgraded just from standalone 3.8 to 3.9 but was stumped at where my license and some of the textboxes content went.

Just copied subsonic.properties (license, some settings) from old install to new one, along with the db folder and also the thumbs folder for added convenience^^

And yeah, the permissions are especially nasty when you think you thought of everything

OT:
dang, subsonic is getting more refined with every release, i love it. especially the new name.subsonic.org feature is great, certain posts pertaining to remote access in the tutorials forum were probably among the most read, and sindre reacted promptly.