Hmmm.. this sounds like a tricky one :(

If you are getting the headers correctly, then we know it's talking to the INN server correctly and understands how to retrieve header information by message number.

The next thing the script does is to run those headers through the spam filter and if it decides it's an article we want, it requests the body from the news server using the message number and the 'body()' method from perl NNTP module. Assuming you have no replacements set, this must be the bit that is failing to retrieve the article from the server.

Why it's failing, I have no idea. Have you got another news server you can try. That way you can determine if it's an INN specific thing. You might want to make sure you have the latest NNTP module from CPAN aswell.

Yeah, I installed perl 5.6.1 and all the modules fresh, as a precaution. Unfortunately that machine can't get out to the net easily, so it doesn't really have access to another news server I can test against.

Thats not a major problem though. I was simply wondering if anyone had bumped into this one before.. I'm going to hack some debugging into your code and see what it *thinks* it's inserting into the DB =). A look at the database would probably be helpful, too.

BTW, have you thought about using a newsfeed instead of a poll method to populate the database? I wrote a perl script (still have it if you want it) that plugged into an INN server and pushed postings into a mysql database. It's spawned by INN when it starts, and INN pushes it an id of every message that INN gets sent (whether by an external feed, or a client) and then the script uses an INN supplied command to turn that id into a path to the physical location of the posting. The script reads it into an NNTP::Article structure and gets all the headers/body and dumps it into relevant fields in a MySQL database.

It's a bit broken atm, no checking for SQL statements in the body/headers etc :eek: but it works.

I was originally going to turn it into a vB clone, but then I found your hack. :D Much nicer to get vB to do all the hard work thats already been done, eh?

The reason I did it this way was that most people don't have either a full newsfeed or their own news server. Most of us are using a personal news account such as Supernews. There are issues of missing posts and problems with different character sets that could probably be handled more easily if the news server was dumping things raw into a database for us to process later so I would be very interested in your script if you'd like to share it :)

http://www.stupendous.net/news2mysql-0.1.tar.gz

I hope you find it useful.

I notice that you don't use News::Article for parsing of the articles? You'd probably find you would have better luck using that to handle all the character set issues and to avoid using home-grown regex's. I'm sure it uses regex's internally (not bothered to look) but why reinvent the wheel, eh?

How do Supernews feel about you polling their servers every 30 minutes? :)

Hrm...

The postings are definately in the database, and your newnews.pl is definately pulling out the body fine, its just that vB is not displaying it at all. Very strange :confused:

Feh.

Do you know if there is any debugging I can turn on in vB that will show extended information about the postings when viewing threads? its almost as if the posting isn't being associated with the thread properly.

In fact I described my problem incorrectly. The threads are being created but individual posts are not showing up in the threads - it's not the body of the message at all - Doh...

For some reason newnews.pl isn't assigning a threadid to any of the posts that it inserts into the DB. More investigation needed I think =)

Your DBI or DBD module is out of date. Read back a few pages and you will see other references to this. Older versions of these modules do handle the mysql_insertid() function correctly.

Here's is a news server that I found.

ccnews.thu.edu.tw

Joey

DBI-1.18
Msql-Mysql-modules-1.2216
perl 5.6.1

those are the latest afaics

What versions are you using to develop with?

I'm using those versions.

The problem you are seeing is due to something not handling the mysql_insertid() function.

Others had the same problem and solved it by upgrading their modules. Perhaps it's a MySQL issue. I'm not sure as I have never encountered the problem.

Oh well. :(

I did write a small test perl script that basically inserted a row into a table and tried to use the mysql_insertid function to get the id, and it doesn't work, so I'm confident it's entirely my problem, and not your code at all ;).

I reinstalled my Msql-Mysql-modules and when I did a make test, I noticed some failures. Hrm. Something about my Solaris 8 sparc platform I think. I'm going to try upgrading the compiler to gcc 2.95.3 and seeing if that helps (rebuilding all the DBI related modules).

If not, I'll start bugging the DBI/MySQL developers :D

Thanks for the help anyway. Good job on the hack, in any case :)

chrome.

Ok I have about 15 newsgroups online. I reindexed and the turned ON usenet index. Got the same problem I had the other day with duplicate entries. I have turned back off,

