Posted: 2/28/2007 6:05 AM
Post subject: Majora's Mask thread is still broken
Quote
OmnipotentEntity
He/Him
Player (36)
Joined: 9/11/2004
Posts: 2631
Please Bisqwit, if you would: 
UPDATE `phpbb_topics` SET `topic_replies` = `topic_replies` - 1 WHERE `topic_id` = 267 LIMIT 1;
Build a man a fire, warm him for a day, Set a man on fire, warm him for the rest of his life.
Posted: 3/4/2007 8:24 AM
Post subject: Re: Majora's Mask thread is still broken
Quote
mwl
Joined: 3/22/2006
Posts: 636
While we're at it: 
UPDATE `phpbb_topics` SET `topic_title` = 'Majora\'s Mask' WHERE `topic_id` = 267 LIMIT 1;
Posted: 3/4/2007 12:15 PM
(Edited: 3/13/2007 11:50 AM)
Post subject: Re: Majora's Mask thread is still broken
Quote
Bisqwit
Editor, Active player (297)
Joined: 3/8/2004
Posts: 7469
Location: Arzareth
As for topic title, that was AngerFist's decision to type it like that. He can, however, edit his post, and the topic title will change. So bug him, not me :) As for the post count, I don't see an error there. But such an error was recently fixed anyway. Thirdly, I hate those extravagant quotes and I'm not using phpbb_ prefix in the table names anyway… EDIT: Underlined a part which nobody (i.e. mwl and OmnipotentEntity) seem to notice.
Posted: 3/4/2007 1:19 PM
Post subject: Re: Majora's Mask thread is still broken
Quote
Tub
Joined: 6/25/2005
Posts: 1377
Bisqwit wrote:
(And fourthly, I don't think LIMIT works with UPDATEs.)
yes, it does. Since topic_id is a primary key the LIMIT is useless, but it doesn't hurt either.
m00
Posted: 3/5/2007 12:56 AM
Post subject: Re: Majora's Mask thread is still broken
Quote
OmnipotentEntity
He/Him
Player (36)
Joined: 9/11/2004
Posts: 2631
Bisqwit wrote:
As for topic title, that was AngerFist's decision to type it like that. He can, however, edit his post, and the topic title will change. So bug him :) As for the post count, I don't see an error there. But such an error was recently fixed anyway. Thirdly, I hate those extravagant quotes and I'm not using <tt>phpbb_</tt> prefix in the table names anyway :) (And fourthly, I don't think LIMIT works with UPDATEs.)
I annoyed you about it on chat the same day I posted this, and you fixed it then. I think. We'll see what happens when a new page is spawned.
Build a man a fire, warm him for a day, Set a man on fire, warm him for the rest of his life.
Posted: 3/9/2007 3:25 AM
Quote
OmnipotentEntity
He/Him
Player (36)
Joined: 9/11/2004
Posts: 2631
Still doesn't seem to be fixed. phpbb still thinks that there's one more reply than there actually is, so between the time when $replies % 20 == 0 and $replies % 20 == 1 the last page returns "There are no posts in this topic." You can test this behavior by pointing your browser to http://tasvideos.org/forum/viewtopic.php?t=267&start=<The Number Of Replies to the Thread> On most topics this will return the very last reply of the thread and nothing else. On Majora's Mask's thread it returns an empty thread because phpBB thinks there's one more reply than there actually is. The reason I think your script failed to fix it is because the way phpBB handles the number of posts in a thread is funky. phpBB does not count the first post as a reply to the thread (presumptively because it's technically not a reply to anything.) So topic_replies holds $total_number_of_posts_in_thread - 1 Anyway, the SQL command that will fix Majora's Mask is again, 
UPDATE `phpbb_topics` SET `topic_replies` = `topic_replies` - 1 WHERE `topic_id` = 267 LIMIT 1;
Build a man a fire, warm him for a day, Set a man on fire, warm him for the rest of his life.
Posted: 3/13/2007 1:43 PM
Quote
Bisqwit
Editor, Active player (297)
Joined: 3/8/2004
Posts: 7469
Location: Arzareth
OmnipotentEntity wrote:
So topic_replies holds $total_number_of_posts_in_thread - 1
I already know this. This is what I use to fix the topics table whenever I do it. 
$SQL = 'update topics t set'.
           ' topic_first_post_id=( select min(post_id) from posts p where p.topic_id=t.topic_id )'.
           ',topic_last_post_id=( select max(post_id) from posts p where p.topic_id=t.topic_id )'.
           ',topic_time=( select min(post_time) from posts p where p.topic_id=t.topic_id )'.
           ',topic_replies=( select count(post_id)-1 from posts p where p.topic_id=t.topic_id ) ';
print "$SQL;\n"; ForumExecute($SQL);
Posted: 3/13/2007 1:56 PM
Quote
OmnipotentEntity
He/Him
Player (36)
Joined: 9/11/2004
Posts: 2631
That script doesn't verify whether or not a post_id has a corresponding entry in post_text. phpBB will silently fail to display that post_id if that is so.
Build a man a fire, warm him for a day, Set a man on fire, warm him for the rest of his life.
Posted: 3/13/2007 2:25 PM
Quote
Bisqwit
Editor, Active player (297)
Joined: 3/8/2004
Posts: 7469
Location: Arzareth
OmnipotentEntity wrote:
That script doesn't verify whether or not a post_id has a corresponding entry in post_text. phpBB will silently fail to display that post_id if that is so.
That part is already taken care by an orphan row check earlier in the script.
Posted: 3/13/2007 2:29 PM
Quote
OmnipotentEntity
He/Him
Player (36)
Joined: 9/11/2004
Posts: 2631
Well, I don't know what the problem is off of the top of my head then. But here's an example of what's going wrong valid as of right now. The Mario Kart 64 topic currently has 119 replies. http://tasvideos.org/forum/viewtopic.php?t=2970&start=119 The Majora's Mask top currently has 884 replies http://tasvideos.org/forum/viewtopic.php?t=267&start=884 I'll think on this while I'm at work today, but I've got to go.
Build a man a fire, warm him for a day, Set a man on fire, warm him for the rest of his life.