Repair Error: Duplicate Entry

Started by humbleworld, June 02, 2010, 10:26:03 PM

Previous topic - Next topic

humbleworld

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?

Norv

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?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

humbleworld

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.

Norv

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
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

humbleworld

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

humbleworld

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

Norv

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.
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

humbleworld

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?

Norv

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 :))
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

humbleworld

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.

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


humbleworld

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




2nd SQL Query

SELECT ID_TOPIC, ID_MSG
FROM smf_messages
WHERE   ID_MSG = 316086




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)




4th SQL Query

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




5th SQL Query

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





humbleworld

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.

humbleworld

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.           

Norv

Sorry for the delay on this, humbleworld. Is the problem the same? Did anything happen meanwhile?
To-do lists are for deferral. The more things you write down the later they're done... until you have 100s of lists of things you don't do.

File a security report | Developers' Blog | Bug Tracker


Also known as Norv on D* | Norv N. on G+ | Norv on Github

humbleworld

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.

xenovanis

Humblewold, do you still need assistance with this?

I found this topic 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;
"Insanity: doing the same thing over and over again and expecting different results."

humbleworld

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...

Advertisement: