[2.0.6] Prune User Posts

The cleanup is complete. This forum is now read only.

Rating:

Excellent!
21
66%
Very Good
5
16%
Good
3
9%
Fair
0
No votes
Poor
3
9%
 
Total votes: 32

GrrrlRomeo
Registered User
Posts: 27
Joined: Sun Jul 13, 2003 4:21 am

Post by GrrrlRomeo » Thu Jan 29, 2004 11:11 pm

I'm trying to prune all the locked topics within a certain range of time...even though I have locked topics in the range I enter it says "Unable to start pruning - No posts were found to prune". :?
I don't know why I bother.

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Sat Jan 31, 2004 12:06 am

Hmm, will investigate the probable bug. Thanks.

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Bug

Post by afx114 » Wed Feb 11, 2004 11:59 pm

I am getting the following error:

Code: Select all

phpBB : Critical Error

Could not select posts count forum total

DEBUG MODE

SQL Error : 1064 You have an error in your SQL syntax near '' at line 3

SELECT COUNT(post_id) as numrows FROM phpbb_posts WHERE forum_id =

Line : 1191
File : /path/to/phpBB2/admin/admin_prune_user_posts.php
Here are the options I'm using:
Forums To Prune: One forum selected
Users To Prune: All Users
Date Criteria: From (entered some date range)
Pruning Options
- Remove Topics by Users = Yes
- Exempt Stickies = No
- Exempt Announcements = No
- Exempt Open Topics = No
- Exempt Polls = No
- Adjust Post Counts = Yes
- Update Search Tables = Yes

The topics get pruned as expected, but I'm guessing that the Post Counts and Search Tables are not being properly updated.

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Post by afx114 » Thu Feb 12, 2004 12:12 am

Sure enough, I turned set "Adjust Post Counts" to NO and everything runs as planned without the PHP error. Looks like there's a bug in that SQL:

SELECT COUNT(post_id) as numrows FROM phpbb_posts WHERE forum_id =

forum_id is not defined.

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Thu Feb 12, 2004 12:26 pm

Thanks for reporting. There are a couple of bugs I need to address in 1.2.1 when I get a chance.

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Thu Feb 12, 2004 7:09 pm

afx114 wrote: Sure enough, I turned set "Adjust Post Counts" to NO and everything runs as planned without the PHP error. Looks like there's a bug in that SQL:

SELECT COUNT(post_id) as numrows FROM phpbb_posts WHERE forum_id =

forum_id is not defined.

Okay, could not reproduce error. The only way this could happen is if your forums table has some bad entries OR a post in your database has an invalid forum entry in a post record, at first glance. I'll do some more code testing, but do you have any MODs on your forum?

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Post by afx114 » Thu Feb 12, 2004 7:22 pm

R45 wrote: Okay, could not reproduce error. The only way this could happen is if your forums table has some bad entries OR a post in your database has an invalid forum entry in a post record, at first glance. I'll do some more code testing, but do you have any MODs on your forum?


Nope, no other MODs. I looked in my DB and could not find any bad entries in the forums table or any posts or topics with a blank forum_id column.

You can get around the problem by verifying that forum_id has a value before executing that SQL statement. But that might just be masking the problem (if there really is one).

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Thu Feb 12, 2004 8:41 pm

Roger, but that doesn't solve the problem.

Can you try

Code: Select all

SELECT * FROM phpbb_posts WHERE forum_id IS NULL
And see if you get any problems?

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Post by afx114 » Thu Feb 12, 2004 8:44 pm

R45 wrote: Can you try

Code: Select all

SELECT * FROM phpbb_posts WHERE forum_id IS NULL
And see if you get any problems?


0 rows returned.

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Post by afx114 » Thu Feb 12, 2004 8:45 pm

P.S., that is a NOT NULL column in the DB, so a row with NULL in that column cannot be inserted.

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Thu Feb 12, 2004 8:57 pm

afx114 wrote: P.S., that is a NOT NULL column in the DB, so a row with NULL in that column cannot be inserted.

Well aware of the fact, however it happens with some RDMS.

Regardless, I'm curious as to why this is happening. Three possible queries that fill the array to update forums

This:

Code: Select all

	$sql = "SELECT forum_id
				FROM ".FORUMS_TABLE;
Or this:

Code: Select all

			$sql_array[] = "SELECT p.post_id, p.topic_id, p.poster_id, p.forum_id, t.topic_poster, t.topic_vote, t.topic_first_post_id, t.topic_last_post_id
							FROM ".POSTS_TABLE." as p, ".TOPICS_TABLE." as t
								".$where_sql." AND p.poster_id = ".$users[$i]['user_id'];
