Manually Converting Jamf Pro Tables From MyISAM to InnoDB

Standard

With Jamf Pro 10.6.0, Jamf changed the default storage engine from MyISAM to InnoDB.

Jamf also released a tool to convert the tables with the release of 10.7.0.

However, as the Jamf supplied tools are not available currently available as a standalone download for those of us that do manual installations & as we host our datajar.mobi instances on Kubernetes… we went the manual route.

Below is how we performed these conversions.

Why Change?

Well, MyISAM was the default storage engine of MySQL pre-5.5.

MyISAM offers fast read times, but slower writes than InnoDB & MyISAM can only do full table-level locking, whereas InnoDB has row-level locking. This can allow for backups to be run without impacting Jamf Pro usage (although this is not supported by Jamf).

Also, the below note is from Jamf’s 10.6.0 release notes:

InnoDB is now the default storage engine for fresh installs of the MySQL database and any new database tables. InnoDB provides a number of benefits over MyISAM, including:

• Improved stability
• Support for transactions
• Better performance
• Improved data management for large deployments<

InnoDB will be required in an upcoming version of Jamf Pro, however, you can convert your database from MyISAM to InnoDB any time before then.

InnoDB is now the default storage engine for fresh installs of the MySQL database and any new database tables. InnoDB provides a number of benefits over MyISAM, including:

• Improved stability
• Support for transactions
• Better performance
• Improved data management for large deployments

InnoDB will be required in an upcoming version of Jamf Pro, however, you can convert your database from MyISAM to InnoDB any time before then.

So the last line advises that InnoDB will be required at some point, so why not make the change?

More information can be found at:

etc…

Jamf Pro Server Tools Command-Line Interface

If you leverage the Jamf installers for installing Jamf Pro, its probably best you stick to the Jamf Pro Server Tools Command-Line Interface that ships with Jamf Pro 10.7.0.

Again, as mentioned above, as we have a non-standard environment & 100ish hosted datajar.mobi instances, we went the manual route.

Pre-Change

If your Jamf Pro has been around since before 10.6.0, you should see something like the below near the top of a Jamf Pro summary (just above the “Activation Code” section):

MyISAM Tables                417
InnoDB Tables .............. 4

So, you will already have some InnoDB tables.

It’s also pertinent to now point out that there is a Jamf KB, which under the “Configure MySQL” section details some changes needed for InnoDB.

Prod, on a Friday before holiday

Oh, & if you’re looking to try the below please test on a test DB.

Ideally this would be a restored copy of your production DB.

And, never make this change in Prod, on a Friday before you go on holiday!

Finding The MyISAM Tables

The following, which I think was taken from Stack Exchange, needs to be ran on the MySQL host.

Note, this script at this stage is not making changes to your DB. But reading which tables are MyISAM then outputting a line if the table is MyISAM in the form of an MySQL statements which can be used later.

To use the below command, replace <account name> & <database name> as needed, it may be possible to also run this remotely using the -h flag to specify a remote MySQL host (but I have not attempted this).

mysql -u <account name> -p <database name> -e "show table status where Engine='MyISAM';" | awk 'NR>1 {print "ALTER TABLE "$1" ENGINE = InnoDB;"}'

The above, will output something like the below:

ALTER TABLE account_invitations ENGINE = InnoDB;
ALTER TABLE activation_code ENGINE = InnoDB;
ALTER TABLE adobe_payloads ENGINE = InnoDB;
ALTER TABLE advanced_searches ENGINE = InnoDB;
ALTER TABLE airplay_permission_mappings ENGINE = InnoDB;
ALTER TABLE airplay_permissions ENGINE = InnoDB;
ALTER TABLE android_device_details ENGINE = InnoDB;
ALTER TABLE app_lock_assignments ENGINE = InnoDB;
ALTER TABLE app_push_notification_queue ENGINE = InnoDB;
ALTER TABLE app_updater_settings ENGINE = InnoDB;
ALTER TABLE apple_school_manager_active_syncs ENGINE = InnoDB;
ALTER TABLE apple_school_manager_instance ENGINE = InnoDB;
ALTER TABLE apple_school_manager_matcher ENGINE = InnoDB;
ALTER TABLE apple_school_manager_sync_change_requests ENGINE = InnoDB;
ALTER TABLE apple_school_manager_sync_records ENGINE = InnoDB;
<snip>

