Previous Thread
Next Thread
Print Thread
Hop To
#252082 01/16/2013 12:21 AM
Joined: Mar 2007
Posts: 522
Addict
Addict
Joined: Mar 2007
Posts: 522
And it has been, and I'm guessing that I'm not the only one.

I've been using the referrer log for a long time, and I have a busy forum. Even though I refer ( laugh ) to it all the time, I have not been maintaining it.

I was looking at the database, and the referrer log table had blossomed to close to two million (2,000,000!!!) rows. I have now pruned it to about 100,000 rows. Even that is taking up 26MB.

The same goes for the admin log, although it isn't nearly as large, and might be more important to keep. Mine is at 110,000 rows and 20MB.

Maybe in the future we could be able to set something up to be able to automatically delete log entries over a certain age.

Food for thought.


Steve

UBB.classic from 2000-2003
UBB.threads from 2003-present!
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
just do it with a custom island that runs every so often.

i do that all the time smile

SD #252084 01/16/2013 12:43 PM
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Interesting idea SD.
Sounds like I could use this as a home made cron job for several items.
I never thought of that.

I assume you would also need to enable the Always Build option.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
not really...

i just use an island that is displayed... like maybe the random image one....

then i add the special code in there..

so not only does it do the random image, but it does the 'cron' stuff too

smile

'always build' is too often anyway... with a random image every 15mins, i accomplish 2 goals smile

Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Oh, I see in your case you are hijacking a island to serve multiple uses.

Cool!


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
This is such a good idea.

Would someone mind adding a "small sample" here? One that is written to be used in an Island.

For example: Delete referer logs > 60 days old.

And, again, on the recommended times. Every 15 minutes?? Wow... 100 times a day? Wouldn't it be more prudent to have it run less frequently?

Thanks!


--BIll B
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
How about just how to delete all postings in Forum xyz that are > 60 days old.


--BIll B
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
How about this??

SQL Query
/* PHP CODE HERE, IF NECESSARY */
/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<?PHP

DELETE * FROM "vccachat_org_-_chat".ubbt_TOPICS
WHERE forum_id = 25
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 5184000)

?>
EOF;
/* DO NOT CHANGE THE LINE ABOVE */
Now, If I only used the Island to do this one function, what would be a good way to trigger it once a day or so?

--Bill

Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
It should follow the cache time setting in the island you add it to.
So 60 minutes times 24 hours=1440 minutes
So possibly if you have say a static island then hijack that island and change the time from 0 to 1440.
See the USA island could be a candidate.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
Ah... very good Ruben. Yes, "See the USA" would be a great place to do that.

Now I just need the have the guts to allow an "island" to delete active records from my database!?!?!? ha ha!!
onoes


---Oh, one other question. Is it okay to have multiple "jobs" running in a single Island? Or might there be a "timing" issue if the jobs take too long? I think that I'd like to have 3 or 4 of these "jobs" set to run at different intervals, and I don't know whether I've got that many islands that I can hijack.


--BIll B
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Go for the glory.
If faint of heart then make a backup first.
I have no idea on multiple processes. They don't take that much time. But this is new territory for myself as well.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
Well, I got wrapped around the "Island" axle.... dang it.

The MySQL query works good. But I'm messing up when I insert it into a Custom Island. I've never really gotten the hang of exactly HOW it works. PHP at top, HTML at bottom. But.... ?

Do I need to add <? PHP or $Query ="
(Oh, I did look at all of the samples out on the Wiki, but couldn't extrapolate a solution.)

Here's a start:

Code
/* PHP CODE HERE, IF NECESSARY */

<?PHP
DELETE *
FROM "abc_org_-_chat".ubbt_TOPICS
WHERE forum_id = 22
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 5184000)
?>

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/test.jpg" width="108" height="25" align=center alt="Time Flys!">HelloWorld

EOF;
/* DO NOT CHANGE THE LINE ABOVE */

Or, is it more like this?
Code
/* PHP CODE HERE, IF NECESSARY */

<?PHP
$query = ("DELETE *
FROM "abct_org_-_chat".ubbt_TOPICS
WHERE forum_id = 22
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 15552000)")
?>

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/test.jpg" width="108" height="25" align=center alt="Time Flys!">HelloWorld

EOF;
/* DO NOT CHANGE THE LINE ABOVE */


--BIll B
Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
I think it'd more like:

Code
/* PHP CODE HERE, IF NECESSARY */
query

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/test.jpg" width="108" height="25" align=center alt="Time Flys!">HelloWorld

EOF;
/* DO NOT CHANGE THE LINE ABOVE */

I'm not entirely sure though, I haven't mucked with using islands like this...


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
Nope... I get this error message:

Quote
Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING in /var/www/vhosts/vcca.org/httpdocs/forum/cache_builders/custom/portal_box_57.php on line 5
When I delete the query, the Island works okay.
And when I run the query using MySQL Query Browser, it works.

So it has something to do with how the Custom Island parses the coding. I wish that there was better documentation on this.

Does anybody else have a suggestion?


--BIll B
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Looks like you need to define a name for the query and place quotes in the query and you need place a run command.

Take a look at example 4 at:
http://www.ubbwiki.com/wiki/view/79/custom-islands.html
SD did that one for current events in the calendar and it works.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Another example as SD noted is Ricks quick hack on the random image island located on the bottom right at this site.

