Simple Machines Community Forum

SMF Support => SMF 2.0.x Support => Topic started by: backend on May 06, 2018, 03:06:51 AM

Title: Whole forum is down due to a corrupt table
Post by: backend on May 06, 2018, 03:06:51 AM
Visited my forum few days ago to find it down worth the following message :

“smf_messages' is marked as crashed and last (automatic?) repair failed”

I tried repairing it in phpmyadmin and I wasn’t able to


Anyone how else I can fix this? Better yet, what is the function of this table and can I simply get a new version of this table created and if so , how....
Title: Re: Whole forum is down due to a corrupt table
Post by: Aleksi "Lex" Kilpinen on May 06, 2018, 03:09:40 AM
It stores all your forum's posts, so you may be facing a situation where you need to restore a backup. Hope you have one.
Title: Re: Whole forum is down due to a corrupt table
Post by: Illori on May 06, 2018, 06:11:23 AM
have you asked your host if they can repair the table? they may have other ways that you dont have access to.
Title: Re: Whole forum is down due to a corrupt table
Post by: shawnb61 on May 06, 2018, 12:20:25 PM
You can repair tables in phpmyadmin also.

Title: Re: Whole forum is down due to a corrupt table
Post by: backend on May 06, 2018, 03:23:28 PM
I called GoDaddy and after an hour of back and fourth, they tell me they don't have any other backup of my forum other than one from Feb 4, 2018!!!! They said my DB doesn't allow direct access, so no backups were being made. Such crap!!!!!

So I have a backup of this corrupt table (Backup_smf_messages) - is there an easy way to copy its content to smf_messages?
Title: Re: Whole forum is down due to a corrupt table
Post by: Illori on May 06, 2018, 03:36:11 PM
do you know when that backup was made? it is possible it was made when you were upgrading from one version of SMF to another, if so that version of the table would need to be upgraded and would be missing a lot of data.

as a host they should have FULL access of everything on the server including the database. this is a good time for you to find a new host that does include regular backup's.
Title: Re: Whole forum is down due to a corrupt table
Post by: HDB on May 06, 2018, 07:42:25 PM
Sorry to hear that you are having problems. Some hosts do backups as part of their hosting package but not GoDaddy. They sell a backup service. GoDaddy sells a service from Drop My Site. You will also see that you can do manual backups yourself in cPanel or you can schedule a cron job in cPanel for automatic scheduled backups of your database.

I hope you can fix your current DB.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on June 19, 2018, 09:59:22 PM
Hi guys,

So I was able to restore from a backup that GoDaddy. But here is the problem: after I restore, every couple of days, the DB will crash again with the same error message:

"“smf_messages' is marked as crashed and last (automatic?) repair failed”"

I called the host and spent close to half an hour trying to see what the problem is, and they couldn't find anything or what is causing this.

I am really at a loss. I have lost months worth of posts on my forum due to this error message and having to restore every couple of days. Heck I even gave up on it at times, as I can't keep doing this.

Is there something specific I should look for?
Anyway to ensure I don't get this error message?


I really appreciate any help out there.
Title: Re: Whole forum is down due to a corrupt table
Post by: vbgamer45 on June 19, 2018, 10:04:41 PM
Table corrupted happens if a server is restarted suddenly or just if a mysql has an unexpected issue. if it happens often it is a database server issue.
You can maybe try switching to Innodb for the smf_messages tab which is a different mysql engine

Title: Re: Whole forum is down due to a corrupt table
Post by: GigaWatt on June 19, 2018, 11:15:17 PM
If the forum is not too big, try temporarily using a free host, see if the same thing starts happening again... which I seriously doubt after some of the posts I've read about GoDaddy on this forum.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on July 08, 2018, 02:57:58 PM
Table corrupted happens if a server is restarted suddenly or just if a mysql has an unexpected issue. if it happens often it is a database server issue.
You can maybe try switching to Innodb for the smf_messages tab which is a different mysql engine



How would I switch to Innodb?

I have now restored the DB some 6 times and it keeps crashing after 3 days or so. I have given up on trying to restore this again because it will just crash again. And support is being useless.

At the very least, am trying to determine if the problem is with my DB (abo out 1 GB) or something on their side. If the server is being restarted, is that something that would lead to this??? And why would they restart the same server over and over like this , am sssumig there are others on this server that would get affected too.
Title: Re: Whole forum is down due to a corrupt table
Post by: GigaWatt on July 08, 2018, 04:50:18 PM
Try this.

