Manually purging old entries from the nntp//rss database

Have any useful hints and tips for nntp//rss users? Share them here.

Manually purging old entries from the nntp//rss database

Postby jason on Sun Sep 07, 2003 10:49 pm

Here are the steps to manually remove old entries from the nntp//rss database. More flexible expiration options will be in the v0.4 release of nntp//rss, however in the interim these instructions will allow you control the size of an existing database.

1. Stop nntp//rss and BACKUP YOUR EXISTING DATABASE - These are the nntprssdb.* files within your nntprss directory.

2. Open a new command prompt / shell, change to the nntprss directory.

3. Run the following command to start the HSQLDB database engine (this assumes that the java command is in the path):

Code: Select all
java -cp .\ext\lib\hsqldb.jar org.hsqldb.Server -database nntprssdb


This will start the hsql process - you should see something like:

Code: Select all
server.properties not found, using command line or default properties
Opening database: nntprssdb
HSQLDB server 1.7.1 is running
Use SHUTDOWN to close normally. Use [Ctrl]+[C] to abort abruptly
Sun Sep 07 18:30:29 EDT 2003 Listening for connections ...


4. Open another command prompt / shell, change to the nntprss directory.

5. Run the following command to start the HSQLDB database manager (this assumes that the java command is in the path):

Code: Select all
java -cp .\ext\lib\hsqldb.jar org.hsqldb.util.DatabaseManager -url jdbc:hsqldb:hsql://localhost


Within a few moments a GUI window will appear. It should have a tree view in the left pane showing the three nntprss tables - CHANNELS, ITEMS, CONFIG. If these do not appear, stop the Database Manager, wait a few moments, then restart. It may be possible that the database is still being initialized.

6. To purge items older than a certain date, enter the following command into the text entry pane of the Database Manager: (this example removes all entries that were received before 1st July 2003)

Code: Select all
DELETE FROM items where dtStamp < '2003-07-01'


Note that this will delete in all channels items that were created before the specified date .

7. Click on the EXECUTE button to execute the request. Depending upon the size of your database, this process may take some time. At the end of deletion, you'll be presented with a result that indicates the number of items removed.

8. Enter the following command in the text entry pane:

Code: Select all
SHUTDOWN COMPACT


9. Click on the EXECUTE button. The database will now compact itself, recovering the space of the deleted items. Again, this process may take a few moments. When it has completed, you will note that the database process you started in step 3 has terminated. At this stage you can shutdown the Database Manager.

10. Restart nntp//rss.
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Postby snowzone on Wed Jun 30, 2004 5:44 pm

i guess there's no way to automate this?

it would be great to just click on a button on the nntp//rss admin page :)
snowzone
 
Posts: 2
Joined: Tue Jun 29, 2004 10:19 pm
Location: kingston, on. canada

Postby jason on Wed Mar 16, 2005 12:26 pm

Just to follow up on this issue - the recently release nntp//rss v0.5b1 now supports expiration times for historical articles. This automates the process of purging old entries from the database.
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Postby Zed on Thu May 05, 2005 2:18 pm

I'm using nntp//rss v0.5b1 since january, and I have yet to see it purge anything. So far all messages retrieved since then are still in the database. And expiration is set to 2 weeks.
Any idea why "purge" doesn't work on my system ? (win98)
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby jason on Fri May 06, 2005 9:41 pm

Zed - I will look into this issue. Please confirm how are you checking to see if items have been purged.

e.g. Using a different newsreader, Monitoring the list of messages in the groups in your current newsreader, monitoring the size of the Derby database files / MySQL tables.
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Postby Zed on Sat May 07, 2005 9:51 am

jason wrote:Zed - I will look into this issue. Please confirm how are you checking to see if items have been purged.

e.g. Using a different newsreader, Monitoring the list of messages in the groups in your current newsreader, monitoring the size of the Derby database files / MySQL tables.


First there is a clue: reading new messages automatically in the "slashdot" group is getting slower and slower.

Then, there is the confirmation with all the groups. I purge the messages in my newsreader, then ask it to fetch headers for all available messages. Bingo, I'm getting messages as old as 23/1/2005 in all the group existing at this date.
I don't remember anymore if I've kept (probably not) all the messages in the database when I've upgraded to v0.5, and if somehow they have been purged since then. But 23/1/2005 is not 2 weeks old.
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby jason on Sat May 07, 2005 11:40 am

Ok - this is useful information. What newsreader are you using?
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Postby Zed on Sat May 07, 2005 11:37 pm

Forte agent 1.93
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby Zed on Tue May 10, 2005 10:14 am

Do you have an utility which I could use to connect directly to the database, and feed it with SQL queries ?
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby jason on Fri May 13, 2005 1:47 pm

nntp//rss uses Apache Derby as its default embedded database. If you download the Derby distribution (available from http://incubator.apache.org/derby ), derbytools.jar in the distribution contains an interactive command line interface through which you can execute SQL queries.

You should also be able to use any JDBC-capable database manager. I believe there are a few available in open source.
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Postby Zed on Fri May 13, 2005 2:16 pm

OK, I'll check this.

Any idea why purge fails on my system ? When is it supposed to purge ? (when it updates the database with new records ?)
Could it be the fault of a corrupted DB ?
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby jason on Mon May 16, 2005 11:15 am

Ok - I think I may have tracked down the issue. Channel purging is set to execute once every 24 hours. It looks like there's a bug related to how the lastCleaned date of a channel gets persisted.

I will run some more tests, and, along with a few other fixes, will post a new release. Depending on how busy I am, this may take up to a week.
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Postby Zed on Mon Jun 20, 2005 12:40 pm

Any idea of when the new release will come ?
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby Zed on Wed Jul 20, 2005 1:34 pm

Helllloooo ?
...
oooo...
...
oo...
...
Zed
 
Posts: 9
Joined: Thu May 05, 2005 2:13 pm

Postby jason on Fri Jul 22, 2005 3:10 pm

Hi Zed,

Apologies for the delayed response. I haven't had much time to work on nntp//rss recently, due to other obligations. I will try and make a new build available of nntp//rss within the next week which will include the patches.

If you're interested in helping me out with some pre-release tests, please send a email to me (jason at methodize.org).

Thanks.
jason
Site Admin
 
Posts: 114
Joined: Sat May 03, 2003 10:44 pm
Location: West Orange, NJ

Next

Return to Hints and Tips

Who is online

Users browsing this forum: No registered users and 1 guest

cron