#2835 Recreate missing discussions table rows

Other
closed-fixed
Rob Malda
MySQL (78)
5
2004-04-27
2004-04-01
Jamie McCarthy
No

Foundries has 89, NewsForge has 403, Slashdot has 3, no other
OSDN site has any. What are they? Dirty stories with broken
discussions that once had comments.

Do this on Slashdot to see its 3 broken stories:

select * from stories left join discussions on
stories.discussion=discussions.id where writestatus='dirty' and
stories.discussion is not null and discussions.id is null and
stories.commentcount > 0;

Those rows in the stories table have a discussion ID that has no
corresponding row in the discussions table. I don't know how it got
that way. This is why we need to use foreign keys!

It looks like the discussions table is just missing rows, in at least
one case:

<http://www.newsforge.com/article.pl?sid=02/03/05/0232253>

mysql> select sid,time,discussion,commentcount,hitparade from
stories where sid='02/03/05/0232253';
+------------------+---------------------+------------+--------------
+--------------------+
| sid | time | discussion | commentcount |
hitparade |
+------------------+---------------------+------------+--------------
+--------------------+
| 02/03/05/0232253 | 2002-03-05 02:15:13 | 584 | 366
| 366,366,56,0,0,0,0 |
+------------------+---------------------+------------+--------------
+--------------------+
1 row in set (0.00 sec)

mysql> select * from discussions where id=584;
Empty set (0.00 sec)

mysql> select count(*) from comments where sid=584;
+----------+
| count(*) |
+----------+
| 366 |
+----------+
1 row in set (0.00 sec)

mysql> select sid,cid,date,subject from comments where sid=584
order by cid limit 3;
+-----+------+---------------------+----------------+
| sid | cid | date | subject |
+-----+------+---------------------+----------------+
| 584 | 8322 | 2002-03-05 03:34:01 | FUD |
| 584 | 8325 | 2002-03-05 03:51:14 | Same old story |
| 584 | 8326 | 2002-03-05 03:57:32 | Re:FUD |
+-----+------+---------------------+----------------+
3 rows in set (0.00 sec)

The fix is to create rows in the discussions table where they are
missing. The LEFT JOIN syntax above should help find stories.sid's
that have no corresponding discussions.

This is important not only to restore missing comments, but
because, when this happens, freshenup.pl does not know how to
proceed. These stories are all marked as dirty and freshenup.pl
keeps trying and failing to refresh them every minute. So the
slashcluster's slashd is wasting 95% of its effort on the same few
dozen stories, over and over, 24/7.

Discussion

  • Jonathan Pater
    Jonathan Pater
    2004-04-27

    • assigned_to: cowboyneal --> cmdrtaco
    • status: open --> open-fixed
     
  • Rob Malda
    Rob Malda
    2004-04-27

    • status: open-fixed --> closed-fixed