Investigating a problem, help appreciated

[LisaNeverSayDie]

First, @emk: big thank you for taking this seriously! When I couldn't find myself it was quite distressing; and I didn't spot any of the other threads or posts or anything, as I'd been not keeping up for a couple of months. There was a surreal quality to never having existed.

Personally, now that I've been able to recover most of my log for my personal reference (another thank you to Purangutan!), I can live with the rest of the loss. It's sad that my other posts were lost and I think some of them were slightly interesting, and yeah it would be nice if things went back to the way they were. But water under the bridge, they aren't something anyone ever would need to see again. Not worth taking a whole lot of admin time.

(Also note, what is still lost that I would really like to see again, the part of my log that is not recoverable from Bing's cache, is from May 7 to September 4, which is mostly not going to be available if the backups are May and after the deletion).

If the contact form doesn't work, though, it would be nice to change the text to say so. I waited for a day waiting for a response...

[golyplot]

This observation is important, since it pinpoints that the problem occurred in September or during the very first days of October 2024. So there is a gap between an old backup from May and the current database.

I don't want to put too much weight on this, since other people seem to mostly be talking about a more recent outage. However, I noticed that in my own log, the link to the 4 year update is broken while the link to the 4.5 year update is still correct. This implies that a deletion occurred sometime between Jan 1 2024 and July 1 2024. Perhaps there were multiple deletions?

Actually, we do know that it mainly is the member list that is in trouble - even if all post of certain members had disappeared you would still see an empty member ID if it just was the list of posts that had been hit.

However, we do see members with a false 0 post count. For example, pinkyslippers.

Pinkyslippers currently has a post count of 0, but they definitely did post in the past. In fact, you can still see the remains of their log! Other people's comments in the thread still exist, but all of their own posts have been deleted.

It seems that there have been multiple kinds of outages, as some people like Handa had their entire account deleted, while others like Pinkyslippers "only" had all their posts deleted while their account still exists.

[Iversen]

Since I mentioned a date from May 2024 onwards new information has arrived that suggests that at least some lost accounts still existed primo October - but soon after the first reports from re-established members arrived. I mentioned my own log because I have images in almost all posts, and I can see which images I have used in each month. I checked all pages a couple of years back, and no non-deleted post was missing - and then I continued with just every fifth, later every tenth page until my page numbers from May 2024 coincided with those from today - and that happened around page 100 in 2018.

The Pinkyslippers case is interesting because it's different from the others I have read about - all posts lost, but not the bare root of the account.

[smallwhite]

Not sure if related, but my avatar is missing. Not sure since when.
It knows to say avatar=61_1512559726.jpg

[jeff_lindqvist]

My avatar disappeared around the time many others went missing. I uploaded mine again.

[badger]

my account (same username) was deleted around that time. the thread where I reported it (under new/temporary username badger1) went mysteriously missing too iirc.

rdearman said he'd been running some scripts to clean up unused/dormant users - which I wasn't - so I was a bit miffed tbh.

[jackb]

My account and all of my posts were deleted too. I recreated an account with the same username

[coldrainwater]

To whom it may help, here is the SQL logic I would use to pull missing accounts given three database snapshots restored to a new dev server not connected to production with dates: 2024-05-01, 2025-10-30 and currentDate. Assume the three restored database names are LLORG_05, LLORG_10, and LLORG_Current. This approach minimizes risk by working entirely on a dev server and minimizes SQL code by using existing views to extract data with minimal effort. It assumes entire user accounts were deleted in full, which is what I understand to be the case. The approach below should yield the same accurate data as the approach suggested earlier by Iversen and I believe it to be conceptually equivalent.

Code: Select all

--Get all accounts that existed in either prior snapshot that do not exist today.
SELECT AccountID FROM
 (SELECT AccountID FROM LLORG_05.dbo.Accounts UNION SELECT AccountID FROM LLORG_10.dbo.Accounts) BackUpAccounts
EXCEPT
SELECT AccountID FROM LLORG_Current


Code Comments (assumptions)

• The syntax is for SQL Server but can be adapted for MySQL. Use an LLM model to convert between SQL dialects.
• The three restored database names are LLORG_05, LLORG_10, and LLORG_Current all with identical schema (dbo, short for database owner).
• A table called Accounts exists with the primary key field AccountID listing all accounts in each database at the time of the snapshot.
• Each table has a CreateDate field (datetime not null), a ModifiedDate or UpdatedDate field, and other helpful metadata.
• Other tables such as Posts and Attachments exist.
• Views with analogous nomenclature are available for query.

Notes
Once you have those AccountID values, you can extract other related fields by left joining on their respective foreign keys or filtering via account directly. For simplicity, put those accounts in a new table called MissingAccounts and another called BackupAccounts as aliased above (and by analogy BackupPosts, etc). Most commercial databases will have views or stored procedures showing accounts, posts, likes, attachments and any other relevant information typically required for reporting purposes. Therefore, complex code and extra joins can be averted by leveraging those pre-existing queries. Then you can write straightforward queries such as:

Code: Select all

SELECT * FROM BackupAccounts
WHERE AccountID in (SELECT AccountID FROM MissingAccounts)

--or perhaps

SELECT * FROM BackupPosts
WHERE AccountID in (SELECT AccountID FROM MissingAccounts)


If desired, you can clean the data presented to eliminate likely spam accounts and other data irrelevant to our needs. If feasible, an idea incorporating the data long-term is to isolate the lost posts to a sub-forum that isn't intermingled with current production (designated Archived_Data or similar) and expose it for download by registered users. To maintain forum stability, I normally don't suggest complex data mergers of backups with originals, preferring to keep content isolated and separated but searchable as outlined above.

[jeffers]

rdearman said he'd been running some scripts to clean up unused/dormant users - which I wasn't - so I was a bit miffed tbh.

That's the post I was referring to in the second post in this thread, but I couldn't find it. I believe your case, Badger, was the first one I saw reported, whenever that was.

[golyplot]

In case it helps, I discovered more evidence of the deletions.

This post in my log said that it was the top of page 150 (at the time it was posted). However, it is now the fifth post on page 145 instead. This implies that 46 posts from my log were deleted sometime since August 24th.

To be honest, I'm amazed that that many posts could even have been deleted, since people didn't comment on my log very often. We already know that Handa and Pinkyslippers were affected, but I doubt they commented that much. Unfortunately, it's hard to tell what's missing now.

I also found this post reporting that someone got deleted on Nov 15-16 2022.