All newsgroups are working fine except a small probel with 2 of the them. I run newnews.pl and watch it download messages for these 2 groups, example tonight each one downloaded 24 messages each. I go to my forums and they still show zero in the count. I went and looked at the orphan files for each but they also are empty. The 2 groups or comp.mail.smail and comp.mail.eudora.
Does anyone where these messages might be going?

Joey

Sorry guys I think I found it. It might have worked if someone had posted to either one of those groups in the last month. They must be dead or something

Joey

Did anyone ever come with a answer to somehow minimize the amount of orphans not be posted. I have been getting a alot of them. Some of them I see 4 or 5 the same ones all re: some of them are replies to a known thread that is already showing up on my board. Any ideas or fix's

Joey

Wow - this is exactly what I've been looking for! Thank you, thank you, thank you!

I am very excited about the possiblity of integrating 2 usenet newgroups with my vBulletin forums, but I'm worried about doing anything which might endanger my hosting account.

1.) Can this script be run on a virtual/shared host? (300 other hosting accounts per RH Linux server, P3 800, 1 GB Ram)
Is anyone running this on a shared host, or is it for dedicated servers only?

2.) What kind of server resources would be required if I was looking at including 2 usenet groups which currently have 100 messages per day max?

3.) What happens if connection to the news server hangs? (lingering processes?)

Also, I am a little worried about the possibility of being targeted by a spammer who could use my forums to spam usenet and thus get my hosting account suspended.

4.) Is there any type of post rate limiter designed in?

5.) Is there an option to allow only moderated posts to usenet (or maybe an option to have them appear in the forum before being approved for posting to usenet?)

Thanks very much - this is a fantastic option. I just hope that I will be able to use it.

I just installed the script - wow!
I am using Cyrilic encoding but on my forum i have WIN-1251-codepage and usenet-topics in russian are in KOI-8. How I can convert it to 1251?

Can i get this in .zip format? Im having problems with the tar.gz format...

<a href="http://www.winzip.com/" target="_blank">http://www.winzip.com/</a> unpacks it without any trouble...

I saw Ed post a reply about a similar problem just a three or four days ago. It involved editing the vB code somewhere. Your edits will need to be a little more complicated though as you only want to make changes to forums that contain usenet threads.

The character set options in the usenet control panel are just for the quoted printable character conversions.

Sorry I can't offer any more help.

I have found how to convert KOI8 to CP1251

Modules to install - Convert::Cyrillic; Unicode::Map8

Code to add:
use Convert::Cyrillic;

Code to change:
$wd->decode(YOURTEXT) replace to
Convert::Cyrillic::cstocs ('koi8','win', YOURTEXT)

for example
$$art{body} = $wd->decode($$art{body})
$$art{body} = Convert::Cyrillic::cstocs ('koi8','win', $$art{body});


:D

Just downloaded and activated basic setup. Exactly what I need.

HOWEVER... I get a

parse error on usenet.php on line 378

also

Warning: GLOBAL variable declaration meaningless in main() scope in /xx/xx/xx/admin/usenet.php on line 353


trying to do the administration ??

are these known errors. Or probably, have I missed something.


Could it have something to do with needing to insert xtra \\s in some SQL inserts?


Does the newnews.pl need to run first? before I do admin.


:confused:

I am installing some of the options with this hack. I have installed 3 or 4 of them. 2 questions; First
I installed the stat's hack in stats.php, but when I go to the admin control panel and view stat's it does not show usenet stat's I then looked in drop down menu and there was not a selection to choose them. I also installed the hack that separtes the board post from the usenet post. Would this have something to do with them not showing up on the stat's page or to do I have to place a command in one of templates for this?
Second Question.
I want to add the last active thread option, Before I do I would like someone to look at code below and make sure I'm putting it in the right place. Area's I am going to place are in bold and state before this to the side.

8) Populating of last active thread in the forum table. This can then be referenced in templates by using $forum[lastactivethread].
a) admin/functions.php - line 1022

