Simple Machines Community Forum

SMF Support => SMF 1.1.x Support => Topic started by: humbleworld on June 02, 2010, 10:26:03 PM

Title: Repair Error: Duplicate Entry
Post by: humbleworld on June 02, 2010, 10:26:03 PM
I ran forum maintenance and repaired errors. This what I got:

Message #316086 is in non-existent topic #0.
Message #328622 is in non-existent topic #0.
Message #328623 is in non-existent topic #0.
Message #330998 is in non-existent topic #0.
Message #330999 is in non-existent topic #0.
Message #331000 is in non-existent topic #0.
Message #331976 is in non-existent topic #0.
Message #331977 is in non-existent topic #0.
Message #333434 is in non-existent topic #0.
Message #353685 is in non-existent topic #0.
Message #353686 is in non-existent topic #0.
Message #353694 is in non-existent topic #0.
Message #363674 is in non-existent topic #0.
Message #363675 is in non-existent topic #0.
Message #365254 is in non-existent topic #0.
Message #365755 is in non-existent topic #0.

a lot of this... i just cut it here...

Topic #11 has the wrong number of replies, 40.
Topic #344 has the wrong number of replies, 7.
Topic #1449 has the wrong number of replies, 3.
Topic #1583 has the wrong number of replies, 671.
Topic #1692 has the last message ID 316604, which is incorrect.
Topic #1692 has the wrong number of replies, 13.
Topic #5261 has the wrong number of replies, 19.
Topic #5657 has the wrong number of replies, 606.
Topic #6794 has the wrong number of replies, 822.
Topic #7781 has the wrong number of replies, 7126.
Topic #9466 has the wrong number of replies, 705.
Topic #9560 has the wrong number of replies, 595.
Topic #9634 has the last message ID 316467, which is incorrect.

and more like this...

So I chose "YES" in repairing the errors, but I got this message:


Duplicate entry '316086-145' for key 3
File: /home/cpanel/public_html/myforum/Sources/RepairBoards.php
Line: 190

What is that duplicate entry? And in line 190?
Title: Re: Repair Error: Duplicate Entry
Post by: Norv on June 04, 2010, 09:22:09 AM
Please post as attachment your file myforum/Sources/RepairBoards.php, for us to be sure.

Your posts/topics seem quite messed up... Did something happen lately? Like a database crash or something?
Just wondering, it can be solved in a certain measure, that is, all posts can be recovered but it seems that some of them might not be in the right topics anymore.

Do you have database backups, and how old are they?
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 04, 2010, 09:48:46 AM
Dear Norv,

I have attached the repairboards file.

I had been experiencing database crash several times when my vps ram wasn't enough.

My posts are okay, only that I moved hundreds of threads to another section and deleted that section. I am trimming my forum site. There were too many boards. So I moved hundreds of threads.

All topics can be opened but very slow.

My vps host says that my mysql processes are not optimized, causing my cpu load very high all the time though there are few visitors in my forum site.

Thanks Norv for your advice.
Title: Re: Repair Error: Duplicate Entry
Post by: Norv on June 04, 2010, 11:35:42 AM
Could you please select all messages of Fix and repair, copy them into a text file, and attach that file here? For us to see them all, if possible.

To see if there are topics with problems, please, try to see if anything happens when you go to: http://your_forum_address/index.php?topic=11.0
or, http://your_forum_address/index.php?topic=344.0

I tend to think that suite of a database crash or something which went very wrong there can be situations which weren't predicted and correctly handled by the repair boards feature...
Please can you login in phpmyadmin, and run:

SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG
FROM smf_topics
WHERE ID_FIRST_MSG = 316086


SELECT ID_TOPIC, ID_MSG
FROM smf_messages
WHERE ID_MSG = 316086
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 04, 2010, 11:50:14 AM
Dear Norv,

I am attaching here the list of errors after running find and repair errors.
The URLs have changed since I installed prettyURLs mod.

If I click yes to repair, I would get this message:

Duplicate entry '316086-145' for key 3
File:   /home/cpanel/public_html/myforum/Sources/RepairBoards.php
Line: 190
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 05, 2010, 04:10:31 AM
Hi Norv,

For the first sql query, this is the result:

ID_TOPIC
24534

ID_BOARD
145

ID_FIRST_MSG
316086

For the second command, here's the result:

ID_TOPIC
0

ID_MSG
316086
Title: Re: Repair Error: Duplicate Entry
Post by: Norv on June 05, 2010, 04:23:15 AM
Okay, thank you. Please, run:

UPDATE smf_messages SET ID_TOPIC = 24534 WHERE ID_MSG = 316086;


At this point, you shouldn't get that particular error when running Fix anymore...

Then, please run (and tell the results :)):

SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG, ID_LAST_MSG
FROM smf_topics
WHERE ID_TOPIC IN (11, 344, 1449, 1583, 1692, 5261, 5657, 6794, 7781, 9466, 9560, 1692, 9634)


SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG, ID_LAST_MSG
FROM smf_topics
WHERE ID_LAST_MSG = 316604


SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG, ID_LAST_MSG
FROM smf_topics
WHERE ID_LAST_MSG = 316467