Or

Code: Select all

			$sql_array[] = "SELECT p.post_id, p.topic_id, p.poster_id, p.forum_id, t.topic_poster, t.topic_vote, t.topic_first_post_id, t.topic_last_post_id
						FROM ".POSTS_TABLE." as p, ".TOPICS_TABLE." as t
							".$where_sql.$ip_sql;
All of which I cannot see returning an empty value unless the data is corrupted. Since I can't reproduce, will you be willing to add some code to your DBAL to echo every query run on a page, then paste them here

In your database's specific file in /db/, add under

Code: Select all

	function sql_query($query = "", $transaction = false)
	{
add

Code: Select all

global $query_cache;

$query_cache .= "<pre>$query</pre><br /><br />";
Then in your page_footer_admin.php , under

Code: Select all

$db->sql_close();
add

Code: Select all

global $query_cache;

echo $query_cache;
It will output the queries run at the bottom of each admin page. You can always undo it when you're done.

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Queries

Post by afx114 » Thu Feb 12, 2004 9:24 pm

Queries on form page:

Code: Select all

SELECT *
	FROM phpbb_config



SELECT u.*, s.*
			FROM phpbb_sessions s, phpbb_users u
			WHERE s.session_id = '014da822b28d25132118ff374aabf88b'
				AND u.user_id = s.session_user_id



UPDATE phpbb_sessions 
						SET session_time = 1076619523, session_page = 0 
						WHERE session_id = '014da822b28d25132118ff374aabf88b'



UPDATE phpbb_users 
							SET user_session_time = 1076619523, user_session_page = 0 
							WHERE user_id = 2



DELETE FROM phpbb_sessions 
						WHERE session_time < 1076615923 
							AND session_id <> '014da822b28d25132118ff374aabf88b'



SELECT *
		FROM phpbb_themes
		WHERE themes_id = 2



SELECT group_id, group_name
				FROM phpbb_groups
					WHERE group_single_user = 0
						ORDER BY group_name



SELECT COUNT(ban_id) as numrows
				FROM phpbb_banlist
					WHERE ban_userid <> 0



SELECT COUNT(ban_id) as numrows
				FROM phpbb_banlist
					WHERE ban_userid = 0



SELECT cat_id, cat_title
				FROM phpbb_categories
					ORDER BY cat_order ASC



SELECT forum_id, forum_name
					FROM phpbb_forums
						WHERE cat_id = 2
							ORDER BY forum_order ASC
But now I am unable to reproduce when submitting the form. Now I get just a blank page with the following source!

Code: Select all

<html><body></body></html>
I can't keep doing this over and over because my this is on my production site. I will try loading an older version of the DB into my development environment and try it there.

Also, unrelated, but I think it would be wise for you to change the default selected radio button of the Date Criteria from "All Posts" !!!! The combination of "All Users" and "All Dates" is can very easily lead to accidental loss of all posts, I've come very close to doing so myself! Force the admin to specifically and manually select "All Posts" in order to avoid massive meltdown. Or perhaps a JavaScript confirmation box: "Are you sure you want to delete posts from All Users and All Dates? This will delete every single post in the DB!!!"

R45
Registered User
Posts: 2830
Joined: Tue Nov 27, 2001 10:42 pm

Post by R45 » Fri Feb 13, 2004 1:12 pm

Temporarily disable Gzip while running submitting to prune. I appreciate your help.

Re changing the default checkings, well "All Users" is not checked by default. A confirmation box might be useful indeed.

afx114
Registered User
Posts: 10
Joined: Wed Feb 11, 2004 11:52 pm

Post by afx114 » Fri Feb 13, 2004 5:08 pm

R45 wrote: Re changing the default checkings, well "All Users" is not checked by default. A confirmation box might be useful indeed.


Yes, All Users is not checked by default, but I think a lot of people will be using this mod to do things like: "Delete all posts prior to dd/mm/yyyy"
When using the mod in this way, it becomes quite easy to accidentally delete ALL posts by forgetting to put in a date range.

Nautilus_Xtrem!!
Registered User
Posts: 15
Joined: Tue Mar 02, 2004 4:27 am

Post by Nautilus_Xtrem!! » Wed Mar 03, 2004 12:29 am

Can someone help me I installed the mod and all that jazz but when I click on the prune posts link in the admin section I get this error:
Template->make_filename(): Error - file does not exist

Kinda new to all this so any help will be greatly appreciated.

Post Reply

Return to “[2.0.x] MOD Database Cleanup”