With a line per table which is MyISAM being outputted.

So, for the table shown in the “Pre-Change”, you would have 417 MySQL statements of output.

Copying The Output

One lovely little tip, picked up from scriptingosx’s great talk at MacADUK this year, is to press:

Shift-Command-A

This will then select the text your previous Terminal prompt & the current one (at least on macOS), therefore selecting the output from the command.

Now you can simply copy the output.

Another option is mentioned by Graham in the comments below, whereby you write the output to a file to ingest.

Converting The Tables

With the command output copied, (& making sure that this isn’t being done in Prod, on a Friday before you go on holiday), stop each of Jamf Pro Web Apps which are using the DB we’re about to modify & then connect to your MySQL host & switch to your Jamf Pro’s DB via the “USE” command.

I will say, if that last part does not make sense, please do not proceed! (but there are some examples at the beginning of this Jamf KB.)

Once connected, you can run the 400+ MySQL statements by pasting the above output into the MySQL command line (!!!).

Each MySQL statement will then ran one by one, with output like the below:

mysql> ALTER TABLE vpp_licenses ENGINE = InnoDB;
Query OK, 145 rows affected (0.04 sec)
Records: 145  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE vpp_subscriptions ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mobile_device_group_memberships ENGINE = InnoDB;
policyQuery OK, 3305 rows affected (0.13 sec)
Records: 3305  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mobile_device_groups ENGINE = InnoDB;
Query OK, 16 rows affected (0.11 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mobile_device_inhouse_app_validator_settings ENGINE = InnoDB;
Query OK, 1 row affected (0.09 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mobile_device_installed_applications ENGINE = InnoDB;
Query OK, 263952 rows affected (3.86 sec)
Records: 263952  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mobile_device_installed_certificates ENGINE = InnoDB;
Query OK, 54554 rows affected (2.57 sec)
Records: 54554  Duplicates: 0  Warnings: 0

<snip>

It is advisable to run any log flushing etc before hand to lessen the rows & to speed things up.

After running the above, restart each of Jamf Pro Web Apps which are using the DB & check your Jamf Pro summary.

It should now look something like the below:

MyISAM Tables               0
InnoDB Tables .............. 421

Erm, you sure?

So, I’ll admit that the above seems odd & it’s probably not the correct way to make this change.

But, we followed the above when Jamf Pro 10.6.0 was in beta, then when we updated some instances to Jamf Pro 10.6.0 some weeks ago we also performed the above pre-10.6.0 installation.

We have now ran the above across all of our 100ish datajar.mobi instances & all seems well.

But I’d love to be shown some better examples.

6 thoughts on “Manually Converting Jamf Pro Tables From MyISAM to InnoDB

  1. Graham

    To shorten the amount of copy-pasting, just output to a file, then read the file back in:

    “`mysql -u -p -e “show table status where Engine=’MyISAM’;” | awk ‘NR>1 {print “ALTER TABLE “$1″ ENGINE = InnoDB;”}’ > mysql_commands.sql“`

    “`mysql -u -p < mysql_commands.sql“`

  2. Daniel Shane

    This is a bash oneliner that will convert the Database, NOTE: you will have to type your password in twice, or change the -p for -p”password”

    mysql -u root -p database_name_to_convert -e “show table status where Engine=’MyISAM’;” | awk ‘NR>1 {print “ALTER TABLE “$1″ ENGINE = InnoDB;”}’ | mysql -u root -p database_name_to_convert

  3. Daniel Shane

    Also As long as the engine is innodb you can live backup the database without taking anything down !

    mysqldump -u root -p –single-transaction -q database_to_backup >backup.sql

Leave a Reply to GrahamCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.