Whole forum is down due to a corrupt table

Started by backend, May 06, 2018, 03:06:51 AM

Previous topic - Next topic

Aleksi "Lex" Kilpinen

As I understand it, MyISAM tables are bound to crash or corrupt at some point. So InnoDB is the wiser choice for forums really.
Slava
Ukraini!


"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

How you can help SMF

backend

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?

Aleksi "Lex" Kilpinen

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).
Slava
Ukraini!


"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

How you can help SMF

Kindred

egads...   i have a forum whose database is close to 3 gigs...

get a new host!! !!!
Слaва
Украинi

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

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

GigaWatt

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?"..."

backend

#25
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?


backend

Quote from: GigaWatt on August 01, 2018, 11:12:45 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

LiroyvH

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.
((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.

aegersz

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:

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 160+ mods & some assorted manual tweaks) can be found here and notes on my mods can be found here (warning: those links will take you to a drug related forum). My (House) music DJ dedication page is here

Sir Osis of Liver

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.

Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

landyvlad

Quote from: Sir Osis of Liver on October 14, 2018, 05:25:35 PM
move to a better host.  It'll be a lot less work than trying to straighten out this mess on GoDaddy,

Good advice.
"Put as much effort into your question as you'd expect someone to give in an answer"

Please do not PM, IM or Email me with questions on astrophysics or theology.  You will get better and faster responses by asking homeless people in the street. Thank you.

Be the person your dog thinks you are.

backend

So after so many tries and much appreciated help from GigaWatt, I am getting closer to hopefully getting things back on track.

Here is what I did:

-Installed a fresh copy (not an upgrade) - on a new directory (just to test things) - I installed version 2.0.13 just to ensure it is compatible with my PHP version (5.0)
-I found some older DB backup that was smaller in size, so I restored it. But now I am getting a new error message:

"unknown column 'add_deny' in 'field list'"

Did some research on this and it appears to do with upgrading, but like I mentioned, I am trying to install a fresh copy here (2.0.13)


Any idea what this could mean in the context of how I am trying to fix this?


Here is my PHPinfo file > http://assyrianvoice.net/smftest/phpinfo.php


Sir Osis of Liver

Cripes, I've seen a number of GoDaddy servers running php 5.3, but 5.2?  Went eol almost 8 years ago. :P

Which SMF version is the database?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

vbgamer45

Quote from: backend on November 21, 2018, 01:15:57 PM
So after so many tries and much appreciated help from GigaWatt, I am getting closer to hopefully getting things back on track.

Here is what I did:

-Installed a fresh copy (not an upgrade) - on a new directory (just to test things) - I installed version 2.0.13 just to ensure it is compatible with my PHP version (5.0)
-I found some older DB backup that was smaller in size, so I restored it. But now I am getting a new error message:

"unknown column 'add_deny' in 'field list'"

Did some research on this and it appears to do with upgrading, but like I mentioned, I am trying to install a fresh copy here (2.0.13)


Any idea what this could mean in the context of how I am trying to fix this?


Here is my PHPinfo file > http://assyrianvoice.net/smftest/phpinfo.php


If you want to use a fresh copy don't restore the database. If you restore you will need to run the large upgrade package
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

backend

I am making slow progress on this, as I keep getting different errors and it is taking me time to research and try to fix things.

Latest:  I created a new temporary forum and uploaded the upgrade files.

Now trying to run the Upgrade.php file and I am getting the following error:

Updating Your SMF Installation!
Thank you for choosing to upgrade to SMF 2.1 Beta 3. All files appear to be in place, and we're ready to proceed.
Your FTP connection information
The upgrader can fix any issues with file permissions to make upgrading as simple as possible. Simply enter your connection information below or alternatively click here for a list of files which need to be changed.

The following error was encountered when trying to connect:

Invalid Server



I know I am putting my correct FTP information as follows, but it is still not accepting it:





The address (often localhost) and port for your FTP server: www.Mysite.com
Username: my ftp user ID
Password:   Password......
Install Path: /home/content/42/9269142/html/myforum




Kindred

First and foremost, why are you trying to use the beta version?
Second, why would you use beta3? That is extremely outdated.


Third, if you insist on doing It, use beta4 and make sure the permissions are correct to begin with and you won't need the ftp info
Слaва
Украинi

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

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

backend

Quote from: Kindred on December 08, 2018, 10:25:05 PM
First and foremost, why are you trying to use the beta version?
Second, why would you use beta3? That is extremely outdated.


Third, if you insist on doing It, use beta4 and make sure the permissions are correct to begin with and you won't need the ftp info


Thanks for the reply. To answer your question, I am using that because that is the only upgrade option I see here: https://download.simplemachines.org/

Where else am I supposed to look?

Arantor

Beta versions should be taken from GitHub where the development happens - beta 3 is over a year old, with 'beta 4' being developed since.

backend

Quote from: Arantor on December 09, 2018, 12:14:44 PM
Beta versions should be taken from GitHub where the development happens - beta 3 is over a year old, with 'beta 4' being developed since.

Curious why the newest version is not here on SMF, as it  may create confusion about what is the latest version available,

Tonyvic

QuoteI am using that because that is the only upgrade option I see here: https://download.simplemachines.org/
The 'Full Install' and 'Large Upgrade' for 2.0.15 are both on that page.

Advertisement: