Manually purging old entries from the nntp//rss database

[jason]

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.

[snowzone]

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

[jason]

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.

[Zed]

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)

[jason]

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.

[Zed]

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.

[jason]

Ok - this is useful information. What newsreader are you using?

[Zed]

Forte agent 1.93

[Zed]

Do you have an utility which I could use to connect directly to the database, and feed it with SQL queries ?

[jason]

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.

[Zed]

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 ?

[jason]

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.

[Zed]

Any idea of when the new release will come ?

[Zed]

Helllloooo ?
...
oooo...
...
oo...
...

[jason]

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.