SoylentNews

Acknowledgements:

For those who may not be aware, all staff on SoylentNews are volunteers; nobody has ever been paid anything for their work on this site. Everything has been done in people's spare time. Early morning before work. During work breaks. Nights, weekends, holidays, and vacations too.

I hereby express my gratitude to all who have supported my work on this investigation. I have invested at least two person-weeks of effort digging into this. This necessitated my letting up on processing story submissions and making them available on the site. Please join me in thanking janrinok, fnord666, takyon, and chromas who stepped up and carried much of the editing load while I have been busy on this investigation. Thanks are in order, too, for @SemperOSS; lending a most-welcome helping hand in finding locations of — and incantations for extracting pertinent data from — important information about how the site is configured and operates.

teamwork++

Early Site History:

For those who may not have been here at the beginning, SoylentNews arose as a result of frustration with how Slashdot failed to listen to what they called "their audience" in response to their rollout of a Beta version of their site. (And also lead to the infamous "Buck Feta!" war cry.) This, in turn, lead to the infamous "Slashcott" — a week-long period where users voluntarily abstained from visiting Slashdot. (Slashdot boycott). This Slashcott was then extended for an additional week.

While this was happening, some like-minded folk got together and found an open-sourced release of slashcode. This code was a few years out of date and depended on old versions of other applications. Among other things, this included the database server (MySQL), the web server (Apache), web cache (memcached), and Perl in which the site code had been written. In a frantic two-week-long period, these intrepid coders took this ancient software and hacked it into "running condition" and SoylentNews went public on February 16th, 2014.

The first few weeks of SoylentNews were filled with site crashes and reports of display artifacts and problems with the general operation of the site. In short, many bugs resulting from the feverish pace of development revealed themselves and, over time, were found and quashed. It is a testament to their hard work and dedication that problems with the site are, today, viewed as an aberration instead of a regular occurrence. I do not know whether or not the original Slashcode ever underwent a rigorous review. But, given the amount of hacking involved in just getting SoylentNews up-and-running, any stability of the current site is mostly a result of extremely dedicated Whack-A-Mole debugging.

Sections:

• Site Crash on 2019-08-15
• Malicious URL?
• Other Sources
• Use the Source, Luke!
• The Journals-Related Tables
• The Discussion-Related Tables
• Or is it?
• The Comment-Related Tables
• Trying Another Perspective
• The Message-Related Tables
• Conclusion
• Recommendation

Site Crash on 2019-08-15:

We had a site crash on August 15, 2019. We do not know why. Given the site had already been down for several hours, it was deemed necessary to restore a backup to the drive on which the site database resides. No root cause analysis is practicable at this time. Over the next couple days, we found some artifacts from that unplanned crash and site restart. For example, we discovered that slashd — the daemon which launches periodic processes in support of the site (such as emailing notifications about comment moderations and replies) had not been restarted. Also, it is possible that some race condition arose that interfered with the site properly saving a Journal Entry.

Malicious URL?:

It was suggested that someone could construct a malicious URL to delete another user's journal. The author of a Journal Entry has a button they can click so as to delete one of their own Journal Entries. It was thought that, perhaps, a modification of the resultant URL might suffice to delete a Journal Entry for someone else. I tried this on our development server where I have "god mode" privileges; absolutely nobody on the production SoylentNews site has this privilege level. Even with these elevated privileges, my attempt failed: I saw no error message, but neither was the Journal Entry deleted.

Finding: Even the most-priviledged user cannot use a malicious URL to delete a Journal Entry.

Other Sources:

As part of this investigation, I looked for data that may have been captured externally. I posed queries to Google for the Journal Entry author's nickname and the date of the post, and found nothing. Another place I looked was the Wayback Machine on the Internet Archive. I found these archives for that date:

• https://web.archive.org/web/20190824042431/https://soylentnews.org/
• https://web.archive.org/web/20190824102455/https://soylentnews.org/
• https://web.archive.org/web/20190824162417/https://soylentnews.org/
• https://web.archive.org/web/20190824222439/https://soylentnews.org/

Notice how the link contains the date and time of when the site was scanned: "YYYY MM DD hh mm ss". The first two links bracket the date/time in question. I could find no indication of a Journal Entry there, either.

Finding: Failed to find an external source which showed that the Journal Entry had been posted to the site.

Use the Source, Luke!

Well, the source of data for the entire site: the database, that is. Having found no external corroboration, there was nothing left but to dive into the database.

It is worth mentioning at this point that the source code for SoylentNews has been made available on GitHub under https://github.com/soylentnews. Note that the original slashcode on which soylentnews was built was saved under https://github.com/SoylentNews/slashcode and had a last commit in September of 2009. Observant readers will note that leaves a nearly 4.5 year gap from last commit and the use of it as a base for SoylentNews which launched on 2014-02-16 . Many dependent packages had undergone major updates and bug fixes since that time. This required significant modification in the received code just to be able to run at all.

The code which runs the current SoylentNews site can be found under https://github.com/SoylentNews/rehash.

The Journals-Related Tables:

This was the obvious starting point. There are 4 potential tables of interest:

• journal_themes
• journal_transfer
• journals
• journals_text

