Storage engine change required with the release of Jamf Pro 10.10 for On-Premise Customers

[Fink]

Effective in the upcoming release of Jamf Pro 10.10.0, all Jamf Pro MySQL databases will require the use of the InnoDB database storage engine. Previous versions of Jamf Pro are using MyISAM by default. Prior to upgrading to Jamf Pro 10.10.0 or later, you must convert your database storage engine from MyISAM to InnoDB.

The database can be converted using the Jamf Pro Server Tools Command Line Interface (CLI), which can be used with Jamf Pro 10.0.0 or later. For instructions on how to use the CLI tool convert the database to InnoDB, please reference this Knowledge Base article: Converting the MySQL Database Storage Engine from MyISAM to InnoDB Using the Jamf Pro Server Tools C....

For additional assistance with this conversion, please contact your Customer Success Manager, by selecting your username in the top right and then My Account Team while logged into Jamf Nation.

If you run into issues with the process of conversion, please contact our Technical Support team by selecting the Support tab in the menu bar on Jamf Nation.

[bradtchapman]

This is a very significant change. The performance improvement is totally worth it.

That being said, there are universal laws which dictate that someone, somewhere, will make a mistake or ignore the iceberg right ahead. Therefore, it's important to know if Jamf has planned any safeguards or contingencies.

