I am having the EXACT same problem as this person is having. However I'm on MySQL v3.23.58. I have tried to downgrade to earlier versions but not to 4.x yet. We're running UBBThreads v6.2.2.
Our problems started when we had to switch to a new server. We were on Redhat 7.x and the new server is Redhat 9. However the database version is the same on both. We're using all the stock redhat binaries, but I did try a custom built MySQL (both older and the same versions) with the exact same result.
Using the advice I learned here I pruned our users table, which removed 10k plus users. But that didn't solve it, we still very often have the db lock and the processlist shows tons of LOCKED tables (all on the users table). Speaking of that, is there any way to bring those users back? I did a dump of the database prior to pruning it. Would re-importing the users table work, or would that break other things?
Here's what I've tried so far:
I have optimized the table(s)
I have run repairs and myisamchk's many times
I have completely dumped and re-imported the data
I have pruned the users table
I have tried multiple versions of mysql
I have read through the mysql tuning and troubleshooting documentation and made many configuration changes and followed their advice
None of these has fixed the problem. Does anyone have any idea what's going on and how to fix it? I was so relieved to read that I wasn't alone in this and it's given me hope that perhaps someone has figured this out and has a fix.
ANY help would be most appreciated!
Thanks!
Cheers,
John