Nothing much of interest in journal_themes:

mysql> DESCRIBE journal_themes ;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)         | NO   | UNI | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM journal_themes ORDER BY id ;
+----+----------+
| id | name     |
+----+----------+
|  1 | generic  |
|  2 | greypage |
|  3 | bluebox  |
+----+----------+
3 rows in set (0.00 sec)

There is nothing at all in the journal_transfer table:

mysql> SELECT count(*) FROM journal_transfer ;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>

The journals table provides an "abstraction" of a Journal Entry. The actual text of a Journal Entry is stored in the journals_text table:

mysql> DESCRIBE journals_text;
+-----------+-----------------------+------+-----+---------+-------+
| Field     | Type                  | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+-------+
| id        | mediumint(8) unsigned | NO   | PRI | NULL    |       |
| article   | mediumtext            | NO   |     | NULL    |       |
| introtext | mediumtext            | NO   |     | NULL    |       |
+-----------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

We will come back to this table shortly. For now, suffice it to say that the id field provides an index into both the journals and journals_text tables, the introtext field contains the HTML for the Journal Entry, and the article field contains a plain-text rendition of the introtext field.

Now, let's look closer at the journals table:

mysql> DESCRIBE journals ;
+-------------+------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                               | Null | Key | Default           | Extra                       |
+-------------+------------------------------------+------+-----+-------------------+-----------------------------+
| id          | mediumint(8) unsigned              | NO   | PRI | NULL              | auto_increment              |
| uid         | mediumint(8) unsigned              | NO   | MUL | NULL              |                             |
| date        | datetime                           | NO   |     | NULL              |                             |
| description | varchar(80)                        | NO   |     | NULL              |                             |
| posttype    | tinyint(4)                         | NO   |     | 2                 |                             |
| discussion  | mediumint(8) unsigned              | YES  |     | NULL              |                             |
| tid         | smallint(5) unsigned               | NO   | MUL | NULL              |                             |
| promotetype | enum('publicize','publish','post') | NO   |     | publish           |                             |
| last_update | timestamp                          | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| srcid_32    | bigint(20) unsigned                | NO   | MUL | 0                 |                             |
| srcid_24    | bigint(20) unsigned                | NO   | MUL | 0                 |                             |
+-------------+------------------------------------+------+-----+-------------------+-----------------------------+
11 rows in set (0.01 sec)

mysql>

Let's first look for entries made within a day on either side of 2019-08-24:

mysql> SELECT id, date, uid, description, discussion FROM journals WHERE "2019-08-23" < date AND date < "2019-08-26" ORDER BY id ;
+------+---------------------+------+-------------------------------------------------------------+------------+
| id   | date                | uid  | description                                                 | discussion |
+------+---------------------+------+-------------------------------------------------------------+------------+
| 4512 | 2019-08-23 15:02:42 | 4388 | You Are Banned.                                             |      33255 |
| 4513 | 2019-08-23 16:25:06 | 6150 | "Our great American companies are hereby ordered..."        |      33256 |
| 4514 | 2019-08-24 04:38:24 | 4543 | Perdurabo.                                                  |      33265 |
| 4515 | 2019-08-24 08:17:01 | 4543 | By request, The HU.                                         |      33266 |
| 4517 | 2019-08-24 20:09:11 | 5086 | No, Virginia, "conservatism" is not the new counter-culture |      33274 |
| 4518 | 2019-08-25 13:35:30 | 2926 | The 'Soft' Racism of Gun Control                            |      33285 |
| 4520 | 2019-08-25 16:38:48 | 5291 | Valediction: On corruption and moderation                   |      33287 |
| 4521 | 2019-08-25 22:04:51 | 3682 | Plasma Tsunamis Responsible for Sunspots                    |      33289 |
+------+---------------------+------+-------------------------------------------------------------+------------+
8 rows in set (0.02 sec)

mysql>

Notice that (id==4520) was posted by this user (uid==5291), but... it is not the droid Journal Entry we were looking for. Looking closer, notice how we are missing (id==4516) and (id=4519). Apparently, deleting a Journal Entry actually deletes it from the database, instead of leaving it in the DB but flagging it as being deleted (i.e. unavailable).

Historical Perspective: Slashdot was registered on 1996-11-17. Disk space usage was a much greater concern back then. For historical perspective consider that Windows 95 had just been released and that the Pentium MMX and Pentium Pro were introduced in about the same time period. As for disk storage, this timeline of hard disk drives notes that in 1996 Seagate shipped its first 10,000 RPM "Cheetah" disk drive and 1997 saw the release of the "IBM Deskstar 16GP 'Titan' - 16,800 megabytes, five 3.5-inch disks". Yes, 16,800 MB or 16.8 GB. Flash drives of that capacity can be purchased for under $10 today.

So, getting back to the missing entries, we can ignore (id==4519) as that was clearly well past the time of interest. That leaves (id==4516)... where did it go?

Let's see what we can learn from looking at the journals_text table.

