Usenet gateway

THIS THREAD IS CLOSED!
Posts in here will NOT be answered

This hack has undergone many major changes since this thread was started. Consequently, most of the posts have become dated and of little use. To coincide with the latest major release (20010712), a new one has been started.

See this thread for the latest version and discussion

Now I am puzzled. I did a clean install of the forums (imported the original DB that had no posts, etc. into a new DB) and imported the rec.sport.unicycling newsgroup and now I only have 15 messages in the usenet_article table and when I look at it, there are reasons that I see that they were not inserted. Stuff like "Re(2):" or "Re: [Re:" are in the subject and some of those have no refs (so the orphan replies code doesn't associate a ref for it). Earlier today, I had 52 messages in the usenet_article table and most looked like they shouldn't have had the problems.

You might want to keep an eye on this. If there really is a problem, what you are seeing indicates there is something wrong with usenet replies to forum originated messages. Although I've never exprienced any problems, there could be some kind of timing issue that doesn't apply the references correctly.

What happens when a forum message is destined for usenet is this:[list=1][*]A dummy msgid is created in the msgid column to avoid primary key violations[*]The message is sent to usenet at the next run. (this is the last thing it does after pulling articles. at this stage there is still only the dummy key available which is meaningless)[*] The next run retrieves the msgid of the posted message and updates the msgid column with the correct message id. (This is is the fisrt thing it does, so theoretically, it should have no problem with the messages it's about to pull)[/list=1]
So the bottom line is... it shouldn't be a problem, but there may be some scenario I've overlooked that could cause the msgid not to be updated.

The obvious cause is if you change your 'useragent' in the control panel. This is how the script identifies your messages. Once you have set it, you should not change it if you have outstanding messages from usenet.

Yesterday when I tried the newsgroup again from scratch, I did not have any better success than before, as I had thought. What happened was that I had the days to keep orphaned replies set to 7 days, so it deleted the ones that were older than 7 days. So today, I tried it with a setting that would be longer than any of the dates of the messages that are in the newsgroup and there were 69 that could not be inserted into the forums (and 468 that were put in the forums).

Anyways, looking at the ones that did not get inserted, I had these cases:

1. A thread starting post had two direct replies, both had the reds set to the msgid of the first post in the thread. However, the one that was not inserted had an order of 2 while the parent post had an ord or 0 and the other reply had an ord of 1. So that didn't fit the ref matching along with the ord being equal to or one more that the parent post. So it didn't get inserted. So, I changed the MySQL call in the newnews.pl file to ignore the ord. So on line 404, I removed "((d.ord = a.ord) OR (d.ord +1 = a.ord))"so that I now have:

   my $q3 = db_fetch("SELECT b.title, a.nntpposter, a.forum, a.msgid, a.dtm, a.subject, a.poster, a.body, a.ord, a.postid, a.email, b.threadid, c.ref FROM usenet_article AS a, thread AS b, usenet_ref AS c, post AS d where b.threadid = d.threadid and b.forumid = $$newsgroup->{forumid} and c.ref = d.msgid and a.msgid = c.msgid  ORDER BY a.dtm, a.ord");

   That made it where this post was then inserted into the proper thread along with 4 others that had the same problem and brought the total in usenet_article to 64.
   
   The rest of the cases are now without the ord part of the MySQL query.
   
2. A message has two refs in it and both those refs are associated with posts in the post table, however, these referenced posts are in different threads. Also, the original message on the newsgroup does not have any references associated with it. The refs associated with this message both had the same title "(no subject)". So the orphaned replies code must have associated this post with both of these messages.
   
3. A reply to a message that had a different title than what it's reference was and then the post that was not inserted was a reply to that message that had no refs in the headers. The original post was from a newbie to newsgroups and replied to a message to start a new thread.
   
4. One post was a reply to a post, but the reference for that post did not match the parent post (the message did have quotes from the parent post though).
   
5. Some posts have weird subject lines associated with it, such as one person's reply to a message titled "My Post" ends up being "Re: [My Post]", and a reply to another replied message ends up being "Re: [Re: My Post]". These posts do not have an references in them on the newsgroup, . Also, there were instances of subjects like "Re(2): My Post" and "Re: Re(2):".
   
6. Some posts to the mailing list do not make it to the newsgroup, so the replies to that get trapped in the usenet_article table.
   
7. Some posts looked like they matched all the conditions to be inserted, with the refs being associated with the post(s) that id goes to. I went through the MySQL query and everything matched. So I added some console() calls to see if those messages did get selected and they did. So, I added some more console() statements to determine what's happening, so starting at line 440, I put in:

    		  console("\nTrying post from $poster:");
             if (db_execute("INSERT INTO post (allowsmilie,threadid,username,dateline,pagetext,visible,ord,msgid,userid,ipaddress,isusenetpost,seq) VALUES ($config{allowsmilies},$threadid,$poster,$dtm,$fbody,'1',$ord,$msgid,$userid,$nntpposter,1,$seq+1)",1)) {
                console(" posted!");
                $postid = $dbh->{'mysql_insertid'};
                db_execute("DELETE FROM usenet_article WHERE msgid = $msgid");
                db_execute("DELETE FROM usenet_ref WHERE msgid = $msgid");
                my $q4 = db_fetch("SELECT lastpost FROM thread WHERE threadid=$threadid");
                my ($lastpost) = $q4->fetchrow_array;
                if (!$lastpost) { $lastpost = $dtm; }
                db_execute("UPDATE thread SET replycount = $seq ".(($dtm >= $lastpost)?",lastpost=$dtm,lastposter=$poster":"")." WHERE threadid=$threadid");
                my $q5 = db_fetch("SELECT lastpost FROM forum WHERE forumid=$forumid");
                $lastpost = $q5->fetchrow_array;
                if (!$lastpost) { $lastpost = $dtm; }
                db_execute("UPDATE forum SET replycount=replycount + 1 ".(($dtm >= $lastpost)?",lastpost=$dtm,lastposter=$poster":"")." WHERE forumid=$forumid");
                indexpost($postid);
                push(@updated_threads,$threadid);
            }
   		 else { console($DBI::errstr); }

   This resulted in these results:
   Getting article batch from rec.sport.unicycling
   No new messages in rec.sport.unicycling
   inserting new threads into forums
   inserting replies into forums
   
   Trying post from 'Jonathan Marsha'uplicate entry '<CSujlIAW5Mw6Eww0@jbmarshl.demon.co.uk>' for key 5
   Trying post from 'Mark Wiggins'uplicate entry '<3AC0D05A.620D0A71@ftel.co.uk>' for key 5
   Trying post from 'Mark Wiggins'uplicate entry '<3AC0D05A.620D0A71@ftel.co.uk>' for key 5
   Trying post from 'Mark Wiggins'uplicate entry '<3AC0D05A.620D0A71@ftel.co.uk>' for key 5
   Trying post from 'Chuck Webb'uplicate entry '<As6w6.166$9d.54655@newshog.newsread.com>' for key 5
   Trying post from 'Greg House'uplicate entry '<5Xew6.151$pn3.483290@nntp3.onemain.com>' for key 5
   Trying post from 'Greg House'uplicate entry '<5Xew6.151$pn3.483290@nntp3.onemain.com>' for key 5Processing outgoing messages
   Clean disconnection from news.tc.umn.edu
   
   Um, ok, now I figured that one out since those posts are already in the forum somehow ended up in the usenet_article table many times.

So in conclusion, the rec.sport.unicycling newsgroup is pretty messed up as far as the posts being properly threaded. The newsgroup is also an email mailing list so that causes some pretty strange things for referenences (such as no references) and the formatting of the subject line.

Originally posted by fastforward
What happens when a forum message is destined for usenet is this:

That isn't the problem that I have with the one newsgroup, rec.sport.unicycling, as I haven't ever posted a message that went to the newsgroup from the forums. It may have been a factor in the other newsgroups that I played around with but didn't focus too much on trying to get them to work.

1. A thread starting post had two direct replies, both had the reds set to the msgid of the first post in the thread. However, the one that was not inserted had an order of 2 while the parent post had an ord or 0 and the other reply had an ord of 1. So that didn't fit the ref matching along with the ord being equal to or one more that the parent post. So it didn't get inserted. So, I changed the MySQL call in the newnews.pl file to ignore the ord. So on line 404, I removed "((d.ord = a.ord) OR (d.ord +1 = a.ord))"so that I now have:

I don't understand how the message with an order of 2 was a direct reply to the parent. If this were the case, it would have an order of 1. This 'ord' field is simply a count of references in the header. The other unlikely scenario is that the news server added two references (but as you pointed out, there was only one ref in each message). I will check the code, but as I'm sure you've seen, not a lot can go wrong with counting how many refs are in the header. Somewhere between the count and the insertion into the usenet_article table it must have lost one of the refs.

If you leave that clause out permanently, you will get messages out of synch. This will happen more on busy groups that are propogated to many servers.

I thought long and hard about the best way to handle it, and this method with the ord seemed to be the method that fit most situations. In addition to the ord column, there is the 'seq' column this is simply incremented and the thread id displayed on this order. The 'ord' method makes sure the 'seq' 'makes sense'.

As for the other issues, as you probably realize, there's not much we can do about that I don't think. (Except your suggestion of flagging un-inserted messages for manual insertion). If everybody used a good solid news client we'd be fine

I just noticed that on the usenet forum, the "Next thread" link doesn't show up properly. I'd keep pressing it for next thread, and I'd think I'm at the last thread since it only shows "Last thread" But if I check the thread list, there are a few more to read.

This happens on and off, they sometimes show up till the last thread?!?

This must simply be a post count problem. My script does nothing to the actual forum display or the way vB handles it. Try updating the threadcounts. I'll have a look at the code and see if there's a problem with incrementing and indexing of the posts in the script.

I've already noticed a small problem with the counting of usenet user posts if you have enabled the 'import user from usenet' option. That will be fixed in the next release.

Originally posted by tamarian
I just noticed that on the usenet forum, the "Next thread" link doesn't show up properly. I'd keep pressing it for next thread, and I'd think I'm at the last thread since it only shows "Last thread" But if I check the thread list, there are a few more to read.

This happens on and off, they sometimes show up till the last thread?!?

I believe I've found the problem. It was updating the replycount in the forum table, but not the threadcount. In addition, it was updating the replycount twice; once for the thread starter and once again for the associated post. I'll post a new release later today along with a few other minor bug fixes.

Originally posted by fastforward
I don't understand how the message with an order of 2 was a direct reply to the parent. If this were the case, it would have an order of 1. This 'ord' field is simply a count of references in the header. The other unlikely scenario is that the news server added two references (but as you pointed out, there was only one ref in each message). I will check the code, but as I'm sure you've seen, not a lot can go wrong with counting how many refs are in the header. Somewhere between the count and the insertion into the usenet_article table it must have lost one of the refs.

I looked more closely at the actual messages on the newsgroup server and message 3 was a reply to message 2, however, it had no refs in the header. So an order of 2 is correct, but the orphaned replies code only found one reference to associate with it.

Talking about threads, my usenet threads count is always 0 on main page since i have installed 2.3. Post count is correct but thread count doesn't progress.

Also on another note.. user group permissions to post.
- With usenet forum set to 'Open for new posts?' (No) noone can post including user groups allowed to do so.
- With usenet forum set to 'Open for new posts?' (Yes)
everyone can post including canpostusenet = 0 groups BUT only canpostusenet = 1 group propagates to usenet. Confusing bit is that some replies get propagated to usenet, and then canpostusenet = 0 replies appear only on the board. In effect to NNTP users my guys appear to be replying to phantom posts in the thread.

I went through the whole lot few times, can't spot the bug...

Originally posted by v0n
Talking about threads, my usenet threads count is always 0 on main page since i have installed 2.3. Post count is correct but thread count doesn't progress.

Check you have entered the variable name correctly in the templates. It should be '$totalusenetthreads'

Also on another note.. user group permissions to post.
- With usenet forum set to 'Open for new posts?' (No) noone can post including user groups allowed to do so.
- With usenet forum set to 'Open for new posts?' (Yes)
everyone can post including canpostusenet = 0 groups BUT only canpostusenet = 1 group propagates to usenet. Confusing bit is that some replies get propagated to usenet, and then canpostusenet = 0 replies appear only on the board. In effect to NNTP users my guys appear to be replying to phantom posts in the thread.

That's exaclty how it's supposed to work. This way there is no interference with the vB forum permissions. All the canpostusenet flag does is give you one more thing to play with when you're setting up forum permissions. It will not stop anyone posting to a forum (the vB permissions are already available for you to do that). As you have correctly determined, all it does is enable/disable propogation to 'usenet'. I will clarify that in the documentation of the next release.