Admin --> Maintenance --> Routine --> Find and repair any errors --> Run task now

Admin --> Maintenance --> Database --> Optimize all tables --> Run task now

Admin --> Maintenance --> Routine --> Empty the file cache --> Run task now
Title: Re: Whole forum is down due to a corrupt table
Post by: Gazmanafc on July 08, 2018, 07:52:51 PM
I have now restored the DB some 6 times and it keeps crashing after 3 days or so. I have given up on trying to restore this again because it will just crash again. And support is being useless.

At the very least, am trying to determine if the problem is with my DB (abo out 1 GB) or something on their side. If the server is being restarted, is that something that would lead to this??? And why would they restart the same server over and over like this , am sssumig there are others on this server that would get affected too.


If anything if the issues recur that often, my recommendation at this point in time is to consider moving hosts.
Title: Re: Whole forum is down due to a corrupt table
Post by: shawnb61 on July 08, 2018, 08:27:23 PM
If anything if the issues recur that often, my recommendation at this point in time is to consider moving hosts.
+1

Although InnoDB is known to be more stable & crash-resistant, you should also know that it doesn't like getting its tables moved around.  Export/Import is the proper way to relocate InnoDB tables.  If the files are moved via a filesystem-level move or restore, there is an ugly process needed to recover the InnoDB tables.  I'd be nervous that GoDaddy moves them on you unannounced...   

That said, to directly answer your question...  To convert a table to InnoDB, you can either alter a table's engine directly in phpMyAdmin or use SQL to do so.  In phpMyAdmin, pull up the table to view its structure, click on Operations & under Table Options you will see Storage Engine: MyISAM.  You can change it to InnoDB there. 

Alternatively, you can use SQL to do so:
Code: [Select]
ALTER TABLE my_table ENGINE = InnoDB;
I would do all tables.  (My forum is all InnoDB.)   I don't think it does you much good to have messages InnoDB but boards & topics not InnoDB... 
Title: Re: Whole forum is down due to a corrupt table
Post by: Kindred on July 09, 2018, 08:52:59 AM
IIRC there is a reason why we do not use InnoDB for all tables to start with...


backend...   you don't normally have to restore the database. Usually, you can go in via phpmyadmin and repair the crashed table by itself.
However, regular crashes like that indicate a larger problem with the host and their setup.
Title: Re: Whole forum is down due to a corrupt table
Post by: shawnb61 on July 09, 2018, 09:10:36 AM
InnoDB is preferred over MyISAM.    FYI, 2.1 installs will use InnoDB on all tables.   
Title: Re: Whole forum is down due to a corrupt table
Post by: Kindred on July 09, 2018, 09:39:07 AM
Quote
InnoDB has row-level locking, MyISAM can only do full table-level locking. InnoDB has better crash recovery. MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013). InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not

Ah...   that third sentence is why we never did a switch...

As long as we still support (prior to) MySQL 5.6, the lack of fulltext search indexes might be an issue.
Title: Re: Whole forum is down due to a corrupt table
Post by: shawnb61 on July 09, 2018, 10:51:40 AM
Interesting...  I've always used custom indexes, so I've never seen an issue in 2.x or 2.1. 
Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on July 12, 2018, 09:01:04 AM
Seeing that fulltext is not set by default, I think you’d be safe to run with it.
Title: Re: Whole forum is down due to a corrupt table
Post by: aegersz on July 13, 2018, 02:36:10 AM
never had a table crash since moving to InnoDB.

haven't looked back and infrequent DB backups are wonderful.

easy conversion also.
Title: Re: Whole forum is down due to a corrupt table
Post by: Aleksi "Lex" Kilpinen 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.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend 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?
Title: Re: Whole forum is down due to a corrupt table
Post by: Aleksi "Lex" Kilpinen 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).
Title: Re: Whole forum is down due to a corrupt table
Post by: Kindred on July 30, 2018, 08:59:29 AM
egads...   i have a forum whose database is close to 3 gigs...

get a new host!! !!!
Title: Re: Whole forum is down due to a corrupt table
Post by: 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.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend 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?

Title: Re: Whole forum is down due to a corrupt table
Post by: backend 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
Title: Re: Whole forum is down due to a corrupt table
Post by: CoreISP 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.
Title: Re: Whole forum is down due to a corrupt table
Post by: aegersz 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
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver 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.