mysql> SELECT id, substr(introtext, 1, 80) FROM journals_text WHERE 4512 <= id AND id <= 4521 ORDER BY id ;
+------+----------------------------------------------------------------------------------+
| id   | substr(introtext, 1, 80)                                                         |
+------+----------------------------------------------------------------------------------+
| 4512 | <p>This morning, I took a quick look at the website of the Church of the Flying  |
| 4513 | <p>You probably saw who said that, so I don't have to mention his name.</p><p>Bu |
| 4514 | <tt>I've mentioned before that I perceive hip-hop as more vibrant, more alive, t |
| 4515 | <tt>https://www.youtube.com/watch?v=v4xZUr0BEfE<br><br>Please, anyone, contribut |
| 4517 | Recently one of our frothier, crazier members, who is in my opinion a fascinatin |
| 4518 | <p>The historically racist origins of gun control are hardly a topic for debate. |
| 4520 | <p>This will be my final interaction with this site, which I have supported for  |
| 4521 | <p><b>Rejected submission by RandomFactor at 2019-07-25 21:17:28 from the tSUNam |
+------+----------------------------------------------------------------------------------+
8 rows in set (0.01 sec)

Hmmm, same missing entries as before; no joy here.

Attentive readers will have noticed the discussion column in the journals table. Further, for (id==4515) we have (discussion==33266) and for (id==4517) we have (discussion==33274). We will come back to those presently.

Finding: We have failed to find the sought-for Journal Entry, but have found two tuples of id and discussion that warrant further investigation.

The Discussion-Related Tables:

Stepping back for a moment, since comments can be posted to both Journal Entries and stories, it makes sense they may share some commonality. There are two tables related to discussions in our database: discussion_kinds and discussions.

mysql> DESCRIBE discussion_kinds ;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| dkid  | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)         | NO   | UNI |         |                |
+-------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM discussion_kinds ORDER BY dkid ;
+------+---------------+
| dkid | name          |
+------+---------------+
|    1 | story         |
|    2 | user_created  |
|    3 | journal       |
|    4 | journal-story |
|    5 | poll          |
|    6 | submission    |
|    7 | feed          |
|    8 | project       |
+------+---------------+
8 rows in set (0.00 sec)

Now we are getting somewhere! There are entries for story and for journal, just as we had hoped!

The other table of interest was discussions. Let's see what we can find there.

mysql> DESCRIBE discussions ;
+---------------+------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                                                                                           | Null | Key | Default             | Extra                       |
+---------------+------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
| id            | mediumint(8) unsigned                                                                          | NO   | PRI | NULL                | auto_increment              |
| dkid          | tinyint(3) unsigned                                                                            | NO   |     | 1                   |                             |
| stoid         | mediumint(8) unsigned                                                                          | NO   | MUL | 0                   |                             |
| sid           | char(16)                                                                                       | NO   | MUL |                     |                             |
| title         | varchar(128)                                                                                   | NO   |     | NULL                |                             |
| url           | varchar(255)                                                                                   | NO   |     | NULL                |                             |
| topic         | int(10) unsigned                                                                               | NO   | MUL | NULL                |                             |
| ts            | datetime                                                                                       | NO   |     | 1970-01-01 00:00:00 |                             |
| type          | enum('open','recycle','archived')                                                              | NO   | MUL | open                |                             |
| uid           | mediumint(8) unsigned                                                                          | NO   |     | NULL                |                             |
| commentcount  | smallint(5) unsigned                                                                           | NO   |     | 0                   |                             |
| flags         | enum('ok','delete','dirty')                                                                    | NO   |     | ok                  |                             |
| primaryskid   | smallint(5) unsigned                                                                           | YES  | MUL | NULL                |                             |
| last_update   | timestamp                                                                                      | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| approved      | tinyint(3) unsigned                                                                            | NO   |     | 0                   |                             |
| commentstatus | enum('disabled','enabled','friends_only','friends_fof_only','no_foe','no_foe_eof','logged_in') | NO   |     | enabled             |                             |
| archivable    | enum('no','yes')                                                                               | NO   |     | yes                 |                             |
| legacy        | enum('no','yes')                                                                               | NO   |     | yes                 |                             |
+---------------+------------------------------------------------------------------------------------------------+------+-----+---------------------+-----------------------------+
18 rows in set (0.01 sec)

So far, so good. Let's take a look inside.

