Need to delete Mysql rows

[Johnny_Kim]

Hello!

Long story short, my JSS has been collecting inventory way too frequently and my database size is about 35gb. my Mysql column for applications is 31.1gb with over 175 million rows. I've tried everything from doing a backup in hopes that repair would fix the database to following Rich Trouton's site (https://derflounder.wordpress.com/2014/02/01/fixing-caspers-mysql-database-with-mysqlcheck/). This is my last resort!

How can I delete the rows from the tables of application? I've googled but am a bit unclear on this.

Any help would be appreciated!!

[Sonic84]

You should reach out to jamf support on this one. they usually have the commands on-hand for this kind of operation. It's really easy to corrupt your database if you are new to mySQL's CLI.

[Hugonaut]

5 Steps before messing with the mysql stuff

Step 1. Back up your Database

Step 2. Back up your Database again

Step 3. Back up your Database again

Step 4. Back up your Jamf Server Entirely

Step 5. Get a Hot Swap Server Machine ready in case the database gets corrupted, test the machine make sure it works all the same as your live machine, etc.

I made this walkthrough for my team in case something happened when I was gone but this will save you a lot of time if you have a table crash (and is kinda what you were asking about)

In order to find out what is going on gotta visit Server Log.

Located at /library/jss/logs/JAMFSoftwareServer.log

In the log peel back dem lids and find something like this (CMD+F "marked as crashed")

Table './jamfsoftware/applications' is marked as crashed and last (automatic?) repair failed
Caused by: java.sql.SQLException: Table './jamfsoftware/applications' is marked as crashed and last (automatic?) repair failed
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Table './jamfsoftware/applications' is marked as crashed and last (automatic?) repair failed
java.sql.SQLException: Table './jamfsoftware/applications' is marked as crashed and last (automatic?) repair failed
Caused by: org.mariadb.jdbc.internal.util.dao.QueryException: Table './jamfsoftware/applications' is marked as crashed and last (automatic?) repair failed
2017-11-29 14:08:03,692 [ERROR] [Tomcat-93 ] [ComputerHelper ]

Database Name / Table Name (EXAMPLE: Table './jamfsoftware/applications' is marked as crashed --- /jamfsoftware/ is the database --- /applications is the table)

(Now we know what table to fix & in what database it's located)

1.) Open Terminal - Be the Root, Embody the Root (pass is standard local root)

2.) Unload Tomcat

sudo launchctl unload /Library/LaunchDaemons/com.jamfsoftware.tomcat.plist

3.) Login to mysql

/usr/local/mysql/bin/mysql -u root -p

• password is: (nothing) just press enter at 'password:' dialogue

4.) Access Database

use jamfsoftware;

5.) Check, Truncate & Drop Table

• Not Necessary but I always check the table to make sure it is there and I'm entering the correct table name

show tables;

• I then Truncate the table clear & check if that did it (usually doesn't)

truncate applications;

• Finally I drop the table.

drop table applications;

6.) Logout Sql and back to Root / Casper user in Terminal & Turn Tomcat On

sudo launchctl load /Library/LaunchDaemons/com.jamfsoftware.tomcat.plist

7.) Open JSSDatabaseUtil.Jar

located /library/jss/bin/

8.) Restart Tomcat
- w/ Jamf Pro Database Utility Application Selected, Navigate to Main Menu
- Select 'Utilities' Drop Down
- Select Restart Tomcat

9.) Restart Mysql
- w/ Jamf Pro Database Utility Application Selected, Navigate to Main Menu
- Select 'Utilities' Drop Down
- Select Restart MySQL

THIS WILL TAKE A WHILE, it is rebuilding the 'Applications' Table we just truncated/dropped.

10.) Once MySQL "Restarts" Restart the Casper Server entirely
- Visiti JSS Web Portal, should now load & Self Service should work. Happy Day.

[sam_g]

@Hugonaut Hey man, thanks for the extremely detailed steps! I had this exact issue today - in the middle of a backup our server restarted and the Applications table was corrupted. Mysql's autocheck did not fix the issue, so followed your steps to truncate that table. We lost the application data for each Mac, but that'll rebuild over time and now our Jamf server is back up and running. A better solution to me than messing around with restoring a database backup.

[benducklow]

Another option is to look up PI-003459 with your Jamf buddy. I just discovered that our applications table alone was 27GB and was not getting trimmed via the automated Log flushing (Computer Inventory Reports); they supplied me some manual MySQL code to basically trim it down to 221 MB. We don't seem to be having the same issue as @Hugonaut but we do have some sort of log flushing issue (not) happening...

[Johnny_Kim]

Oh man, I totally didn't get to thank @Hugonaut for this! Those steps has saved me countless of times!! Thanks again!