News:

Want to get involved in developing SMF, then why not lend a hand on our github!

Main Menu

Whole forum is down due to a corrupt table

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

Previous topic - Next topic

Sir Osis of Liver

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).
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Sir Osis of Liver

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.

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

                                     - R. Waters

Arantor

There were db changes specifically in 1.1.9 if it helps.

Sir Osis of Liver

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
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Sir Osis of Liver

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.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Sir Osis of Liver

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?
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Arantor

What does the step do? Maybe take a look at it before deciding whether or not it's appropriate for your situation.

Sir Osis of Liver

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.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Arantor

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.

Sir Osis of Liver

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



/******************************************************************************/
--- 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);
---}
---#


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

                                     - R. Waters

Sir Osis of Liver

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
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Arantor

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)

Sir Osis of Liver

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.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Sir Osis of Liver

Got it loading board index, showing all messages correctly.  Database error if I try to login, view post, or submit registration.  It's here.
Ashes and diamonds, foe and friend,
 we were all equal in the end.

                                     - R. Waters

Advertisement: