Advertisement:

Author Topic: Database Error when running Find and Repair Any Errors  (Read 3320 times)

Offline imogen

  • Semi-Newbie
  • *
  • Posts: 17
Database Error when running Find and Repair Any Errors
« on: June 29, 2008, 09:38:54 AM »
I run find and repair any errors in Admin panel.

get the following list.

Code: [Select]
The following errors are fouling up your forum:
Message #20648 is in non-existent topic #0.
Message #20649 is in non-existent topic #3090.
Message #20648 is in non-existent topic #0.
Message #20649 is in non-existent topic #3090.
Topic #3089 has the first message ID 20648, which is incorrect.
Topic #3090 is marked as read for one or more people, but does not exist.
Topic #3090 is marked as read for one or more people, but does not exist.
Member #165 has received one or more personal messages, but does not exist.
Member #274 has received one or more personal messages, but does not exist.
Member #324 has received one or more personal messages, but does not exist.
Member #512 has received one or more personal messages, but does not exist.
Member #517 has received one or more personal messages, but does not exist.
Member #527 has received one or more personal messages, but does not exist.
Member #538 has received one or more personal messages, but does not exist.
Member #540 has received one or more personal messages, but does not exist.
The subject of topic #3088 is currently not stored in the subject cache.
The subject of topic #3089 is currently not stored in the subject cache.

Would you like to fix these errors?
Yes - No

Select Yes and get this error.

Code: [Select]
Duplicate entry '20648-62' for key 3
File: /home/service2/public_html/forum2/Sources/RepairBoards.php
Line: 190

I have had a quick look on repairboards.php and I cant see any duplicate lines.

can anyone suggest what I need to do to fix this?

Database and files are on 1.1.5.

Cheers
« Last Edit: July 04, 2008, 05:29:09 PM by imogen »

Offline Jade Elizabeth

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,643
  • Gender: Female
  • Keep your enemies close and your friends closer ;)
    • jadetrainor on LinkedIn
    • @alundra on Twitter
    • Jades-World
Re: Database Error when running Find and Repair Any Errors
« Reply #1 on: July 02, 2008, 01:14:49 AM »
Have you installed any modifications?
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring forum: Color Me Forum/Patreon.

Offline searchgr

  • Sophist Member
  • *****
  • Posts: 1,247
Re: Database Error when running Find and Repair Any Errors
« Reply #2 on: July 02, 2008, 01:18:48 AM »
Did you delete these users?

Offline maxringo

  • Jr. Member
  • **
  • Posts: 199
Re: Database Error when running Find and Repair Any Errors
« Reply #3 on: July 02, 2008, 02:59:53 AM »
Have you moved your forum lately?

Offline Jade Elizabeth

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,643
  • Gender: Female
  • Keep your enemies close and your friends closer ;)
    • jadetrainor on LinkedIn
    • @alundra on Twitter
    • Jades-World
Re: Database Error when running Find and Repair Any Errors
« Reply #4 on: July 02, 2008, 03:07:25 AM »
Okay guys, these errors come from deleting users and maybe topics. It has nothing to do with moving the forums. The user is asking for help with the error when trying to repair these, the errors they're trying to repair arent the problem :).

Can I also have 5 lines above and below that line it mentions?
Please put these in a code box and label the line causing issues for me :).
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring forum: Color Me Forum/Patreon.

Offline maxringo

  • Jr. Member
  • **
  • Posts: 199
Re: Database Error when running Find and Repair Any Errors
« Reply #5 on: July 02, 2008, 03:13:18 AM »
You may be right Alundra, but a lot of times you get duplicate entries when you are trying to import or restore your forum onto something that is already there. 

« Last Edit: July 02, 2008, 03:16:47 AM by maxringo »

Offline imogen

  • Semi-Newbie
  • *
  • Posts: 17
Re: Database Error when running Find and Repair Any Errors
« Reply #6 on: July 02, 2008, 09:47:23 AM »
Ok slight update.

It is an old forum that has been upgraded a couple of times.
No new data is being imported and the forum has not moved for a long time now. Well before these errors started appearing.
I have deleted a few users recently.

Mods installed are.

1.     YouTube BBCode      2.2
2.    Invite    1.14
3.    PM On Registration    1.0.2
4.    WoW-Itemstats 1.25.8    1.25.8
5.    Enhanced Calendar    1.1
6.    SMF 1.0.12 / 1.1.4 / 2.0 b1.1 Update    1.0
7.    SMF 1.0.13 / 1.1.5 / 2.0 b3.1 Update    1.0
8.    Custom Action Mod    2.04
9.    SMF 1.0.12 / 1.1.4 / 2.0 b1.1 Update    1.0
10.    Itemstats BBC Tag (XHTML Compliant)    1.0
11.    Karma Description Mod    2.3
12.    Treasury    2.05
13.    TinyPortal    0.983
14.    Default Membergroup on Registration    1.1
15.    MessagePreviewOnHover    1.5
16.    Spoiler Tag    0.3.2
17.    Who Voted What?    1.1.2
18.    Reason For Editing Mod    1.14

Lines of code mentioned are

Code: [Select]

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]
Row 190 according to the editor I am using is
Code: [Select]
$row[myID_FIRST_MSG], $row[myID_LAST_MSG], $row[myNumReplies])", __FILE__, __LINE__);


Cheers,

Offline H

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 21,662
  • Gender: Male
Re: Database Error when running Find and Repair Any Errors
« Reply #7 on: July 03, 2008, 09:42:55 AM »
I believe these errors are normally caused when MySQL messes up as I don't think you should be able to intentionally create a duplicate key.

The duplicate key will be in the DB so you'll need to try and remove it with phpmyadmin. See: What is phpMyAdmin?. The duplicate key should be in the ID column in smf_messages, smf_topics or smf_boards. When you find it you should be able to just delete that row.
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Offline imogen

  • Semi-Newbie
  • *
  • Posts: 17
Re: Database Error when running Find and Repair Any Errors
« Reply #8 on: July 03, 2008, 10:17:37 AM »
I have checked those tables for a duplicate ID LIKE 20648 but not found any duplicates.

My SQL however only extend to simple select statements :) so if anyone can suggest an SQL query that will show me any duplicate records it would be appreciated :)

I am using this to try and show any duplicate messages which I think should do the trick
Code: [Select]
select 'ID_MSG', count(*) from `smf_messages` group by 'ID_MSG' HAVING count(*) > 1;
« Last Edit: July 03, 2008, 10:22:37 AM by imogen »

Offline Jade Elizabeth

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,643
  • Gender: Female
  • Keep your enemies close and your friends closer ;)
    • jadetrainor on LinkedIn
    • @alundra on Twitter
    • Jades-World
Re: Database Error when running Find and Repair Any Errors
« Reply #9 on: July 03, 2008, 05:40:28 PM »
I think it's in the topics table, cause the ID belongs to a topic. I dont know what the 62 is though. Maybe just look for duplicates, not ID's?
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring forum: Color Me Forum/Patreon.

Offline imogen

  • Semi-Newbie
  • *
  • Posts: 17
Re: Database Error when running Find and Repair Any Errors
« Reply #10 on: July 04, 2008, 06:12:52 AM »
I found topic 20648 but there is only one, topic 20648-62 doesnt exist.

Offline Jade Elizabeth

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,643
  • Gender: Female
  • Keep your enemies close and your friends closer ;)
    • jadetrainor on LinkedIn
    • @alundra on Twitter
    • Jades-World
Re: Database Error when running Find and Repair Any Errors
« Reply #11 on: July 04, 2008, 11:35:44 AM »
Maybe try repairing/optimising through phpmyadmin and see if that helps any?
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring forum: Color Me Forum/Patreon.

Offline imogen

  • Semi-Newbie
  • *
  • Posts: 17
Re: Database Error when running Find and Repair Any Errors
« Reply #12 on: July 04, 2008, 04:19:27 PM »
Thanks for the help so far it is appreciated.

Checked tables in phpmyadmin - No errors
Repaired anyway and optimised anyway.

Still get the same error. :(

Offline Jade Elizabeth

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,643
  • Gender: Female
  • Keep your enemies close and your friends closer ;)
    • jadetrainor on LinkedIn
    • @alundra on Twitter
    • Jades-World
Re: Database Error when running Find and Repair Any Errors
« Reply #13 on: July 04, 2008, 04:23:47 PM »
Can we have a data dump of the topics and boards tables (If they arent a billion MB's big)?
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring forum: Color Me Forum/Patreon.

Offline imogen

  • Semi-Newbie
  • *
  • Posts: 17
Re: Database Error when running Find and Repair Any Errors
« Reply #14 on: July 04, 2008, 05:13:03 PM »
BTW the -62 on the end refers to the ID_Board

I did something I should have done at the start. I read the message. Went and found the topic. Found the next post which was by one of the moderators saying he had an error message when he tried merging the thread but it all looked ok. I created a new post and merged again this time with no errors. Ran the repair any errors. Got the same list of problems but when I clicked yes to fix it actually worked this time!

Thanks for all your help Alundra :D

Offline Jade Elizabeth

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,643
  • Gender: Female
  • Keep your enemies close and your friends closer ;)
    • jadetrainor on LinkedIn
    • @alundra on Twitter
    • Jades-World
Re: Database Error when running Find and Repair Any Errors
« Reply #15 on: July 04, 2008, 06:42:24 PM »
You're welcome, and thanks to H for pointing it out to us :D
Once proud Documentation Writer and Help Squad Leader | Check out my new adult coloring forum: Color Me Forum/Patreon.