[FIX][RC] Prune bugs in phpBB 2.0.14 and less

A place for MOD Authors to post and receive feedback on MODs still in development. No MODs within this forum should be used within a live environment! No new topics are allowed in this forum.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

IMPORTANT: MOD Development Forum rules

On February 1, 2009 this forum will be set to read only as part of retiring of phpBB2.
Post Reply
chyduskam
Registered User
Posts: 110
Joined: Thu Oct 28, 2004 9:33 am
Location: Saint Petersburg, Russia
Contact:

[FIX][RC] Prune bugs in phpBB 2.0.14 and less

Post by chyduskam »

Prune SQL have a bug, that overload server. It use incorrect OR construction and so it use reference many-to-many nor one-to-one or one-to-many.
It's not secure bug, so I can't report about it at support page.
I place code here.

Solution

Code: Select all

##############################################################
## MOD Title: Fix prune bugs
## MOD Author: chyduskam < chuduskam@debilarius.ru > (Egor Naklonyaeff) N/A
## MOD Description: Fix stupid SQL in prune.php
## MOD Version: 0.0.1
##
## Installation Level: Easy
## Installation Time: 2 Minutes
## Files To Edit: 1
##    includes/prune.php
##    Included Files: n/a
##############################################################
## For Security Purposes, Please Check: http://www.phpbbguru.net/mods/ for the
## latest version of this MOD. Downloading this MOD from other sites could cause malicious code
## to enter into your phpBB Forum. As such, phpBB will not offer support for MOD's not offered
## in our MOD-Database, located at: http://www.phpbbguru.net/mods/
##############################################################
## Author Notes: I'm too lazy to write something
##############################################################
## MOD History:
##   2005-04-08 - Version 0.0.1
##      - initial release
##############################################################
## Before Adding This MOD To Your Forum, You Should Back Up All Files Related To This MOD
##############################################################

#
#-----[ OPEN ]-------------------------------------------------------
#
includes/prune.php

#
#-----[ FIND ]----------------------------------------
#
	global $db, $lang;

	$prune_all = ($prune_all) ? '' : 'AND t.topic_vote = 0 AND t.topic_type <> ' . POST_ANNOUNCE;
	//
	// Those without polls and announcements ... unless told otherwise!
	//
	$sql = "SELECT t.topic_id
		FROM " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t
		WHERE t.forum_id = $forum_id
			$prune_all
			AND ( p.post_id = t.topic_last_post_id
				OR t.topic_last_post_id = 0 )";
	if ( $prune_date != '' )
	{
		$sql .= " AND p.post_time < $prune_date";
	}

	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not obtain lists of topics to prune', '', __LINE__, __FILE__, $sql);
	}

	$sql_topics = '';
	while( $row = $db->sql_fetchrow($result) )
	{
		$sql_topics .= ( ( $sql_topics != '' ) ? ', ' : '' ) . $row['topic_id'];
	}
	$db->sql_freeresult($result);

#
#-----[ REPLACE WITH ]----------------------------------
#
	global $db, $lang;

	$prune_all = ($prune_all) ? '' : 'AND t.topic_vote = 0 AND t.topic_type <> ' . POST_ANNOUNCE;
	$sql_prune_data=( $prune_date != '' )? "AND p.post_time < $prune_date":'';
	//
	// Those without polls and announcements ... unless told otherwise!
	//
	$sql = "SELECT t.topic_id
		FROM " . POSTS_TABLE . " p, " . TOPICS_TABLE . " t
		WHERE t.forum_id = $forum_id
			$prune_all
            $sql_prune_data
			AND p.post_id = t.topic_last_post_id";
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not obtain lists of topics to prune', '', __LINE__, __FILE__, $sql);
	}

	$sql_topics = '';
	while( $row = $db->sql_fetchrow($result) )
	{
		$sql_topics .= ( ( $sql_topics != '' ) ? ', ' : '' ) . $row['topic_id'];
	}
	$db->sql_freeresult($result);