mysql> SELECT id, dkid, stoid, sid, substr(title,1,20), url, ts, uid, last_update FROM discussions WHERE "2019-08-23 00:00:01" < ts AND ts < "2019-08-24 23:59:59" ORDER BY id ;
+-------+------+--------+------------------+----------------------+---------------------------------------------------+---------------------+------+---------------------+
| id    | dkid | stoid  | sid              | substr(title,1,20)   | url                                               | ts                  | uid  | last_update         |
+-------+------+--------+------------------+----------------------+---------------------------------------------------+---------------------+------+---------------------+
| 33258 |    1 | 370766 | 19/08/23/2359248 | Billionaire David Ko | //soylentnews.org/article.pl?sid=19/08/23/2359248 | 2019-08-24 00:28:00 |   76 | 2019-08-29 01:41:03 |
| 33259 |    1 | 370767 | 19/08/24/0012203 | Study Corroborates t | //soylentnews.org/article.pl?sid=19/08/24/0012203 | 2019-08-24 02:49:00 |   76 | 2019-08-27 04:52:14 |
| 33260 |    1 | 370768 | 19/08/24/0021202 | Alleged "Snake Oil"  | //soylentnews.org/article.pl?sid=19/08/24/0021202 | 2019-08-24 05:12:00 |   76 | 2019-08-25 06:48:09 |
| 33261 |    1 | 370769 | 19/08/24/0027221 | Federal Agency Turf  | //soylentnews.org/article.pl?sid=19/08/24/0027221 | 2019-08-24 07:32:00 |   76 | 2019-08-25 23:40:34 |
| 33262 |    1 | 370770 | 19/08/24/0038241 | Phone Companies and  | //soylentnews.org/article.pl?sid=19/08/24/0038241 | 2019-08-24 09:54:00 |   76 | 2019-08-27 02:50:36 |
| 33263 |    1 | 370771 | 19/08/24/0133226 | EU Officials Draft P | //soylentnews.org/article.pl?sid=19/08/24/0133226 | 2019-08-24 12:19:00 |   76 | 2019-08-25 11:48:20 |
| 33264 |    1 | 370772 | 19/08/24/0135254 | YouTube Banning Comb | //soylentnews.org/article.pl?sid=19/08/24/0135254 | 2019-08-24 14:37:00 |   76 | 2019-08-25 04:10:23 |
| 33265 |    3 |      0 |                  | Perdurabo.           | //soylentnews.org/~Arik/journal/4514              | 2019-08-24 04:38:24 | 4543 | 2019-08-27 08:28:48 |
| 33266 |    3 |      0 |                  | By request, The HU.  | //soylentnews.org/~Arik/journal/4515              | 2019-08-24 08:17:01 | 4543 | 2019-08-27 15:02:04 |
| 33267 |    1 | 370773 | 19/08/24/128219  | NASA Astronaut's Div | //soylentnews.org/article.pl?sid=19/08/24/128219  | 2019-08-24 16:55:00 |   76 | 2019-08-29 18:13:08 |
| 33268 |    1 | 370774 | 19/08/24/1216253 | Neil Young on Sound  | //soylentnews.org/article.pl?sid=19/08/24/1216253 | 2019-08-24 19:17:00 |   76 | 2019-08-26 18:42:21 |
| 33269 |    3 |      0 |                  | Test                 | //soylentnews.org/~AthanasiusKircher/journal/4516 | 2019-08-24 12:55:02 | 5291 | 2019-08-24 17:17:12 |
| 33270 |    1 | 370775 | 19/08/24/1341250 | Deadly Superbug Outb | //soylentnews.org/article.pl?sid=19/08/24/1341250 | 2019-08-24 21:34:00 |   52 | 2019-08-26 04:19:22 |
| 33271 |    1 | 370776 | 19/08/24/1347258 | Capsule Containing R | //soylentnews.org/article.pl?sid=19/08/24/1347258 | 2019-08-24 23:53:00 |   52 | 2019-08-26 18:27:55 |
| 33274 |    3 |      0 |                  | No, Virginia, "conse | //soylentnews.org/~Azuma+Hazuki/journal/4517      | 2019-08-24 20:09:11 | 5086 | 2019-09-09 06:29:43 |
+-------+------+--------+------------------+----------------------+---------------------------------------------------+---------------------+------+---------------------+
15 rows in set (0.62 sec)

And how about that! There — at (id==33269) — is our missing Journal Entry number 4516.

Or is it?

Let's make no assumptions. Let's try to load it in a browser https://soylentnews.org/~AthanasiusKircher/journal/4516:

Sorry, the requested journal entries were not found.

Now that is some progress. Really! We found something that was posted to a journal, but was deleted. The very one that the user stated they had created after noticing the apparent disappearance of his journal article.

So, umm, where is the other Journal Entry? You know, the missing one? Yeah, we're still working on that one, but at least our theory of what happens in the site code for an intentional deletion of a Journal Entry is validated.

Looking back, remember the discussion column from the journals table and those we were going to come back to. Now is the time; here is what we had:

for (id==4515) we have (discussion==33266) and for (id==4517) we have (discussion==33274)

Here, in the discussions table, we see that discussion 33266 corresponds to https://soylentnews.org/~Arik/journal/4515 and discussion 33274 corresponds to https://soylentnews.org/~Azuma+Hazuki/journal/4517 so we have figured out where they went. Unfortunately, this does not provide any closure on the situation, but it does close off one potential avenue of explanation.

Finding: We have found evidence that when a user deletes a Journal Entry, any comments that had been posted to it persist in the database. None have yet been found.

The Comment-Related Tables:

As was the case for the journals table, SoylentNews' comments are split up as well. Here are all of the comment-related tables:

• comment_log
• comment_promote_log
• comment_text
• commentmodes
• comments

I do not know why they still exist, but both comment_log and comment_promote_log are empty. As for commentmodes, that is the source for the pull-down list of how one chooses to see comments displayed:

mysql> SELECT * FROM commentmodes ORDER BY mode ;
+-----------+--------------+-------------+
| mode      | name         | description |
+-----------+--------------+-------------+
| flat      | Flat         |             |
| nocomment | No Comments  |             |
| threadtng | Threaded-TNG | NULL        |
| threadtos | Threaded-TOS | NULL        |
+-----------+--------------+-------------+
4 rows in set (0.00 sec)