1. What is the minimum version of Jamf required to upgrade to 10.10?
2. Is it possible to detect the table engine prior to upgrading? (My gut says 'no.')
3. If MySQL is on the same box, can the installer try the conversion? (Assuming it's already InnoDB, nothing happens)
4. Can you make it an install requirement that a jamf-pro config has been set, and test during pre-install?
5. Will Jamf Pro 'suspend' itself on startup if the table engine is still MyISAM?
6. What is the remediation process?

Thank you for your efforts and your time!

[NCPLANK]

I'm not certain if there's a way to determine every table engine at once. But, you can verify individual tables.

SHOW TABLE STATUS WHERE Name = "Computers";

In the output, you'll see "Name", "Engine", etc that correspond with the table name, table engine, and the other table attributes.
It will look something like this.

| Name | Engine | etc... | etc... |
| etc... | etc... |
| computers | InnoDB | etc... | etc... |
| etc... | etc...|

[ryan_ball]

Show the storage engine for all tables:

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'jamfsoftware';

The conversion should be done before upgrade to 10.10. Contact Jamf to schedule a time to where they can walk you through the conversion using mysql commands if you think you need help. It could take about 2 hours to convert.

[dan-snelson]

You can double-check the database table flavors via a Jamf Pro Summary:
Settings > Jamf Pro Information > Jamf Pro Summary > Database > Check All > Create

    MyISAM Tables.............. 0
    InnoDB Tables ..............428

[bradtchapman]

@dan-snelson, are you running 10.9.0? I don't see that information in 10.8.0. Checking all items under database only shows table row counts and table sizes.

[dan-snelson]

@bradtchapman We're running 10.8.0-t1539715549 on-prem; ours is displayed right before Table row counts (or right before Activation Code for a full report).

[Taylor_Armstron]

For what it is worth, running 10.8 here, and a search gave me the (bad? good?) news:

Jamf Pro Summary: Created 2019-01-24 02:35:41 by Taylor.Armstrong Installed Version 10.8.0-t1539715549 ---------------------------------------------------------------------------------- ........ ........ MyISAM Tables 402 InnoDB Tables .............. 26

I was planning on upgrading to 10.9 this weekend, but will likely hold off for 10.10 at this point.

[wmateo]

@Fink Question, can this be run on JAMF Pro 10.9 without affecting functionality? will my JSS be impacted for normal operations with storage engine changed? Since I don't own the DB server, I want to proactively have this changed before I upgrade the JSS but not at the same exact time. Or will I have to go 10.10 immediately after I do this?

[dan-snelson]

@wmateo I recommend you reach out to your Jamf Buddy to find out for certain; in our case, we converted to InnoDB before upgrading to Jamf Pro 10.8.0.

[wmateo]

@dan-snelson Thanks for that. what I ask is will it break your JSS immediately and you are forced to upgrade right away. I understand 10.10 will not work without the change, but will 10.9 work with it is my question.

[ryan_ball]

@wmateo Jamf Pro 10.9 is fully functional without the innoDB conversion. See the 10.9 release notes:
http://docs.jamf.com/10.9.0/jamf-pro/release-notes/What's_Changed.html

InnoDB Requirement for MySQL Starting with Jamf Pro 10.10.0, the InnoDB storage engine for MySQL will be required.

[Fink]

Hi Everyone,

Thank you very much for the discussion here. I am happy to answer the questions that have been posed.

Jamf Pro 10.0 and later support InnoDB, and if you are running 10.6 or later you have a few InnoDB tables in your database already.

As for incremental upgrades. If you are moving from anything pre 10.0, I would recommend reaching out to your Customer Success Manager to ensure incremental upgrades are done if needed.

How do I know if I need to convert? Thank you everyone in the community that jumped in to answer this question! You all are truly amazing. If you use the Jamf Pro Summary you will need to see that all of your tables are using InnoDB, or you will need to run through the conversion.

The installer will not be able to detect if the conversion has occurred whether on the same box or not. We have sent an email to all Jamf Pro contacts with a technical designation as well as created this post to generate awareness. We discussed this quite a bit and decided for a change of this size we did want interaction from the administrator.

If a Jamf Pro administrator does not convert to InnoDB before upgrading to version 10.10 they will run into an error message that provides a link to the KB for converting to InnoDB. Using the Jamf Pro Server tools an administrator can convert the DB to InnoDB and restart Tomcat and be back up and running again.

If this happens our technical delivery team is more than happy to assist. Contact Technical Support

If you have any further questions at all please don't hesitate to reach out to your Customer Success Manager My Account Team

We are very excited for this change as well!

[L-plateAdmin]

If it helps anyone still on 9.* you need to upgrade no higher than 10.1.0 before then upgrading again. we did this on our way from 9.98 to 10.5.0

[nwiseman]

So...my Jamf Summary doesn't show anything at all about MyISAM or InnoDB....we're upgrading to 10.9 this weekend so i'll check after that???

[lhnguyen1]

I'm on 10.9 and don't see anything about MyISAM or InnoDB either.

[ryan_ball]

@nwiseman and @lhnguyen1 you can do what I posted above.

In MySQL run the following (change jamfsoftware to your database name):

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'jamfsoftware';

[jared_f]

Hi All,

I upgraded to 10.10 and did my DB conversion to InnoDB and it went pretty smoothly. I ran into two minor issues and wanted to make everyone aware.

I was using the new Jamf Pro Server tools app and for some reason I could not connect to the database and make a backup before upgrading. I knew the database username and password so I knew it wasn't an issue of forgetting it. Apparently that app doesn't start functioning until you convert your DB and/or upgrade to 10.10 (we did it at the same time so I can't tell you which it is). This may be known, but I haven't seen this anywhere on Jamf Nation -- granted, I haven't read JN a lot recently. To get around this, I used the database utility which functioned as expected. NOTE: Take periodic backups before the conversion, after the conversion, and before upgrading to 10.10!

Second, after updating to 10.10 (following the standard procedure), the same utility (Jamf Pro Server tools app) was showing the following regarding InnoDB:

Knowing we converted I kept hitting refresh and it would not show that we converted. I chatted with Jamf support and ran the following in the MySQL CLI to determine if the database had converted. I was cautious to even start Tomcat or MySQL without verifying this.

select count(*),engine from information_schema.tables where tables.table_schema="jamfsoftware" group by engine;

It confirmed we indeed had converted:

After that, I started MySQL and Tomcat and the Jamf Pro web app confirmed to me that the 10.10 migration was successful. No reports of any errors to it seems all went well.

Thanks Zachary and Lawrence in support for helping me out.
Jared

[diradmin]

@jared_f What version of the Jamf Pro Server Tools binary are you running? Per Jamf Support, 2.2.7 is supposed to address the issue of incorrectly reporting that an InnoDB conversion is pending.

Changelog - Jamf Pro Server Tools

[jared_f]

@stephen.perry-GS Thanks for sending me that. I thought the tools updated every time Jamf Pro got upgraded. Just executed the powershell command to upgrade it now. Thank you!!