Table crash again, after database optimization

[Nihonto]

Still running 3.4.7 for various reasons. Mid sized forum, not too large.
Every few months, I check out the db in phpmyadmin, and see quite a few tables with overhead.
I usually select tables with overhead and optimize. Most times it sorts them all out nicely. But I have now had the 4th time where one table ( ipbmessage_posts) crashes, and cannot be repaired no matter what i do. The forum works perfectly, but we cannot open or view any pm's at all.
This means I have to restore from backup, which is usually a few days or up to 2 weeks before. Meaning we lose all posts and pm's from that point, and gets my members really frustrated.
Now I know I know far too little to be running this, but can anyone advise on why this table is crashing, or what I should be doing to prevent this?
Is it because someone is using the table when i do the optimization? I tried deactivating the forum first, and then doing the db process, but had the same result once. Maybe they were using it when I shut it down?
Either way..same result. And I am now too scared to do these db functions.
So when should I be doing db maintenance, and how should I be doing it differently to prevent crashed tables? Any advice greatly appreciated.

[AndyF]

I'll skip the bit about the 3x series not being supported anymore (for a relatively long time now) as you know that hopefully anyway if not I can explain. I would however have to say if you must stay on the 3x series for any reason it would be strongly worthwhile to at least have the last version as that will be the most 'up to date' as such regarding bug fixes, plus you did not ask for a lecture about versions so I'll not give one.

Regarding your questions:

I'd be tempted to ask your host actually if they have any input on why the same table is crashing, they may or may not provide any useful information however it is well worth asking them.

I cannot think of anything specific really for this particular table, unless there are huge (I mean huge) PM's ongoing as in hundreds of pages long.

I want to point out I'm not a "server guy" but as far as I'm aware when you do a repair or a backup each table is locked temporarily so that it cannot be used, this as I understand it prevents a situation where data might try to be written to said table while another "operation" is in progress on it.

I would as I say start with your host to see if they have any input. If they are unhelpful ask them if possible for any relevant logs.

[Nihonto]

Short version of why I am still on old software...
Being on shared hosting, I don't have the access, time, resources or knowledge to set up a test forum, try the whole conversion and sit for days trying to fix the issues. 6 days a week fulltime jobk, and very limited knowledge of databases and servers. And fully aware of the fact that this is not going to work well on shared hosting for much longer.
Now I want to upgrade to decent local VPS hosting. Would have to be fully managed too. But to update software at the same time, I would now need to find a way to download 25 Gigs, several hundred thousand files...set up the entire thing on a VPS host...then try a test install and conversion, and then deal with the multitude of issues to resolve when I am dumb as a post.
Thinking of all that makes me cringe, and pretty much 110% sure I would never get it up and running. Just don't have the ability or time.

So my other option is to find someone with the time, knowledge and expertise who can tackle this on a fair paid-support basis. And so far, I have no idea how to tackle that.

That's the answer to the first part of your reply.

[AndyF]

9 minutes ago, Brian Robinson_47676 said:

That's the answer to the first part of your reply.

Oh its OK I did not need to know that, I'm sorry if you read my reply that way. Its just as it is an "unsupported" version I do have to at least mention it briefly in the reply that's all.

Would recommend as above asking your host for any input on this though, nothing to lose by just asking them for their thoughts.

[crmarks]

When you optimize these tables they are locked and that data is replicated on the server during optimization, requiring your MySQL server to have enough available overhead storage for temporary duplication. If you are optimizing all of these tables all at the same time, in the same command, you might be hitting a wall on server resources. If you run out of system storage or the process times out, then that table will crash and data will be lost. If you are anywhere near your shared hosting storage limits, this is something to investigate. Your web host will have a better understanding of the situation as well, they may have certain limits on shared MySQL processing time too. Optimizing just one table at a time may help minimize your risk of a crash, but I would avoid it entirely at this point going forward. @Brian Robinson_47676

 

[Nihonto]

Thanks folks. Andy, didn't take it in a bad way...am just frustrated because I know I need to both move server, and change versions..and I have no idea how I am going to get there. But it has to be done and within the next few months. So just getting some frustration out.

Thanks for the optimization explanations. Just to note that I have only been selecting 4 or 5 tables at a time...there is not huge overhead..and it seems that it is that same table every time (it doesn't do it every time...but when it does it, it is pm's that break)
You all know how forum members (especially on a serious forum where half the members are over 40) react to losing posts and pm's.
Going to avoid any db maintenance where I can, and do regular backups. Just wondering why that same table.

Anyways, have to look at how to move that many files to a new host, and upgrade. That would put me in a better position (if the stress of that doesn't kill me)

Thanks all.