Title: Re: Whole forum is down due to a corrupt table
Post by: landyvlad on October 21, 2018, 09: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.
Title: Re: Whole forum is down due to a corrupt table
Post by: 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

Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on November 21, 2018, 09:23:10 PM
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?
Title: Re: Whole forum is down due to a corrupt table
Post by: vbgamer45 on November 21, 2018, 09:24:55 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
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on December 08, 2018, 08:10:02 PM
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



Title: Re: Whole forum is down due to a corrupt table
Post by: 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
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on December 09, 2018, 12:01:33 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?
Title: Re: Whole forum is down due to a corrupt table
Post by: 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.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on December 09, 2018, 12:35:33 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,
Title: Re: Whole forum is down due to a corrupt table
Post by: Tonyvic on December 09, 2018, 01:03:04 PM
Quote
I am using that because that is the only upgrade option I see here: https://download.simplemachines.org/ (https://download.simplemachines.org/)
The 'Full Install' and 'Large Upgrade' for 2.0.15 are both on that page.
 
Title: Re: Whole forum is down due to a corrupt table
Post by: Kindred on December 09, 2018, 03:24:27 PM
Well, because making an official release is not simple...   and, as the page clearly states, beta versions should not be used in production, nor should they be used unless you understand technical and debugging processes.

2.0.15 is the current release in the main supported branch.
Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on December 09, 2018, 03:29:43 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,