// Fix stupid bug
	$sql = "SELECT t.topic_id
		FROM " . TOPICS_TABLE . " t
		WHERE t.forum_id = $forum_id
		$prune_all
		AND t.topic_last_post_id = 0";
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Could not obtain lists of topics to prune', '', __LINE__, __FILE__, $sql);
	}
	while( $row = $db->sql_fetchrow($result) )
	{
		$sql_topics .= ( ( $sql_topics != '' ) ? ', ' : '' ) . $row['topic_id'];
	}
	$db->sql_freeresult($result);


#
#-----[ SAVE/CLOSE ALL FILES ]------------------------------------------
#
# EoM
User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Post by beggers »

Do our resident phpBB experts have any comments on this?
User avatar
alsakrah
Registered User
Posts: 166
Joined: Wed Dec 04, 2002 7:54 pm
Contact:

Post by alsakrah »

beggers wrote: Do our resident phpBB experts have any comments on this?


8O 8O 8O

ThanX waiting for phpBB team reply :?
chococat
Registered User
Posts: 9
Joined: Fri Apr 22, 2005 8:16 pm
Contact:

Post by chococat »

I work daily with MySQL and yes, that query is wrong. The OR match each topic with t.topic_last_post_id = 0 with all the posts in the forum. Say you have 100000+ messages in your forum (as in my case), you will get 100000+ repeated rows for each topic with that condition. Ther server is easily overloaded, if not here, in the next query that includes all the repeated rows in a IN statement.
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Post by drathbun »

For those of us that are lazy, how 'bout posting the exact lines that are changed instead of the entire block of code. :-)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
MaddoxX
Registered User
Posts: 450
Joined: Sat May 15, 2004 4:43 pm
Location: Russia

Post by MaddoxX »

drathbun wrote: For those of us that are lazy, how 'bout posting the exact lines that are changed instead of the entire block of code. :-)


meh :p to many annoying changes =) just replace the piece :D
:D
Saint Keith
Registered User
Posts: 56
Joined: Sat Aug 09, 2003 8:19 pm
Contact:

Post by Saint Keith »

This Mod eliminated the Prune bug that I had. Many thanks :D
davidh44
Registered User
Posts: 386
Joined: Sat Mar 09, 2002 5:56 am

Post by davidh44 »

Thanks for the mod! I like anything that reduces server load.
Up the Junction
Registered User
Posts: 23
Joined: Wed Apr 02, 2003 10:26 pm

Post by Up the Junction »

A big thankyou to chyduskam for this, you've saved my bacon. Had terrible grief as a result of using the auto prune function; it kept dropping half way through which subsequently rendered that particular forum unusable - until I was able to apply this fix.

Much appreciated.
pfcouto
Registered User
Posts: 11
Joined: Thu Apr 11, 2002 3:57 pm

Post by pfcouto »

in fact, the error is a missing JOIN index key...

Code: Select all

		WHERE t.forum_id = $forum_id

------------>        AND p.topic_id = t.topic_id      <-------- index key missing

			$prune_all 
			AND ( p.post_id = t.topic_last_post_id 
				OR t.topic_last_post_id = 0 )";
with this key, the query runs instantly.

no other fix are necessary.
User avatar
smithy_dll
Former Team Member
Posts: 7630
Joined: Tue Jan 08, 2002 6:27 am
Location: Australia
Name: Lachlan Smith
Contact:

Post by smithy_dll »

Hello,

Could you please edit your topic title with a prefix as outlined in MOD Development forum rules section 3.a, 3.b. The prefix you are currently using is not allowed. If your MOD is still in development you can use the prefixes in section 3.a. If your MOD is stable could you please submit your MOD to the phpBB.com MODDB (MOD release database), for validation pending release.

Your co-operation is very much appreciated by the phpBB MOD Team.

Thank you,
The phpBB MOD Team
Systems Engineering
MeltedMonitor
Registered User
Posts: 54
Joined: Fri Apr 11, 2003 3:59 am
Location: Portland OR

Post by MeltedMonitor »

chyduskam's new code worked! I tried pfcouto's code first, but still had a problem. With the complete code change it worked like a charm!
User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Post by beggers »

MeltedMonitor wrote: chyduskam's new code worked! I tried pfcouto's code first, but still had a problem. With the complete code change it worked like a charm!


What version of phpBB are you running? I kind of assumed this would already have been fixed by now.
Post Reply

Return to “[2.0.x] MODs in Development”