Error updating the database schema

[dpertschi]

Doing a dry run in the lab, I installed 9.65 and restored our 8.73 database. Not too surprisingly at first launch of the web interface I'm getting a Startup Suspended, error updating the database schema.

Being 24 updates behind, should I consider an incremental update or two prior to the current release? 9.2 --> 9.4 --> 9.65 ?

[mpermann]

I don't think you can restore an 8.73 database to a 9.65 server. I think if you install JSS 8.73 on your test server, then load in your 8.73 database then upgrade to 9.65 it will work. Hopefully others will chime in to correct me if I am wrong.

[were_wulff · Accepted solution]

@mpermann @dpertschi

That is one of the recommended ways, yes.

Incremental upgrades typically aren't necessary unless we're coming from a version of 8 that is older than 8.7, but if you're more comfortable doing incremental upgrades, it shouldn't cause any issues.

You can also install 9.65 fresh and restore an 8.73 database, as long as you don't open the 9.65 webapp prior to the restore.

If you initialize the 9.65 JSS first by going to your JSS' URL, it creates all of the tables necessary for a new database during that process, and when you try to restore an 8.x database over that, it will create schema errors. This happens because it sees that there is an 8.x database and attempts to upgrade it, and when it hits tables that already exist it will fail with that error.

Just as an example: In 8.x the table for mobile devices is called iphones, in 9.x it's mobile_devices.
If you restore an 8.x database over a 9.x database, it doesn't overwrite the mobile_devices table because that table didn't actually exist in 8.x, but it does put the iphones table in there. The next time you open the webapp, it detects that there is an 8.x database and tries to rename and convert that iphones table to the two new 9.x tables; that table already exists in this scenario, and it cannot do that, so we get an error in the logs stating the mobile_devices table already exists and the upgrade fails with a Database Schema Error.

There are a couple dozen tables this can happen on, and is the primary reason we don't want to restore an 8.x database over a 9.x install that has already had the webapp started.

In this particular case, it can be cleared up by doing the following:

1. Make sure you have a good database backup first. If you're not 100% sure you have a good backup from before the failed upgrade, do not proceed and call your Technical Account Manager.
2. Log in to MySQL as the root user.
3. Check again to make sure you still have a good backup from before the failed upgrade; this really is important as the next step will get rid of the entire existing jamfsoftware database. Once it's gone, it's gone.
4. Run the following command: drop database jamfsoftware;
5. Run the following command: create database jamfsoftware;
6. Do not go to your JSS URL. Doing so will set up the table structure for a 9.65 database, and we don't want that to happen yet.
7. Restore your 8.73 database.
8. Once the restore is finished, go to your JSS URL and it should go through the upgrade without schema errors.

If it fails again, or if you're not comfortable with/are unsure about any of the steps above, please grab a copy of your JAMFSoftwareServer.log and get in touch with your Technical Account Manager either by giving them a call, by sending an e-mail to support@jamfsoftware.com, or by using the My Support section of JAMF Nation.

Thanks!

Amanda Wulff
JAMF Software Support

[dpertschi]

@amanda.wulff

You can also install 9.65 fresh and restore an 8.73 database, as long as you don't open the 9.65 webapp prior to the restore.

Nice! Super helpful as always. I'll give that a go soon, thank you.

[were_wulff]

@dpertschi

Let us know how it goes if you get a chance!

Just to make sure there's no confusion: If we're going to reuse the same server as-is and not remove/reinstall MySQL or rebuild the VM, you'll need to go in to MySQL and drop the existing database, otherwise, even if we re-run the 9.65 installer and don't go to the JSS URL, the 9.64 database schema with the tables that already exist (and, thus, can't be updated) will still be there and we'll still likely run in to schema errors.

Installing 9.65 & restoring a 8.7x database will only work if we don't open the webapp in 9.65 first; if it's already been opened, we'll need to go through those steps to drop and re-create the database.

Thanks!
Amanda Wulff
JAMF Software Support

[dpertschi]

Bingo! DB updated without obvious error. @amanda.wulff you rule!