So now we are down to the comments and comment_text tables. Last things first, the comment_text table is exactly what it says on the tin, where the text of each comment is stored:

mysql> DESCRIBE comment_text ;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| cid     | int(10) unsigned | NO   | PRI | NULL    |       |
| comment | mediumtext       | NO   |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

The key thing here is the cid (Comment ID) field which is a pointer (key) into the comments table. Having gotten all that out of the way, what can the actual comments table tell us?

mysql> DESCRIBE comments ;
+------------------+-----------------------+------+-----+---------------------+-----------------------------+
| Field            | Type                  | Null | Key | Default             | Extra                       |
+------------------+-----------------------+------+-----+---------------------+-----------------------------+
| sid              | mediumint(8) unsigned | NO   | MUL | NULL                |                             |
| cid              | int(10) unsigned      | NO   | PRI | NULL                | auto_increment              |
| pid              | int(10) unsigned      | NO   | MUL | 0                   |                             |
| opid             | int(10) unsigned      | NO   | MUL | 0                   |                             |
| children         | int(10) unsigned      | NO   |     | 0                   |                             |
| date             | datetime              | NO   | MUL | 1970-01-01 00:00:00 |                             |
| last_update      | timestamp             | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| ipid             | char(32)              | NO   | MUL |                     |                             |
| subnetid         | char(32)              | NO   | MUL |                     |                             |
| subject          | varchar(50)           | NO   |     | NULL                |                             |
| subject_orig     | enum('no','yes')      | NO   |     | yes                 |                             |
| uid              | mediumint(8) unsigned | NO   | MUL | NULL                |                             |
| points           | tinyint(4)            | NO   |     | 0                   |                             |
| pointsorig       | tinyint(4)            | NO   |     | 0                   |                             |
| pointsmax        | tinyint(4)            | NO   |     | 0                   |                             |
| lastmod          | mediumint(8) unsigned | NO   |     | 0                   |                             |
| reason           | tinyint(3) unsigned   | NO   |     | 0                   |                             |
| signature        | char(32)              | NO   |     |                     |                             |
| karma_bonus      | enum('yes','no')      | NO   |     | no                  |                             |
| subscriber_bonus | enum('no','yes')      | NO   |     | no                  |                             |
| len              | smallint(5) unsigned  | NO   |     | 0                   |                             |
| karma            | smallint(6)           | NO   |     | 0                   |                             |
| karma_abs        | smallint(5) unsigned  | NO   |     | 0                   |                             |
| tweak_orig       | tinyint(4)            | NO   |     | 0                   |                             |
| tweak            | tinyint(4)            | NO   |     | 0                   |                             |
| badge_id         | tinyint(4)            | NO   |     | 0                   |                             |
+------------------+-----------------------+------+-----+---------------------+-----------------------------+
26 rows in set (0.00 sec)

Zoinks! That's a lot, but we can ignore much of it for our purposes. We just want to know if any comments were made to the missing Journal Entry. Let's look at a subset:

  mysql> SELECT cid, sid, pid, opid, uid, children, date, last_update, subject FROM comments WHERE pid = 0 AND "2019-08-24 06:00:00" <= date AND date <= "2019-08-24 08:00:00" ORDER BY cid;