if ($newwords) {
$newwords=trim($newwords);
$insertwords="(NULL,'".str_replace(" ","'),(NULL,'",addslashes($newwords))."')";
$DB_site->query("INSERT IGNORE INTO word (wordid,title) VALUES $insertwords");
$selectwords="title IN('".str_replace(" ","','",addslashes($newwords))."')";
$DB_site->query("INSERT IGNORE INTO searchindex (wordid,postid) SELECT DISTINCT wordid,$postid FROM word WHERE $selectwords");
} ===before this====

if ($newtitlewords) {
$newtitlewords=trim($newtitlewords);
$insertwords="(NULL,'".str_replace(" ","'),(NULL,'",addslashes($newtitlewords))."') ";
$DB_site->query("INSERT IGNORE INTO word (wordid,title) VALUES $insertwords");
$selectwords="title IN('".str_replace(" ","','",addslashes($newtitlewords))."')";
$DB_site->query("REPLACE INTO searchindex (wordid,postid,intitle) SELECT DISTINCT wordid,$postid,1 FROM word WHERE $selectwords");
}
}===before this===

// ###################### Start makeforumjump #######################

b) admin/functions.php - line 1577

$DB_site->query("DELETE FROM thread WHERE threadid='$threadid'");
$DB_site->query("DELETE FROM thread WHERE open=10 AND pollid='$threadid'"); // delete redirects
$DB_site->query("DELETE FROM threadrate WHERE threadid='$threadid'");
$DB_site->query("DELETE FROM subscribethread WHERE threadid='$threadid'");
}
}===before this====
// ###################### Start delete post #######################

c) admin/functions.php - line 1599

$DB_site->query("DELETE FROM searchindex WHERE postid=$postid");
$DB_site->query("DELETE FROM post WHERE postid='$postid'");
}
}===before this===

// ###################### Start make login code #######################

d) admin/functions.php - New function at end of file.

//searches expire after a week:
$DB_site->query("DELETE FROM search WHERE dateline<".(time()-(7*24*60*60)));
}

// bye bye!
}===before this====
?>

This is where I put the hack in stats.php

$from = mktime(12,0,0,$frommonth,$fromday,$fromyear);

