Advertisement:

Author Topic: Whole forum is down due to a corrupt table  (Read 2011 times)

Offline Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 17,383
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • aleksi-kilpinen on LinkedIn
Re: Whole forum is down due to a corrupt table
« Reply #20 on: July 14, 2018, 06:48:06 AM »
As I understand it, MyISAM tables are bound to crash or corrupt at some point. So InnoDB is the wiser choice for forums really.
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.

How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
 Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline backend

  • Jr. Member
  • **
  • Posts: 183
Re: Whole forum is down due to a corrupt table
« Reply #21 on: July 29, 2018, 11:21:29 PM »
Looks like I finally figured out what the issue is: my DB has exceeded the limit set by the host, so my website forum keeps crashing.

My DB shows just over 1 GB on PHPMyAdmin. Specifically, my smf_messages is about 8126 MB.

-Wondering what are some effective ways to reduce it? I have gone ahead and deleted some big old threads
-Also, can I remove backup_smf_messages ?
-Last, anyway I can find/sort posts in PHPMyAdmin by size?

Offline Aleksi "Lex" Kilpinen

  • A Peculiar Finn
  • Lead Support Specialist
  • SMF Super Hero
  • *
  • Posts: 17,383
  • Gender: Male
  • Don't worry, I'm n00b friendly
    • Aleksi.Kilpinen on Facebook
    • aleksi-kilpinen on LinkedIn
Re: Whole forum is down due to a corrupt table
« Reply #22 on: July 29, 2018, 11:25:04 PM »
If you have backup_ tables, they are most likely from a large update you have done at some point and can be removed if everything has been working OK since.

You are facing a situation where trimming down contents will probably only delay the inevitable though, and you will have to start thinking of other options (getting a larger DB limit, or switch hosts).
A Finnish Support Specialist
 Happily running multiple SMF 2.0 installations.

How you can help SMF

"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum.
 Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 57,085
  • Gender: Male
    • Kindred-999 on GitHub
Re: Whole forum is down due to a corrupt table
« Reply #23 on: July 30, 2018, 08:59:29 AM »
egads...   i have a forum whose database is close to 3 gigs...

get a new host!! !!!
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline GigaWatt

  • The Smiley Guy
  • Support Specialist
  • Sophist Member
  • *
  • Posts: 1,337
  • Gender: Male
    • Macedonian electronics forum
Re: Whole forum is down due to a corrupt table
« Reply #24 on: August 01, 2018, 11:12:45 PM »
Optimize tables and empty out logs, see how much that reduces the size of the database.
"This is really a generic concept about human thinking - when faced with large tasks we're naturally inclined to try to break them down into a bunch of smaller tasks that together make up the whole."

"A 500 error loosely translates to the webserver saying, "WTF?"..."

Offline backend

  • Jr. Member
  • **
  • Posts: 183
Re: Whole forum is down due to a corrupt table
« Reply #25 on: October 13, 2018, 08:20:22 PM »
Hi guys, sorry to bump this old topic again, but problem persists till now:

I have been able to restore from backup, but as I start working on the DB to clean it and reduce the size, it ends up crashing again. In other words, they want me to reduce the size, but when I start doing so, it ends up crashing. So how the hell will I be able to do this? Whether I am working on the forum itself or in phpmyadmin, it eventually crashes after I have done some modification to the SMF_Messages table. For example, if I attempt to to do any of the following functions, it ends up crashing:

Check table 
Analyze table
Repair table
Optimize table


i am attaching a screenshot of what the table looks like, if you guys can see anything odd about it?

« Last Edit: October 13, 2018, 08:41:02 PM by backend »

Offline backend

  • Jr. Member
  • **
  • Posts: 183
Re: Whole forum is down due to a corrupt table
« Reply #26 on: October 13, 2018, 08:22:35 PM »
Optimize tables and empty out logs, see how much that reduces the size of the database.

Thank you:

-I have emptied out the logs
-For optimising the table, I have tried that and it crashes. Basically, anytime I try to do something to this table in question, it ends up crashing, and then I have to restore and start from scratch. In fact, here is the message I get when I try to optimize the table in question (smf_messages)


myisam_sort_buffer_size is too small
Operation failed

Offline CoreISP

  • Server Admin
  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,464
  • Gender: Male
  • CoreISP.net
    • liroyvh on LinkedIn
    • @liroyvh on Twitter
    • CoreISP Corporation :: WebHosting, Dedicated Servers, and more!
Re: Whole forum is down due to a corrupt table
« Reply #27 on: October 13, 2018, 08:30:32 PM »
That's abnormal. You mentioned they block the r/w to the database at a certain limit. Maybe, just a theory, it's trying to create a temporary table and then the aggressive hard limit kicks in (the temporary table makes it cross the maximum usage they had set OR uses more RAM than you're allowed to use at once), suddenly aborts the operation and poof goes your table. (Or maybe they have a execution limit set rather agressively.) So again it could revolve back to the settings of the host causing your issues. That, or there's some serious corruption in there somewhere, but I've personally never seen that manifest itself in this way.

You could try to download a copy of the database to your own device, load it in to an application that can work with it and do whatever it is you want to do in there.
If it's deleting messages manually in the db, you may be better off simply using the prune function in the admin panel? Just a thought.
- CoreISP.net Corporation -
  WebHosting, Colocation, Domain Registration & Network Services
- DedicatedBox.us Servers -
  Low priced Servers in a high-quality Network, the place for all your (advanced) server needs.
  We specialize in hosting big boards. Contact us!

((U + C + I)x(10 − S)) / 20xAx1 / (1 − sin(F / 10))
President/CEO of Simple Machines - Server Manager
Please do not PM for support - anything else is usually OK.

Offline aegersz

  • SMF Hero
  • ******
  • Posts: 1,444
  • Gender: Male
  • "mods" junkie
    • dopetalk
Re: Whole forum is down due to a corrupt table
« Reply #28 on: October 14, 2018, 03:37:23 AM »
yeah, InnoDB is the way to go but the restore locks rows and takes ages. if running Linux then use the "pv" command (pipe viewer).

here is my conversion script that will need some adjusting:

Code: [Select]
mysql -u dbuser -pdbpass smf209 -e "show table status where Engine='MyISAM';" | awk 'NR>1 { print "ALTER TABLE "$1" ENGINE = InnoDB;" }' | mysql -u dbuser -pdbpass smf209
The configuration of my Linux VPS (SMF 2.0 with 140+ mods) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum)

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,239
  • 'Tis the gift to be simple (duh)
Re: Whole forum is down due to a corrupt table
« Reply #29 on: October 14, 2018, 05:25:35 PM »
If you have a good database backup, move to a better host.  It'll be a lot less work than trying to straighten out this mess on GoDaddy, and doesn't cost much.  Your time must be worth something to you.