Advertisement:

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

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #60 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).

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #61 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.


Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 70,970
    • StoryBB/StoryBB on GitHub
Re: Whole forum is down due to a corrupt table
« Reply #62 on: February 13, 2019, 11:44:39 AM »
There were db changes specifically in 1.1.9 if it helps.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #63 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

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #64 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.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #65 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?

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 70,970
    • StoryBB/StoryBB on GitHub
Re: Whole forum is down due to a corrupt table
« Reply #66 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #67 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.

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 70,970
    • StoryBB/StoryBB on GitHub
Re: Whole forum is down due to a corrupt table
« Reply #68 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #69 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);
---}
---#


Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #70 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

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 70,970
    • StoryBB/StoryBB on GitHub
Re: Whole forum is down due to a corrupt table
« Reply #71 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)
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #72 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.

Offline Sir Osis of Liver

  • SMF Hero
  • ******
  • Posts: 8,816
Re: Whole forum is down due to a corrupt table
« Reply #73 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.