//START USENET HACK
if (($type == "post" || $type == "thread") && $showusenetpostsinstats==0) {
$stats = $DB_site->query("SELECT COUNT(*), DATE_FORMAT(FROM_UNIXTIME($field),'$sqlformat') AS timeframe, MAX($field) FROM $table WHERE $field > '$from' AND $field < '$to' AND isusenetpost=0 GROUP BY timeframe ORDER BY $field $sort");
} else { $stats = $DB_site->query("SELECT COUNT(*), DATE_FORMAT(FROM_UNIXTIME($field),'$sqlformat') AS timeframe, MAX($field) FROM $table WHERE $field > '$from' AND $field < '$to' GROUP BY timeframe ORDER BY $field $sort"); }
//END USENET HACK
unset($totals);
unset($dates);
unset($sum);
while ($stat = $DB_site->fetch_array($stats)) {
$totals[] = $stat[0];
if ($stat[0]>$max) {
$max=$stat[0];

Houston, we have a problem...

Just moved my forums to new server, installed perl 5.6, MySQL 3.23.38, all modules required, installed usenet hack and...

- VBB shows thread titles but no posts inside them.
- All usenet posts retrieved from the server in database have threadid=0

Here's what I did so far:

Manualy compiled all the modules once again with versions as posted in this thread
Once again installed Bundle::CPAN and all the required modules matching CPAN version.

Still the same - all posts are inserted to database with threadid=0 and don't appear under threads in VBB...

Correct.
Correct.
Correct.
WRONG!
It is a NEW function. Add it after the brace but before the php close
The stats option doesn't actually add a new option. I think there may be a typo. I believe it actually shows the usenet posts UNLESS you apply this option. The next version will provide more options for stats.

Looks like you're having the same problem as chrome. As you know, a couple of people have seen this problem but have fixed it by upgrading their modules. See if you can get in touch with chrome and see if he's found the problem.

fastforward:
ok I got all the code in place for the lastactivethread and I placed on the following location in the forumdisplaybit template but it still not showup on the opening page in my forums. Can you direct me on where I am going wrong below is the script. I have tried $forum, $forum2, and $thread in front of [lastactivethread] but none work.

<tr align="center">
<td bgcolor="#DFDFDF"><img src="images/$thread[newoldhot].gif" border="0" alt=""></td>
<td bgcolor="#DFDFDF">$thread[icon]</td>
<td bgcolor="#F1F1F1" align="left" width="100%"><normalfont>$thread[gotonew] $paperclip$thread[movedprefix]$thread[typeprefix]<a href="showthread.php?s=$session[sessionhash]&threadid=$thread[threadid]">$thread[title]</a></normalfont> <smallfont>$thread[pagenav]</smallfont></td>
<td align="right"bgcolor="#DFDFDF" nowrap><smallfont>

$forum[lastactivethread]

$thread[firstpostdate] $thread[firstposttime]<br>by <b>$thread[postedby]</b></smallfont></td>
<td bgcolor="#F1F1F1"><normalfont>
<a href="javascript:who($thread[threadid])">$thread[replycount]</a>
</normalfont></td>
<td bgcolor="#DFDFDF"><normalfont>$thread[views]</normalfont></td>
$threadrating
<td bgcolor="$backcolor">
<table cellpadding="0" cellspacing="0" border="0"><tr>
<td rowspan="2" nowrap><a href="showthread.php?s=$session[sessionhash]&goto=lastpost&threadid=$thread[threadid]"><img src="images/lastpost.gif" border="0" alt="Go to last post"></a>&nbsp;</td>
<td nowrap><smallfont>$thread[lastreplydate] <smallfont color="#666686">$thread[lastreplytime]</smallfont></smallfont></td>
</tr><tr>
<td nowrap><smallfont>by <a href="member.php?action=getinfo&find=lastposter&th readid=$thread[threadid]"><b>$thread[lastposter]</b></a></smallfont></td>
</tr></table>
</td>
</tr>

An excellent hack, but as per my previous post. I don't understand where it goes wrong. The admin function does it's job ok... but I get those errors mentioned... any pointers please.
I'm willing of course to do legwork.

I loaded the version from the top, 2 days ago. Perl things in place...etc. Please I need the base to work before I apply the other hacks.

I'm a mainframe guy :o

T.I.A.

George

see the urgency of this need requirement at this site

The USENET Forums are currently hidden.

I have never seen this error before and the usenet.php file has not been changed for months. What does your line 378 say?

cpfooter();

if ($action=="manual") {
if ($mode=="Delete") {
foreach ($lostmsgid as $i){
$DB_site->query("DELETE FROM usenet_article WHERE msgid='" . addslashes($i) . "'");
}
echo "Message(s) Deleted<br><br>";
}
if ($mode=="Insert into thread") {

//$num_rows = $DB_site->num_rows($threadmsgids);
if ($threadmsgids=$DB_site->query("SELECT msgid,ord FROM post WHERE threadid=$threadid ORDER BY dateline LIMIT 1")){
$thread=$DB_site->fetch_array($threadmsgids);
$threadmsgid = $thread["msgid"];
$ord = $thread["ord"]; // most likely 0
foreach ($lostmsgid as $i){
$DB_site->query("UPDATE usenet_article SET refs='" . addslashes($threadmsgid) . "', ord=$ord, manual=1 WHERE msgid='" . addslashes($i) . "'");
$DB_site->query("INSERT IGNORE INTO usenet_ref (msgid,ref) VALUES ('" . addslashes($i) . "','" . addslashes($threadmsgid) . "')");
}
echo "Messages inserted.<br><br>";
}
else { echo "Thread was not found or not specified. Please go back and select a valid thread.";}
}
if ($mode=="New Threads") {
foreach ($lostmsgid as $i){
$subjects=$DB_site->query("SELECT subject FROM usenet_article WHERE msgid='" . addslashes($i) . "' LIMIT 1");
$subject=$DB_site->fetch_array($subjects);
$subject=$subject["subject"];
$subject = eregi_replace("^Re:[ ]*","",$subject);
$DB_site->query("UPDATE usenet_article SET refs='', ord=0, manual=1, subject='" . addslashes($subject) ."' WHERE msgid='" . addslashes($i) . "'");
}
echo "New threads created<br><br>";
}

Check in the forum table and see if the lastactivethread column is populated.

You might be better of not implementing this option. Instead, you can use this hack. It supports showing the icon aswell. newnews.pl works with it.

Don't know what to suggest I'm afraid. Nobody else has reported this problem and your file matches mine. I can only assume it is something specific to your PHP version or configuration.

foreach clause is not supported in PHP3 :(
now to figure out that standard loopy way
hmmmm.... its only the bottom part...
gotta figure out what it does!!

That entire function is for manually inserting orphan replies into threads. It doesn't work at the moment any way. I broke it when I made other changes :) You should be able to safely comment out the lot.

Comment out the foreach all of them

## foreach ($lostmsgid as $i){

Insert after each of them

for($ix = 0; $ix < sizeof($lostmsgid); $ix++) {
$i = $lostmsgid[$ix];

that should do it .... I'm clean now with the parse error.

lastactivethread column is populated but still nothing. I did however use your recommendation and install other hack with the same results. Here is the forumdisplaybit template, I have at the bottom placed how I setup the 2 tables in your hack. I have also attached my functions.php in a zip format. My site is http://www.realwebhost.net

<tr align="center">
<td bgcolor="#DFDFDF"><img src="images/$thread[newoldhot].gif" border="0" alt=""></td>
<td bgcolor="#DFDFDF">$thread[icon]</td>
<td bgcolor="#F1F1F1" align="left" width="100%"><normalfont>$thread[gotonew] $paperclip$thread[movedprefix]$thread[typeprefix]<a href="showthread.php?s=$session[sessionhash]&threadid=$thread[threadid]">$thread[title]</a></normalfont> <smallfont>$thread[pagenav]</smallfont></td>
<td align="right"bgcolor="#DFDFDF" nowrap><smallfont> $forum[lastactiveiconid] $forum[lastactivethread] $thread[firstpostdate] $thread[firstposttime]<br>by <b>$thread[postedby]</b></smallfont></td>
<td bgcolor="#F1F1F1"><normalfont>
<a href="javascript:who($thread[threadid])">$thread[replycount]</a>
</normalfont></td>

Field Type Length/Set Attributes Null Default
lastactivethread varchar(64) Yes
lastactiveiconid smallint(5) UNSIGNED No 1

I see one problem in your template, and that is the iconid. iconid only returns the number. You still need to add the image tag and 'icon' part of the name. Here's my template:

------------------------------------------------
<table cellpadding="0" cellspacing="0" border="0" width="100%" id="ltlink">
<tr>
<td rowspan="2" valign="top"><a href="$bburl/showthread.php?s=$session[sessionhash]&goto=lastpost&forumid=$forum[forumid]" title="Go to last post"><img src="images/icons/icon$forum[lastactiveiconid].gif" border="0" alt="">&nbsp;</a></td>
<td align="left"><smallfont><a href="$bburl/showthread.php?s=$session[sessionhash]&goto=lastpost&forumid=$forum[forumid]" title="Go to last post"><font color="#000000">$forum[lastactivethread]</font></a> (<a href="$bburl/member.php?s=$session[sessionhash]&action=getinfo&find=lastposter&forumid=$forum[forumid] title="See $forum[lastposter]s profile">$forum[lastposter]</a>)</smallfont></td>
</tr>
<tr>
<td nowrap><smallfont>$forum[lastpostdate] <font color="#666686">$forum[lastposttime]</font></td>
</tr>
</table>
------------------------------------------------

Try just referencing it just by $lastactiveiconid and $lastactivethread, without the 'forum' bit. If that doesn't work, try $foruminfo[lasticonid] and $foruminfo[lastactivethread]

If that doesn't work, then you're on your own. :) I assume you are using the latest vB and are on php4?

yes vb2.0.1 php4
Thanks I'll try it

Joey

I tried! I think I give up for awhile and try to enjoy the 4th.

Happy July 4th everyone.

Joey

Thanks FastForward

commented out line with following text

// global $DB_site;


caused a warning in admin
usenet.php ... PHP3 change

admin routine now clean for php3 :)

Are you sure it all still works?

The only thing that can safely be disabled is the [Review/Insert Orphans] option within the 'Groups' section. This is handled by the first block of functions you spoke about. $DB_site is the main database class that handles all database access.