www.ubbcentral.com/forums/ubbthreads.php/ubb/showflat/Number/229905/


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
ahhh... I looked at that once before -- and then went off on a different tangent... If I get this resolved, then I'll submit it as another example for the Wiki. Thanks for the tips.


--BIll B
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Okay I just tried this for admin logs and it works fine but I just used the simple truncate command in the query.
It was my test board so it got to be a pain adding rows back to the table for testing.
PHP Code
/* PHP CODE HERE */
$sql = "TRUNCATE TABLE `ubbt_ADMIN_LOG`";
$dbh->do_query($sql);

/* BODY HERE */
$body = <<<EOF
SQL to clear logs
EOF; 
The part about sql to clear logs was just to indicate to me that that island did appear on the forum.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Syntax could probably be cleaner but for the logs I have left it now as:
PHP Code
/* PHP CODE HERE, IF NECESSARY */
$sql = "TRUNCATE TABLE `ubbt_ADMIN_LOG`";
$sql2 = "TRUNCATE TABLE `ubbt_REFERER_LOG`";
$dbh->do_query($sql);
$dbh->do_query($sql2);
/* BODY HERE */ 

Then I set the cache to like every 3 days.
Works perfect as is.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
okay, got it to work... whew... too many nickle-dime issues.

This should have been easy but I must have a hidden desire to succeed ONLY when it's difficult!!!! ha ha...

I'll post full code tonight along with info for a new WIKI Example!!!
grin laugh cool


--BIll B
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
HOW TO DELETE OLD THREADS AUTOMATICALLY AFTER 6 MONTHS

Add this to any CUSTOM ISLAND. Using an island with a static graphic works good.
Set the Island "Cache Time" to 1440. That way it will only run the query once a day.

Code
/* PHP CODE HERE, IF NECESSARY */

$sql = "DELETE FROM ubbt_TOPICS
WHERE forum_id = 44
AND TOPIC_LAST_REPLY_TIME < (unix_timestamp(now()) - 15552000)";

$dbh->do_query($sql);

/* DO NOT CHANGE THE LINE BELOW */
$body = <<<EOF

<img src="http://abc.org/forum/images/some-image.jpg" width="108" height="25" align=center alt="Good times!">Some Text Message

EOF;
/* DO NOT CHANGE THE LINE ABOVE */
You can change the "age" of the files that it deletes by modifying seconds (15552000) for the Unix calculation. (Example: Three months would be half of this number.)

---WARNING: DO NOT ADD THIS CODE UNTIL YOU READ THE FOLLOWING POSTING FIRST--- IT MAY NOT BE ADVISABLE TO DO IT TO LARGE, OR ACTIVE FORUMS---
Warning this code could break your site and may not be recoverable without data backups prior to running.
The user that created this is well aware of the risks involved

Last edited by Ruben; 02/12/2013 6:13 PM. Reason: Added comment by Ruben

--BIll B
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
HOW TO DELETE OLD THREADS AUTOMATICALLY AFTER 6 MONTHS and degrade your database...

just deleting from ubbt_TOPICS does NOT delete all the other supporting table entries that make up a topic!

you have ubbt_POSTS table to consider, which now has orphaned posts with no topic..

watch lists, where people might have been watching that topic.

possible polls.

possible files ( attachments ) that have been added to individual posts and more!

calendar events, announcements, stickies....

so do NOT consider the above CI to be what i'd recommend. matter of fact, i highly discourage it.

just open up scripts/doexpirethreads.inc.php to see all that needs to be taken into account smile

this is probably a ubbdev kinda topic anyway.. 2c

SD #252224 02/09/2013 12:10 AM
Joined: Oct 2006
Posts: 358
enthusiast
enthusiast
Joined: Oct 2006
Posts: 358
Actually I meant to inquire about the related posts that might be orphaned when this is done. But I got in a rush this afternoon while I was drafting a comment - since we had a whole house-load of grand-kids come over. So I just pressed "SEND".

I agree that that is not a good situation. And I've thought previously about trying to hold this discussion in UBBDEV, but 1) nobody is there any more, and 2) well... nobody is there any more. :-)

It's really a shame that this "basic function" isn't provided with the core code. No way that it should be a "hack". And when I say that, I mean as an AUTOMATED function.


--BIll B
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
there are any number of 'automated' niceties that could be added, however my post was to dissuade anyone from using that particular piece of code, since it harms ALL forums who use it ( large, small, medium, active, inactive )

now if Brooks wants to add a 'mods and hacks' forum here and we actively use such a forum, i'm more than willing to participate, since ubbdev is essentially dormant.

Joined: Jun 2006
Posts: 16,292
Likes: 116
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,292
Likes: 116
Well, UBBDev is dormant as a lot of our userbase went elsewhere due to threads basically being shelved for quite a while and users went elsewhere :/...

I'm more than willing to take on suggestions though; any mod requests (or feature requests) I've seen here that look worthwhile I've slapped together and published there (and will only publish there).


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
I agree SD.
This topic started about clearing the admin/referer logs. Which is a simple single table to clean.
Then topic drift went onto pruning posts. Which is dangerous since the data is spread out among several tables.
Since we already have a tool for pruning.
Control Panel » Prune Topics
It would be best to mimic or hack that process


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
yup, i WILL say that Bill B has the right attitude though smile

just don't want to let that one Content Island example get used anywhere wink


Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
0 members (), 396 guests, and 110 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)