Screenshots with the results (if it's too much to copy :)) would also be an option. Or, as you wish.

Please note that it's untested. Make sure you have a database backup before using these queries.
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 05, 2010, 04:38:21 AM
Hello Norv,

This is an update. This is my latest stats:

368,423 Posts in 28,199 Topics by 3776 Members.

Will your command be the same?
Title: Re: Repair Error: Duplicate Entry
Post by: Norv on June 05, 2010, 04:39:49 AM
Absolutely, they're the same. The commands deal with specific topics and posts (those the repair feature complained about), trying to see what is wrong with them. Please do run them. (after making a database backup :))
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 05, 2010, 04:43:20 AM
Thanks Norv. I will get back to you once I'm done making a backup. Database is almost 500MB.
Then I will post the screenshots here.
Title: Re: Repair Error: Duplicate Entry
Post by: Adish - (F.L.A.M.E.R) on June 29, 2010, 05:39:32 PM
Any updates on this ?
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 30, 2010, 06:00:57 PM
Hello Norv,

Here are the results of the sql queries and their screenshots

First sql query:
SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG
FROM   smf_topics
WHERE ID_FIRST_MSG = 316086


(http://farm5.static.flickr.com/4078/4750336784_3b23490c05_b.jpg)

2nd SQL Query

SELECT ID_TOPIC, ID_MSG
FROM smf_messages
WHERE   ID_MSG = 316086


(http://farm5.static.flickr.com/4073/4750336788_47163f38be_b.jpg)

3rd SQL Query

SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG,   ID_LAST_MSG
FROM smf_topics
WHERE ID_TOPIC IN (11, 344, 1449,   1583, 1692, 5261, 5657, 6794, 7781, 9466, 9560, 1692, 9634)


(http://farm5.static.flickr.com/4080/4750336790_7c6321b002_b.jpg)

4th SQL Query

SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG,   ID_LAST_MSG
FROM smf_topics
WHERE ID_LAST_MSG = 316604


(http://farm5.static.flickr.com/4074/4750336792_26c8f27318_b.jpg)

5th SQL Query

SELECT ID_TOPIC, ID_BOARD, ID_FIRST_MSG,   ID_LAST_MSG
FROM smf_topics
WHERE ID_LAST_MSG = 316467


(http://farm5.static.flickr.com/4095/4750336794_a8e58c2024_b.jpg)

Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 30, 2010, 06:09:45 PM
Norv,

I ran "find and repair errors", I still got this message:

Duplicate entry '316086-145' for key 3
File:   /home/cpanel/public_html/forum/Sources/RepairBoards.php
Line:   190           

The list of errors has grown to tens of thousands. Please see attached txt file (size 1.85 MB).

Many of the additional errors look like this:

The subject of topic #26426 is currently not stored in the subject cache.
The subject of topic #26427 is currently not stored in the subject cache.
The subject of topic #26428 is currently not stored in the subject cache.
The subject of topic #26429 is currently not stored in the subject cache.
The subject of topic #26430 is currently not stored in the subject cache.
The subject of topic #26431 is currently not stored in the subject cache.
The subject of topic #26432 is currently not stored in the subject cache.
The subject of topic #26433 is currently not stored in the subject cache.
The subject of topic #26434 is currently not stored in the subject cache.
The subject of topic #26435 is currently not stored in the subject cache.
The subject of topic #26437 is currently not stored in the subject cache.
The subject of topic #26438 is currently not stored in the subject cache.
The subject of topic #26439 is currently not stored in the subject cache.
The subject of topic #26440 is currently not stored in the subject cache.
The subject of topic #27870 is currently not stored in the subject cache.
The subject of topic #27871 is currently not stored in the subject cache.
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on June 30, 2010, 06:31:35 PM
And if I ran this in the browser:

http://forumname.com/index.php?action=316086 I landed on the homepage URL, yet the URL I typed is still there.

But if I typed this in the browser: http://forumname.com/index.php?topic=316086 I got this error message:

An Error Has Occurred!
The topic or board you are looking for appears to be either missing or   off limits to you.           
Title: Re: Repair Error: Duplicate Entry
Post by: Norv on July 19, 2010, 04:54:52 PM
Sorry for the delay on this, humbleworld. Is the problem the same? Did anything happen meanwhile?
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on July 21, 2010, 05:02:18 AM
Hello Norv,

Please see my previous posts. I followed all your instruction and executed the sql commands.

Yet the problem remains.

I'm still at lost.
Title: Re: Repair Error: Duplicate Entry
Post by: xenovanis on July 29, 2010, 09:37:04 AM
Humblewold, do you still need assistance with this?

I found this topic (http://www.simplemachines.org/community/index.php?topic=368171.msg2518879#msg2518879) while searching for a solution for a similar problem.

In your case, you'll need to run these queries in phpMyAdmin:

UPDATE smf_topics SET ID_FIRST_MSG = 0 WHERE ID_FIRST_MSG = 316086;
UPDATE smf_topics SET ID_LAST_MSG = 0 WHERE ID_LAST_MSG = 316086;
Title: Re: Repair Error: Duplicate Entry
Post by: humbleworld on August 13, 2010, 01:13:01 PM
Thank you Xenovanis. The sql query had finally fixed the error.
SMF Community rocks! Thank you, thank you, everyone, and to you Norv, Xenovanis, and Oldiesmann...