+--------+-------+--------+--------+------+----------+---------------------+---------------------+--------------------------------------------------+
| cid    | sid   | pid    | opid   | uid  | children | date                | last_update         | subject                                          |
+--------+-------+--------+--------+------+----------+---------------------+---------------------+--------------------------------------------------+
| 884595 | 33250 | 884485 | 884479 |    1 |        0 | 2019-08-24 06:00:50 | 2019-09-23 07:02:01 | Re:so?                                           |
| 884596 | 33244 | 884418 | 883941 |    1 |       19 | 2019-08-24 06:01:46 | 2019-09-23 07:02:01 | Re:Sign of the times                             |
| 884597 | 33265 |      0 |      0 |   18 |       15 | 2019-08-24 06:03:10 | 2019-09-23 07:02:01 | Wait, wut?                                       |
| 884598 | 33259 | 884585 | 884531 |    1 |        1 | 2019-08-24 06:08:34 | 2019-09-23 07:02:01 | Re:Wow! Then radiation hits fetus...             |
| 884599 | 33244 | 884520 | 883955 |    1 |        2 | 2019-08-24 06:10:36 | 2019-09-23 07:02:01 | Re:It's exaeta again, isn't it?                  |
| 884600 | 33244 | 884596 | 883941 |   18 |       18 | 2019-08-24 06:11:13 | 2019-09-23 07:02:01 | Re:Sign of the times                             |
| 884601 | 33252 | 884400 | 884336 | 2828 |        0 | 2019-08-24 06:11:29 | 2019-09-23 07:02:01 | Re:What's the appeal of mercedes?                |
| 884602 | 33265 | 884581 | 884581 | 2489 |        2 | 2019-08-24 06:18:09 | 2019-09-23 07:02:01 | Re:Is a rapper a singer?                         |
| 884603 | 33259 | 884598 | 884531 |    1 |        0 | 2019-08-24 06:27:33 | 2019-09-23 07:02:01 | Re:Wow! Then radiation hits fetus...             |
| 884604 | 33259 | 884530 | 884530 | 6836 |        6 | 2019-08-24 06:30:48 | 2019-09-23 07:02:01 | Re:If so, then...                                |
| 884605 | 33265 | 884597 | 884597 | 4543 |        9 | 2019-08-24 06:31:30 | 2019-09-23 07:02:01 | Re:Wait, wut?                                    |
| 884606 | 33265 | 884597 | 884597 | 2489 |        4 | 2019-08-24 06:41:07 | 2019-09-23 07:02:01 | Re:Wait, wut?                                    |
| 884607 | 33265 | 884605 | 884597 | 2489 |        8 | 2019-08-24 06:44:10 | 2019-09-23 07:02:01 | Re:Wait, wut?                                    |
| 884608 | 33244 | 884457 | 883942 |   52 |        0 | 2019-08-24 06:47:11 | 2019-09-23 07:02:01 | Re:WTF?                                          |
| 884609 | 33259 | 884604 | 884530 |  881 |        5 | 2019-08-24 06:48:32 | 2019-09-23 07:02:01 | Re:If so, then...                                |
| 884610 | 33265 | 884607 | 884597 | 4543 |        7 | 2019-08-24 06:55:40 | 2019-09-23 07:02:01 | Re:Wait, wut?                                    |
| 884611 | 33244 | 884461 | 883966 |   52 |        1 | 2019-08-24 06:58:54 | 2019-09-23 07:02:01 | Re:Why do we moderate? - Because it is important |
| 884612 | 33259 | 884585 | 884531 |    1 |        0 | 2019-08-24 07:00:14 | 2019-09-23 07:02:01 | Re:Wow! Then radiation hits fetus...             |
| 884613 | 33255 |      0 |      0 | 3272 |        1 | 2019-08-24 07:07:03 | 2019-09-24 07:02:02 | Works on reload.                                 |
| 884614 | 33244 | 884435 | 883966 |   52 |        3 | 2019-08-24 07:08:08 | 2019-09-24 07:02:02 | Re:Why do we moderate? - Because it is important |
| 884615 | 33222 | 883944 | 883944 |    1 |        0 | 2019-08-24 07:39:07 | 2019-09-24 07:02:02 | Re:Come out, exaeta, come out!                   |
| 884616 | 33244 | 884337 | 883966 |   52 |        2 | 2019-08-24 07:39:07 | 2019-09-24 07:02:02 | Re:Why do we moderate? - Because it is important |
| 884617 | 33261 |      0 |      0 | 2452 |       16 | 2019-08-24 07:40:06 | 2019-09-24 07:02:02 | 5G is stupid                                     |
| 884618 | 33265 | 884602 | 884581 | 4543 |        1 | 2019-08-24 07:42:09 | 2019-09-24 07:02:02 | Re:Is a rapper a singer?                         |
| 884619 | 33222 | 884211 | 883718 | 2645 |        0 | 2019-08-24 07:49:42 | 2019-09-24 07:02:02 | Re:Need specifics, actually.                     |
| 884620 | 33244 | 884534 | 883966 |   52 |       13 | 2019-08-24 07:54:11 | 2019-09-24 07:02:02 | Re:Why do we moderate? - Because it is important |
+--------+-------+--------+--------+------+----------+---------------------+---------------------+--------------------------------------------------+
26 rows in set (0.00 sec)

mysql>

The pid field is the comment's "Parent ID", i.e. the comment ID of the comment that is the parent of this comment. Let's look at only those comments which have NO parent id:

mysql> SELECT cid, sid, pid, opid, uid, children, date, last_update, subject FROM comments WHERE pid = 0 AND "2019-08-24 06:00:00" <= date AND date <= "2019-08-24 08:00:00" ORDER BY cid;
+--------+-------+-----+------+------+----------+---------------------+---------------------+------------------+
| cid    | sid   | pid | opid | uid  | children | date                | last_update         | subject          |
+--------+-------+-----+------+------+----------+---------------------+---------------------+------------------+
| 884597 | 33265 |   0 |    0 |   18 |       15 | 2019-08-24 06:03:10 | 2019-09-23 07:02:01 | Wait, wut?       |
| 884613 | 33255 |   0 |    0 | 3272 |        1 | 2019-08-24 07:07:03 | 2019-09-24 07:02:02 | Works on reload. |
| 884617 | 33261 |   0 |    0 | 2452 |       16 | 2019-08-24 07:40:06 | 2019-09-24 07:02:02 | 5G is stupid     |
+--------+-------+-----+------+------+----------+---------------------+---------------------+------------------+
3 rows in set (0.78 sec)

mysql>

All of these comments pertain to the story they are attached to:

• https://soylentnews.org/comments.pl?sid=33265&cid=884597#commentwrap
• https://soylentnews.org/comments.pl?sid=33255&cid=884613#commentwrap
• https://soylentnews.org/comments.pl?sid=33261&cid=884617#commentwrap

Finding: we found no evidence of a comment having been made to a Journal Entry, during the time in question.