Because it changes literally nearly every day...
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on December 09, 2018, 07:53:02 PM
thanks guys, i ended up using the 2.0.15 Large Upgrade (https://download.simplemachines.org/index.php?thanks;filename=smf_2-0-15_upgrade.zip)

Now having another issue with the update:

"Server has not responded for 30 seconds. It may be worth waiting a little longer or otherwise please click here to try this step again"[/b}

And this has been for hours and it is still stuck on "Executing: "Updating and creating indexes..." (1 of 19 - of this script)"



Title: Re: Whole forum is down due to a corrupt table
Post by: Illori on December 09, 2018, 08:04:22 PM
there are no database changes since SMF 2.0 was released, you dont need to run the upgrade script. also it is recommend to use the package manager patches to upgrade if you are already on some sub version of SMF 2.0.

if you did use the large upgrade package that means you have uninstalled maybe even partially uninstalled your mods and may need to reinstall them when you are done.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on December 09, 2018, 08:07:49 PM
there are no database changes since SMF 2.0 was released, you dont need to run the upgrade script. also it is recommend to use the package manager patches to upgrade if you are already on some sub version of SMF 2.0.

if you did use the large upgrade package that means you have uninstalled maybe even partially uninstalled your mods and may need to reinstall them when you are done.

To make a long story short, assuming you didn't follow this thread from the beginning (Gosh, even I forgot some of the details lol) , we had to upgrade because we were having issues and I kept getting errors. One of which was the following : "Unknown column 'add_deny' in 'field list'"

After some research, I found out it had to do with running old files/DB and needed to upgrade to new version of PHP which I did.

Title: Re: Whole forum is down due to a corrupt table
Post by: backend on December 12, 2018, 01:17:44 AM
there are no database changes since SMF 2.0 was released, you dont need to run the upgrade script. also it is recommend to use the package manager patches to upgrade if you are already on some sub version of SMF 2.0.

if you did use the large upgrade package that means you have uninstalled maybe even partially uninstalled your mods and may need to reinstall them when you are done.

To make a long story short, assuming you didn't follow this thread from the beginning (Gosh, even I forgot some of the details lol) , we had to upgrade because we were having issues and I kept getting errors. One of which was the following : "Unknown column 'add_deny' in 'field list'"

After some research, I found out it had to do with running old files/DB and needed to upgrade to new version of PHP which I did.



If I don't upgrade, I am getting '"Unknown column 'add_deny' in 'field list'"'

And I do try to upgrade, I am stuck at 14% and mot moving. Been trying it for days.

If I could get over '"Unknown column 'add_deny' in 'field list'" without having to upgrade, would be a lot better.



Any advise?

Title: Re: Whole forum is down due to a corrupt table
Post by: SpacePhoenix on December 12, 2018, 02:04:57 AM
And I do try to upgrade, I am stuck at 14% and mot moving. Been trying it for days.
When it gets stuck at 14% are there any errors in either the PHP or MySQL error logs?
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on December 13, 2018, 04:09:25 PM
If I could get over '"Unknown column 'add_deny' in 'field list'" without having to upgrade, would be a lot better.

Well, you can't.  A 1.1 database will not work in 2.0.15 install.  Upgrade will sometimes fail if you're on a really crappy server.  I've had to upgrade several forums by importing db to test install on my host, upgrade, import upgraded db to original forum.
Title: Re: Whole forum is down due to a corrupt table
Post by: backend on January 02, 2019, 11:18:02 PM
Happy new year everyone. Been busy last couple of weeks with the holidays so haven't been able to work on this.

I am running the upgrade again and I am stuck at 14%, with the following error message:

"Executing upgrade script 1 of 2.
Executing: "Updating and creating indexes..." (1 of 19 - of this script)

!!Error!
Server has not responded for 30 seconds. It may be worth waiting a little longer or otherwise please click here to try this step again"


I have run this a million times and it keeps getting stuck at the same percentage, so there is clearly something wrong that is preventing this from progressing.


Please advise!

Title: Re: Whole forum is down due to a corrupt table
Post by: Aleksi "Lex" Kilpinen on January 03, 2019, 01:29:48 AM
That error is a bit misleading, as it doesn't actually mean the script has stopped. Allow it some time, and it will try to continue automatically.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on January 03, 2019, 04:38:47 PM
Just reread this topic, still on GoDaddy?  That's your biggest problem right now.  Are you trying to upgrade a 1.1 datrabase to 2.0?  Is the 1.1 database currently running in a 1.1 install?  Have you dropped the backup tables?

Title: Re: Whole forum is down due to a corrupt table
Post by: backend on January 21, 2019, 05:36:55 PM
Just reread this topic, still on GoDaddy?  That's your biggest problem right now.  Are you trying to upgrade a 1.1 datrabase to 2.0?  Is the 1.1 database currently running in a 1.1 install?  Have you dropped the backup tables?



Frankly, as much as GoDaddy is hated here (and I am not here to defend it) I think it is not a good idea to attack it when people are trying to get help. I noticed this being a common theme here. It is not easy to just pick up and leave. Some people have tens of different domains and websites hosted with them.


And to answer your question, yes that is what I am doing.
Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on January 21, 2019, 05:40:48 PM
I guess you've not had to recover sites where GoDaddy's incompetence has been the sole cause of a forum being damaged, sometimes almost beyond repair.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on January 21, 2019, 08:24:55 PM
Nothing personal against GoDaddy, it's just that they're a terrible host.  I've worked on a number of forums that were trashed for no other reason than their gross stupidity.  I've moved several forums off GD to other hosts, and have regulars who remain there.  If you are concientious (sp?) about maintaining your forum, and do regular backups, you can get by with them, but there are many hosts in same price range that offer better packages and much better support.  As for upgrades, I've done two or three that could not be run on GD, had to import db dump into test install on my server, upgrade there, then move it back to GD.  This should never be necessary on any reasonably competent host.
Title: Re: Whole forum is down due to a corrupt table
Post by: Aleksi "Lex" Kilpinen on January 22, 2019, 01:24:47 AM
Sad truth is that GoDaddy is the definition of overseller, and a bad one at that, and while that might not be a problem for static sites, it can fast become a major issue with forums.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 12, 2019, 05:51:56 PM
Been working on this forum couple of days, got as far as this (http://www.thekrashsite.com/avnet/index.php), not sure what to look for at this point.  Production database and recent backups are trashed.  This backup was from 3 May 2018, several days before OP started this topic.  It contains a set of backup tables that run normally in clean 1.1.2 install, but they're from 2007.  Current set of tables is what you see - forum loads, displays database error, no errors logged by forum or server.  I've removed dozens of mod settings from smf_settings, dropped mod tables, ran all phpmyadmin maintenance tasks.  Settings and paths are correct, don't see anything that looks out of place.  Any ideas?


Update:  Just tried something before going offline.  1.1.2 installer will not run on my server, doesn't like MariaDB 10.0.16.  Don't recall ever running a db this old.  The backup tables do work, but could this be causing a problem?  Will try importing cleaned up db to OP's GoDaddy account tomorrow night when I'm back on.  Any other suggestions would be appreciated.

Title: Re: Whole forum is down due to a corrupt table
Post by: vbgamer45 on February 12, 2019, 06:20:23 PM
Post the database errors might be able to find some clues.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 12, 2019, 06:22:35 PM
It's not logging any errors I can see.  Will ask host support if db errors are being logged elsewhere.

Title: Re: Whole forum is down due to a corrupt table
Post by: vbgamer45 on February 12, 2019, 06:48:11 PM
Yeah if you are logged in as admin you can see the error.
If not you have edit the source of SMF to take off the admin check for the database error page.
Title: Re: Whole forum is down due to a corrupt table
Post by: CoreISP on February 12, 2019, 08:08:17 PM
If the db is crashing due to an aggressive resource limitation, running the upgrader on that server is like playing Russian roulette with it.
Best chance is doing it locally indeed. Did I read correctly that the forum is on a very old 1.1 version? You may have more luck running the upgrader on an older PHP/mySQL version. At the very least lower than mySQL 5.7 or similar as that may have you end up running in to the middle finger of strict mode... Which may not end up in regular logs you can see.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 12, 2019, 10:46:56 PM
Forum doesn't run, just what you see at link.  Nothing in smf_log_errors or server errorlog.  Running it in php 5.4, MariaDB.  Backup tables run ok, current tables error out, so I don't think it's MariaDB.  Am considering attempting upgrade to 1.1.21, but would rather have forum running before I try it.  Other dumps I imported are badly damaged and incomplete, this one isn't as bad, connects but doesn't run.  It's on my server, not GoDaddy.  Asked my host support if they can see any errors, will check on that tomorrow (am on my reader).
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 13, 2019, 11:36:20 AM
Thinking about this overnight, couple of things are peculiar.

- Why is there a set of 1.1.2 backup tables from 2007?  backup_smf_ tables are created by upgrader, why would anyone use upgrader on 1.1.2 forum 12 years ago?  There are no backup tables from OP's recent 2.0 upgrade attempts, per earlier posts in this topic.

- Seeing this in smf_settings:

variable            value
-----------------------------
smfVersion        1.1.2
0           smfVersion
1           SMF 1.1.9

Don't know what the second and third are, or where they came from.  Wondering if this might be 1.1.9 database.  There were db changes in 1.1 branch, maybe .9 db will not run in .2.  Will try connecting to .9 install soon as I have time.

 >:( Didn't work, same error in 1.1.9.

Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on February 13, 2019, 11:44:39 AM
There were db changes specifically in 1.1.9 if it helps.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 13, 2019, 10:40:28 PM
Got it running, had to replace missing tables, was able to run 1.1.21 upgrade.  Works good, but posts only go up to 12 Jan 2012.  Found another dump, same file date, it's 2.0.2, runs ok, patched up to 2.0.15.  Last post is 25 May 2015.  The later backups I looked at are badly damaged, partially upgraded, missing tables.  Don't think I can fix them without the original 1.1 db, but will have another look.  What a mess. :P
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 14, 2019, 04:35:56 PM
Trying to upgrade damaged production database.  Replaced 14 missing tables from 2.0.15 test install, upgrade chokes on incorrect column names.  It's looking for 1.1 column name (i.e., eventDate), but some column names have aleady been upgraded to 2.0 (event_date), this halts upgrade.  Any way around this other than manually reverting column names?  Will take forever, as server is having problems running script, very slow, 500 errors.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 14, 2019, 05:54:36 PM
Got to around 60% complete manually coaxing this upgrade, but can't get past Executing: "Making SMF MySQL strict compatible..." (16 of 19 - of this script).  Is this critical?  What happens if I remove step from upgrade?
Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on February 14, 2019, 06:14:15 PM
What does the step do? Maybe take a look at it before deciding whether or not it's appropriate for your situation.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 14, 2019, 06:21:44 PM
Have an idea what it does, and iirc, there's another way to work with mysql strict.  Can't get upgrade past this step, and there's not much to lose, the database is a real mess and we haven't come up with a useable backup that's any more recent than 2015.  I'll try removing the step from upgrade_1-1.sql, see if it will complete.
Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on February 14, 2019, 06:24:43 PM
Depending on what it's doing, you may not have any choice to do it to fit in with what the rest of SMF expects.

I was kinda asking for the step you're actually running into because I don't have the source handy but I'd probably be able to help you disentangle what the problem was if I could see it.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 14, 2019, 06:29:20 PM
I'm at 61/44% Executing: "Making SMF MySQL strict compatible..." (16 of 19 - of this script)

Looks like it's this -

upgrade_1-1.sql

Code: [Select]

/******************************************************************************/
--- Making SMF MySQL strict compatible...
/******************************************************************************/

---# Preparing messages table for strict upgrade
ALTER IGNORE TABLE {$db_prefix}messages
DROP INDEX ipIndex;
---#

---# Adjusting text fields
---#
---{
// Note we move on by one as there is no point ALTER'ing the same thing twice.
$_GET['strict_step'] = isset($_GET['strict_step']) ? (int) $_GET['strict_step'] + 1 : 0;
$step_progress['name'] = 'Adding MySQL strict compatibility';
$step_progress['current'] = $_GET['strict_step'];

// Take care with the body column from messages, just in case it's been enlarged by others.
$request = upgrade_query("
SHOW COLUMNS
FROM {$db_prefix}messages
LIKE 'body'");
$body_row = mysqli_fetch_assoc($request);
mysqli_free_result($request);

$body_type = $body_row['Type'];

$textfield_updates = array(
array(
'table' => 'attachments',
'column' => 'filename',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'ban_groups',
'column' => 'reason',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'ban_items',
'column' => 'hostname',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'ban_items',
'column' => 'email_address',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'boards',
'column' => 'name',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'boards',
'column' => 'description',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'categories',
'column' => 'name',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'log_actions',
'column' => 'extra',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'log_banned',
'column' => 'email',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'log_banned',
'column' => 'email',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'log_errors',
'column' => 'url',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'log_errors',
'column' => 'message',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'log_online',
'column' => 'url',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'membergroups',
'column' => 'stars',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'lngfile',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'realName',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'buddy_list',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'pm_ignore_list',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'messageLabels',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'emailAddress',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'personalText',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'websiteTitle',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'websiteUrl',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'location',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'ICQ',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'MSN',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'signature',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'avatar',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'usertitle',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'memberIP',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'secretQuestion',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'members',
'column' => 'additionalGroups',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'messages',
'column' => 'subject',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'messages',
'column' => 'posterName',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'messages',
'column' => 'posterEmail',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'messages',
'column' => 'posterIP',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'messages',
'column' => 'modifiedName',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'messages',
'column' => 'body',
'type' => $body_type,
'null_allowed' => false,
),
array(
'table' => 'personal_messages',
'column' => 'body',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'package_servers',
'column' => 'name',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'personal_messages',
'column' => 'fromName',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'personal_messages',
'column' => 'subject',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'personal_messages',
'column' => 'body',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'polls',
'column' => 'question',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'polls',
'column' => 'posterName',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'poll_choices',
'column' => 'label',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'settings',
'column' => 'variable',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'settings',
'column' => 'value',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'sessions',
'column' => 'data',
'type' => 'text',
'null_allowed' => false,
),
array(
'table' => 'themes',
'column' => 'variable',
'type' => 'tinytext',
'null_allowed' => false,
),
array(
'table' => 'themes',
'column' => 'value',
'type' => 'text',
'null_allowed' => false,
),
);
$step_progress['total'] = count($textfield_updates);

foreach ($textfield_updates as $ind => $change)
{
// Already done it?
if ($_GET['strict_step'] > $ind)
continue;

// Make the index, with all the protection and all.
textfield_alter($change, $substep);

// Store this for the next table.
$_GET['strict_step']++;
$step_progress['current'] = $_GET['strict_step'];
}

$step_progress = array();
---}
---#

---# Replacing messages index.
ALTER TABLE {$db_prefix}messages
ADD INDEX ipIndex (posterIP(15), ID_TOPIC);
---#

---# Adding log_topics index.
---{
upgrade_query("
ALTER TABLE {$db_prefix}log_topics
ADD INDEX ID_TOPIC (ID_TOPIC)", true);
---}
---#

Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 14, 2019, 06:44:26 PM
Upgrade completed with that removed.  It's running, displays login window.  Registration doesn't work, database error.  Can't find setting that allows guests to view board index, so don't know if messages are working.  Too tired to mess with it much longer today. :P
Title: Re: Whole forum is down due to a corrupt table
Post by: Arantor on February 14, 2019, 07:24:34 PM
Yeah, you really do actually need to do that, just hacking crap out will inevitably come back to bite you. But you should be able to do the individual queries one at a time.

But this is the part where you'll likely tell me that it sort of works therefore you don't need to do it (it's likely the cause of the DB error you have for registration)
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 15, 2019, 12:54:10 AM
No, I wouldn't tell you that.  This is possibly the worst database I've tried to recover, I'm just trying to get it running sufficiently to determine if it's salvageable.  Any assistance is greatly appeciated.  Will get back to it tomorrow.
Title: Re: Whole forum is down due to a corrupt table
Post by: Sir Osis of Liver on February 15, 2019, 11:49:03 AM
Got it loading board index, showing all messages correctly.  Database error if I try to login, view post, or submit registration.  It's here (http://www.thekrashsite.com/avnettest/index.php).