Find and Repair Errors.(forum Maintanance)

[Adish - (F.L.A.M.E.R)]

#1054 - Unknown column 'first_msg' in 'where clause'

[Oldiesmann]

I mis-typed the key names, but apparently you can't select things based on the values of keys in MySQL anyway, so...

Code Select Expand

SELECT * FROM smf_topics WHERE ID_FIRST_MSG = 11783 OR ID_LAST_MSG = 11783;

[Adish - (F.L.A.M.E.R)]

ID_TOPIC  isSticky  ID_BOARD  ID_FIRST_MSG  ID_LAST_MSG  ID_MEMBER_STARTED  ID_MEMBER_UPDATED  ID_POLL  numReplies  numViews  locked 
3607           0           16           11783            11933         1537        277     0     1      67     0


Each title has a number under it. Sorry for the mess again, but i cant really post it in the exact format.

[Oldiesmann]

Ok. Replace the code I gave you with this...

Code Select Expand

// Figure out if either of these posts is the first or last message in a particular topic...
$get_topic_first_msg = db_query("SELECT ID_TOPIC, ID_FIRST_MSG, ID_LAST_MSG FROM {$db_prefix}topics WHERE ID_FIRST_MSG = $row[myID_FIRST_MSG] OR ID_LAST_MSG = $row[myID_FIRST_MSG]", __FILE__, __LINE__);
$get_topic_last_msg = db_query("SELECT ID_TOPIC, ID_FIRST_MSG, ID_LAST_MSG FROM {$db_prefix}topics WHERE ID_FIRST_MSG = $row[myID_LAST_MSG] OR ID_LAST_MSG = $row[myID_LAST_MSG]", __FILE__, __LINE__);
$real_topic_first_msg = 0;
$real_topic_last_msg = 0;

// Found a valid topic!
if(mysql_num_rows($get_topic_first_msg) != 0)
{
while($results = mysql_fetch_assoc($get_topic_first_msg))
{
if($results['ID_FIRST_MSG'] == $row['myID_FIRST_MSG'] || $results['ID_LAST_MSG'] == $row['myID_FIRST_MSG'])
{
$real_topic_first_msg = $results['ID_TOPIC'];
}
}

mysql_free_result($get_topic_first_msg);

// Now we have a topic for the "myID_FIRST_MSG"...
db_query("UPDATE {$db_prefix}messages SET ID_TOPIC = $real_topic_first_msg WHERE ID_MSG = $row[myID_FIRST_MSG]", __FILE__, __LINE__);
}

if(mysql_num_rows($get_topic_last_msg) != 0)
{
while($results = mysql_fetch_assoc($get_topic_last_msg))
{
if($results['ID_FIRST_MSG'] == $row['myID_LAST_MSG'] || $results['ID_LAST_MSG'] == $row['myID_LAST_MSG'])
{
$real_topic_last_msg = $results['ID_TOPIC'];
}
}

mysql_free_result($get_topic_last_msg);

// Now we have a topic for the "myID_FIRST_MSG"...
db_query("UPDATE {$db_prefix}messages SET ID_TOPIC = $real_topic_last_msg WHERE ID_MSG = $row[myID_LAST_MSG]", __FILE__, __LINE__);
}