Trying Another Perspective:

In this comment, it was suggested:

All your fans would have received a message about your journal entry instantly. I for one didn't. You can't invisibly delete a journal entry without:
a) resetting the journal auto-incrementing counter in the database (and there's no programattic way to do that, you can only move it forwards)
but also:
b) invisibly deleting *all* the messages that were sent out because of it
and therefore:
c) resetting the message auto-incrementing counter in the database (ditto)
and here's the killer:
d) on a live system
and the cherry on top:
e) with no logs

If we had someone with that level of technical competence on the administrative team, that would probably double the total amount of competence have - the above goes well beyond mere guru level.

Ockham's razor points to either human error or a bug.

We've previously shown that articles can be deleted; there is a button for it on the journal writer's edit page.

If the deleted Journal Entry was the one with the largest ID, the system just re-uses that slot.

But, the idea that a message might have been sent out announcing to a user that a friend of theirs posted a Journal Entry? that's interesting. Let's see where that leads us.

Finding: We have a new situation to investigate, but no results, yet.

The Message-Related Tables:

Here are all the message-related tables on the site:

• message_codes
• message_deliverymodes
• message_drop
• message_log
• message_web
• message_web_text

At this point, selections made by particular users will be discovered. In the interests of excluding 3rd-parties, please forgive me if some of this discussion is less detailed than previously.

Let's take these in order:

mysql> DESCRIBE message_codes   ;
+-----------------+----------------------------------+------+-----+---------+-------+
| Field           | Type                             | Null | Key | Default | Extra |
+-----------------+----------------------------------+------+-----+---------+-------+
| code            | int(11)                          | NO   | PRI | NULL    |       |
| type            | varchar(32)                      | NO   |     | NULL    |       |
| seclev          | int(11)                          | NO   |     | 1       |       |
| modes           | varchar(32)                      | NO   |     |         |       |
| send            | enum('now','defer','collective') | NO   |     | now     |       |
| subscribe       | tinyint(1)                       | NO   |     | 0       |       |
| acl             | varchar(32)                      | NO   |     |         |       |
| delivery_bvalue | int(10) unsigned                 | NO   |     | 0       |       |
+-----------------+----------------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM message_codes WHERE seclev = 1 ORDER BY code ;
+------+----------------------------+---------+-------+------------+-----------+-------+-----------------+
| code | type                       | seclev  | modes | send       | subscribe | acl   | delivery_bvalue |
+------+----------------------------+---------+-------+------------+-----------+-------+-----------------+
|   -2 | Registration Mail          |       1 | 0     | now        |         0 |       |               0 |
|   -1 | Unknown Message            |       1 | 0     | now        |         0 |       |               0 |
|    0 | Daily Newsletter           |       1 | 0     | now        |         0 |       |               1 |
|    1 | Daily Headlines            |       1 | 0     | now        |         0 |       |               1 |
|    2 | Metamoderation Results     |       1 |       | collective |         0 |       |               3 |
|    3 | Comment Moderation         |       1 |       | collective |         0 |       |               3 |
|    4 | Comment Reply              |       1 |       | now        |         0 |       |               3 |
|    5 | Journal Entry by Friend    |       1 |       | now        |         0 |       |               3 |
|    6 | New Submission             |     100 | 0     | now        |         0 |       |               1 |
|    7 | Journal Reply              |       1 |       | now        |         0 |       |               3 |
|    8 | New Comment                | 1000000 |       | now        |         0 |       |               0 |
|   10 | Daily Site Stats           |     100 | 0     | now        |         0 | stats |               1 |
|   11 | Email Story                |       1 | 0     | now        |         0 |       |               1 |
|   12 | Relationship Change        |       1 |       | collective |         0 |       |               3 |
|   13 | Bad login attempt warnings |       1 | 1     | now        |         0 |       |               2 |
|   14 | Daily Moderation Stats     |     100 | 0     | now        |         0 | stats |               1 |
|   15 | Subscription Running Low   |       1 |       | now        |         1 |       |               1 |
|   16 | Subscription Expired       |       1 |       | now        |         1 |       |               1 |
|   18 | Invalid HTML Input         |     100 |       | now        |         0 |       |               3 |
|   19 | Declined Submission Reason |       1 |       | now        |         0 |       |               3 |
|   20 | Admin to user message      |       1 |       | now        |         0 |       |               3 |
|   22 | Achievement                |       1 |       | now        |         0 |       |               3 |
|   25 | Remarks                    |     100 |       | now        |         0 |       |               2 |
+------+----------------------------+---------+-------+------------+-----------+-------+-----------------+
23 rows in set (0.00 sec)

mysql>

Now, this looks promising; (code==5) is "Journal Entry by Friend". Let's hold on to that and see what else we can find.

mysql> DESCRIBE message_deliverymodes ;
+----------+-----------------------+------+-----+---------+-------+
| Field    | Type                  | Null | Key | Default | Extra |
+----------+-----------------------+------+-----+---------+-------+
| code     | smallint(6)           | NO   | PRI | 0       |       |
| name     | varchar(32)           | NO   |     |         |       |
| bitvalue | mediumint(8) unsigned | NO   |     | 0       |       |
+----------+-----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM message_deliverymodes ORDER BY code ;
+------+-------------+----------+
| code | name        | bitvalue |
+------+-------------+----------+
|   -1 | No Messages |        0 |
|    0 | E-mail      |        1 |
|    1 | Web         |        2 |
+------+-------------+----------+
3 rows in set (0.00 sec)

