MySQL help

[#1]

Hello all.

I've been struggling with a problem with a database table for a while. The plugins table is damaged. The good news is that I don't want the data. The bad news is that jamf wants me to repair it, which last time failed and this is causing issues on my nightly backups and causing the JSS not to startup. This particular table has grown to 12GB causing me to run short on space when repairs are attempted or backups occur.

Here's what I see when the jss checks database health.

jamfsoftware.plugins - Table is marked as crashed and last repair failed
jamfsoftware.plugins - Size of indexfile is: 2161177600 Should be: 1562846208
jamfsoftware.plugins - Size of datafile is: 12549171132 Should be: 10597264592
jamfsoftware.plugins - Keyblock size at page -1 is not correct. Block length: 254 key length: 2
jamfsoftware.plugins - Incorrect key file for table'./jamfsoftware/plugins.MYI'; try to repair it jamfsoftware.plugins - Corrupt

In short, I would be happy to drop all data in the table, but don't want to cause issues with the database. Support recommends rolling back to a good database, but I'd prefer to keep my current data. What options do I have to get rid of the data without causing issues for the data I actually want to keep?

Thanks!
Aaron

[rockpapergoat]

you can try issuing a delete or truncate on that table while logged into a mysql session. make sure you have a current backup. something like so, after selecting the jamfsoftware db:

TRUNCATE tablename;

[tlarkin]

Oh this reminds me of one of my favorite XKCD comics

http://imgs.xkcd.com/comics/exploits_of_a_mom.png

-Tom

[tlarkin]

On a serious note though, you should be able to drop the whole table. You may need to increase your max packet size though to delete it

[#5]

Yeah...I love that cartoon too.

I do have a backup, but recent backups all have this same issue, and going to a backup further back loses data I'd prefer to keep. I'm mostly worried that doing something to the table that will cause issues if other tables are referring to data in the truncated table. I have little practical knowledge of best practices on the database side of things and don't want to create larger issues down the road.

So, with that said, would truncating a corrupted table 1. delete the unwanted data (assuming yes here) 2. potentially cause issues for the database "down the road"

So, if there are no potential issues other than losing the plugin data (which I don't want anyway), then truncating should be ok.

Thoughts?

[#6]

Are the guidelines to determine an acceptable max packet size?

[tlarkin]

There really isn't a formula or set model to set your packet size that I know of personally. I always do it from trial and error. Basically the max_packet_allow is how much memory (RAM) mysql can use to read a blob of data. If your blob is too large and your packet size is set too small it will not be able to perform that action on your database on that particular table. I am by no means a mysql expert, this is just what I have picked up over the years.

You can edit your /etc/my.cnf file to change this. I set mine to max_packet_allowed = 256M so it can use up to 256 Meg of RAM when reading a blob of data. Before when I had database issues I could not even flush policy logs because the max_packet_allow was set to 1M.

I have 24gigs of RAM in my JSS, so you want to be able to make sure everything plays nice.

http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html#sysvar_max_allowed_packet

-Tom