// Now... We only do this if no valid topic was found for either message...
if ($real_topic_first_msg == 0 && $real_topic_last_msg == 0)
{
db_query("
INSERT INTO {$db_prefix}topics
(ID_BOARD, ID_MEMBER_STARTED, ID_MEMBER_UPDATED, ID_FIRST_MSG, ID_LAST_MSG, numReplies)
VALUES ($row[ID_BOARD], $memberStartedID, $memberUpdatedID,
$row[myID_FIRST_MSG], $row[myID_LAST_MSG], $row[myNumReplies])", __FILE__, __LINE__);
$newTopicID = db_insert_id();

db_query("
UPDATE {$db_prefix}messages
SET ID_TOPIC = $newTopicID, ID_BOARD = $row[ID_BOARD]
WHERE ID_TOPIC = $row[ID_TOPIC]", __FILE__, __LINE__);
}

That should fix all possible issues:
The "myID_FIRST_MSG" has an invalid topic ID but is the first or last message in an existing topic
The "myID_LAST_MSG" has an invalid topic ID but is the first or last message in an existing topic

This happens because there was a database problem after SMF inserted the new post and the new topic data that prevented SMF from being able to update the topic ID in the messages table.

[Adish - (F.L.A.M.E.R)]

Well..................

Code Select Expand

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ID_BOARD = 16
WHERE ID_TOPIC = 1' at line 2
File: /home/goldysun/public_html/forum/skywise/Sources/RepairBoards.php
Line: 243

New error yipiiii ! LOL

[Oldiesmann]

I'm not sure what it's complaining about this time to be honest, but that's good news - that means that it found a post that isn't associated with any topic yet...

Try this:

Find

Code Select Expand

db_query("
UPDATE {$db_prefix}messages
SET ID_TOPIC = $newTopicID, ID_BOARD = $row[ID_BOARD]
WHERE ID_TOPIC = $row[ID_TOPIC]", __FILE__, __LINE__);

Replace

Code Select Expand

db_query("
UPDATE {$db_prefix}messages
SET ID_TOPIC = $newTopicID, ID_BOARD = $row[ID_BOARD]
WHERE ID_MSG IN ($row[myID_FIRST_MSG], $row[myID_LAST_MSG])", __FILE__, __LINE__);

[Adish - (F.L.A.M.E.R)]

it seems there are 2 database entries in repairboards.php

i am not totally sure if its exactly the same but it does look same. Shall i replace it twice?


sorry forgot to add the code here...

Code Select Expand

db_query("
UPDATE {$db_prefix}messages
SET ID_TOPIC = $newTopicID, ID_BOARD = $row[ID_BOARD]
WHERE ID_TOPIC = $row[ID_TOPIC]", __FILE__, __LINE__);
}
db_query("
UPDATE {$db_prefix}messages
SET ID_TOPIC = $newTopicID, ID_BOARD = $row[ID_BOARD]
WHERE ID_TOPIC = $row[ID_TOPIC]", __FILE__, __LINE__);
}


[Oldiesmann]

Get rid of the second one.

[Adish - (F.L.A.M.E.R)]

well.. magic..! it did work.. just a question..

Do i have to use only this repairboards.php whenever i put it over the live server ? cant any database edits be done which can help me not to find this edited repairboards.php and put it in ?

Although it happened that as i ran this i got errors and like ran it 10 times and atlast got rid of all the errors...

[Oldiesmann]

A regular un-modified RepairBoards.php file should be fine, as the issue that caused the problem isn't all that common. I'm reporting a bug about this though as the 'find and repair errors' functionality needs to check to make sure the stranded post isn't the first post in an existing topic before trying to create a new topic for it.

[Adish - (F.L.A.M.E.R)]

Well, so after i fix all the errors by this modified file, i should be able to use the normal un-modified version of repairboards.php on the site ?

And a question more, what exactly does it do ? according to the first dignostics..? as in the errors it was trying to fix..?

I need this solved soon comon..! bump.. it shows a long page of errors !!!!

Here is the list of all the errors it found. I need to fix them or else more errors might show up.

Code Select Expand

Message #11783 is in non-existent topic #0.
Message #11785 is in non-existent topic #0.
Message #11786 is in non-existent topic #0.
Message #11787 is in non-existent topic #0.
Message #11788 is in non-existent topic #0.
Message #11789 is in non-existent topic #0.
Message #11790 is in non-existent topic #0.
Message #11791 is in non-existent topic #0.
Message #11792 is in non-existent topic #0.
Message #11796 is in non-existent topic #0.
Message #11797 is in non-existent topic #0.
Message #11798 is in non-existent topic #0.
Message #11799 is in non-existent topic #0.
Message #11800 is in non-existent topic #0.
Message #11801 is in non-existent topic #0.
Message #11802 is in non-existent topic #0.
Message #11804 is in non-existent topic #0.
Message #11805 is in non-existent topic #0.
Message #11783 is in non-existent topic #0.
Message #11785 is in non-existent topic #0.
Message #11786 is in non-existent topic #0.
Message #11787 is in non-existent topic #0.
Message #11788 is in non-existent topic #0.
Message #11789 is in non-existent topic #0.
Message #11790 is in non-existent topic #0.
Message #11791 is in non-existent topic #0.
Message #11792 is in non-existent topic #0.
Message #11796 is in non-existent topic #0.
Message #11797 is in non-existent topic #0.
Message #11798 is in non-existent topic #0.
Message #11799 is in non-existent topic #0.
Message #11800 is in non-existent topic #0.
Message #11801 is in non-existent topic #0.
Message #11802 is in non-existent topic #0.
Message #11804 is in non-existent topic #0.
Message #11805 is in non-existent topic #0.
Topic #3607 has the first message ID 11783, which is incorrect.
Topic #3612 is marked as read for one or more people, but does not exist.
Member #5 has received one or more personal messages, but does not exist.
The subject of topic #3607 is currently not stored in the subject cache.

HELP !!!

I just wana know cause i have tried fixing once in my forum and it created some 5-10 boards called "Salvage" and some numbers and then later i moved the posts to accordingly to the sections.

This one didnt do it, i dont know what exactly did it fix.. Any message deletion that takes place ? or some moving..anything ?

[Oldiesmann]

The salvage board is created if:
A topic has an incorrect board ID (topic is "moved" to the salvage board)
A post has both an incorrect topic ID and incorrect board ID (new topic is created in the salvage board for that post)

The salvage board wasn't needed in this situation because the topic info already existed for the message.

It would take a while to explain every problem that the find and repair errors functionality handles...

[Adish - (F.L.A.M.E.R)]

aah its fine then.. hope there werent any side effects as such anywhere,I think the doc writers might write down something about them if its possible.

I thank you very much. I shall fix up the forum if anything else is in problem.

I will mark this as solved for now until i have any other problems regarding this.

I am very thankful to all the people who have attempted to help me out.

I hope i dont bring up these type of legendary problems again

[SN95Forums]

Ok Im having a serious problem and I need help.

I will PAY FOR GETTING THIS SORTED OUT.

Im using SMF 1.1.7

I have tried a modified repairsettings.php and it did not solve my problem.

----------------
The Problem:
----------------
When I go into my admin panel and go to "Find and repair any errors." I get a massive list of errors, bascially it seems every post # almost.

Here are some examples:

Message #107633 is in non-existent topic #0.
Message #111175 is in non-existent topic #0.
(About 2 pages of the above error)
Topic #12 has the last message ID 547876, which is incorrect.
Topic #116 has the last message ID 552866, which is incorrect.
Topic #633 has the last message ID 550764, which is incorrect.
Topic #870 has the last message ID 554212, which is incorrect.
(About 5 pages of the above error)
The subject of topic #252 is currently not stored in the subject cache.
The subject of topic #254 is currently not stored in the subject cache.
The subject of topic #259 is currently not stored in the subject cache.
The subject of topic #264 is currently not stored in the subject cache.
(About 25 pages of the above error)
Cached word '2003' is linked to a non-existent topic.
Cached word '2004' is linked to a non-existent topic.
Cached word '2004' is linked to a non-existent topic.
Cached word '2005-2008' is linked to a non-existent topic.
Cached word '2008' is linked to a non-existent topic.
(About 15 pages of the above error)

I click to Repair them and get this Error:

Database Error
Duplicate entry '107633-4' for key 3
File: /home/snforums/public_html/Sources/RepairBoards.php
Line: 190

I also need to optimize my database, since it apparently is overloading the server which I am hosted with.

Thanks,
Matt

[Rumbaar]

It's best to create your own topic, than post into a solved (closed) topic as it wont be seen by the majority of the Support team.

Give as much details relating to your issue in the new thread.