mysql>

A registered user (someone who has a nickname and UID on the system) can specify several things for which they wish to receive notifications, and how they want to receive them. This table is where those are defined. On to the next table.

mysql> DESCRIBE message_drop ;
+---------+----------------------------------+------+-----+-------------------+-----------------------------+
| Field   | Type                             | Null | Key | Default           | Extra                       |
+---------+----------------------------------+------+-----+-------------------+-----------------------------+
| id      | int(11)                          | NO   | PRI | NULL              | auto_increment              |
| user    | mediumint(8) unsigned            | NO   |     | NULL              |                             |
| fuser   | mediumint(8) unsigned            | NO   |     | NULL              |                             |
| code    | int(11)                          | NO   |     | -1                |                             |
| date    | timestamp                        | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| altto   | varchar(50)                      | NO   |     |                   |                             |
| send    | enum('now','defer','collective') | NO   |     | now               |                             |
| message | mediumblob                       | NO   |     | NULL              |                             |
+---------+----------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql> SELECT count(*) FROM message_drop ;
+----------+
| count(*) |
+----------+
|      115 |
+----------+
1 row in set (0.00 sec)

mysql>

This table contains a listing of the messages that have been deleted today. Logged-in users can go to their messages inbox, and if they have any pending, web-based messages, will see this message at the top of the screen:

These messages will be kept in the system for only 14 days, whether they have been read or not. After 14 days, they will be deleted.

Messages marked with "*" are unread.

Unfortunately, I did not learn of this table until several days too late.

There's nothing to do at this point but to continue on. Besides, the next table is much more interesting.

mysql> DESCRIBE message_log ;
+-------+-----------------------+------+-----+-------------------+-----------------------------+
| Field | Type                  | Null | Key | Default           | Extra                       |
+-------+-----------------------+------+-----+-------------------+-----------------------------+
| id    | int(11)               | NO   |     | NULL              |                             |
| user  | mediumint(8) unsigned | NO   |     | NULL              |                             |
| fuser | mediumint(8) unsigned | NO   |     | NULL              |                             |
| code  | int(11)               | NO   |     | -1                |                             |
| mode  | int(11)               | NO   |     | NULL              |                             |
| date  | timestamp             | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

mysql>

One thing we can find out here is how often messages about friend's Journal Entries are posted to the site.

mysql> SELECT count(1) AS count, substr(date,15,2) AS mins FROM message_log WHERE code = 5 GROUP BY mins ORDER BY mins;
+-------+------+
| count | mins |
+-------+------+
|    39 | 05   |
|   148 | 10   |
|    77 | 15   |
|    12 | 20   |
|    22 | 25   |
|    60 | 30   |
|    46 | 35   |
|    13 | 40   |
|    25 | 45   |
|    21 | 50   |
|    62 | 55   |
+-------+------+
11 rows in set (0.01 sec)

mysql>

So, every five minutes, if someone has posted a Journal Entry, the site sends out notifications to all those who had tagged the writer as a friend.

As mentioned earlier, so as to avoid unnecessarily involving innocent third parties, a quick look through the tables containing relationships (friends) and notification requests reveals:

• there are 14 people who flagged the journal-entry-writer as a friend,
• of these, 12 have requested notification of when they have posted a Journal Entry
• all 12 of these have requested notification by 'the web' instead of by 'e-mail'.

A search through the message_log table for any messages sent to any of these people for journal posts by the accuser came up empty.

Finding: No indication has been found that a message was sent to a user to notify them of a friend having posted an entry to their journal.

Conclusion:

Multiple sources were investigated to try and find a Journal Entry that was posted to the site and subsequently deleted. External sources were examined and no copy of the alleged Journal Entry were found. A user who has submited a Journal Entry can subsequently delete it, but any comments made to that Journal Entry would persist after such a deletion. No such comments were found. Further, twelve users flagged this user as a friend and requested notification of their having posted a Journal Entry. No such notification was found to have been generated.

A staff member with sufficient privileges and knowledge could, potentially, have deleted a Journal Entry directly through the database. The window of opportunity is at most five minutes, and possibly as short as a few seconds. Such action would have to be performed on a live system and leave no trace of it having been done. This would, also, require the staff memeber to have noticed the posting very shortly after it was saved. Though not impossible, the likelihood of this being the case is vanishingly small.

The user could have written the Journal Entry and, somehow, forgotten to save it. Further discussion with the user revealed their having taken extreme care in updating and saving their Journal Entry.

Given these, and the fact that yet another bug in the code was found while writing this report, combined with the frantic pace of the site's initial development and the many bugs found subsequent to its initial deployment, it appears that the user encountered a bug in the site's handling of posting a new Journal Entry.

Recommendation: It is suggested that the site be updated so that upon successful receipt of a Journal Entry, said entry be immediately displayed to the user by employing the same means that the site uses to display any Journal Entry.