Comments were accidentally overwritten

Some clowns decided it would be a good idea to have a hateful political discussion in a release’s comment section.

I was alerted to this and tried to clean it up. As we have no tools for moderation I went directly into the SQLite file. Usually I take a backup before live editing that but today I did not (I was majorly angry so not thinking straight).

I managed to do the classical “UPDATE without WHERE clause” mistake and updated each and every comment’s text with the same string.

I did not migrate a daily database backup when moving to a new server. The last full backup is from November. :\

I am sorry.

The database is set to read only with its current state so we don’t end up with another version before I restore the old backup. There might be a way to restore some content before I do that.

Had some downtime as trying to dump the memory of the running system (was hoping for the old version to be in some cache) makes the system reboot…

I am scanning a drive dump for salvageable leftovers of the database. If successful, I will do my best to restore. Otherwise the map comments from November 29 until now are gone. Nothing else is affected. Sorry! is the “official” alternative source for news if the site is down.

That’s not too bad then – at least for me. My main reason for creating an account here was to keep track of my played / unplayed maps, and indeed your changes did not affect that.

Welp, rip every comment made on Dwell E1. That sucks.

[quote=Spirit](I was majorly angry so not thinking straight).[/quote]Sorry if saying that black lives matter made you feel that way.

And that is how onetruepurple got banned from Quaddicted.

To give some context, some user with a russian name posted angry comments about a map having writings about BLM and LGBT things.

Thanks for the kind words, Gioyo3aa!

Doesn’t look too bad in the Web Archive thankfully! (4 missing) (1 missing)

[quote=Spirit]Thanks for the kind words, Gioyo3aa!

Doesn’t look too bad in the Web Archive thankfully! (4 missing) (1 missing)[/quote]

Praise the archive, we pretty religiously posted screenshots of the comments in our discord channel, if you’d like to restore them as well. (If not that’s cool, pretty annoying to re-write these.)

Aww FW, I was just trying to help the important map comment archiving project, don’t wanna miss any important ones :smiley:

Sorry to hear about the data loss.

FWIW, Internet Archive has a late-December backup of Xmas Jam with the first 6 comments:

Reviews on ter_shibboleth_drake_redux’s page:

[quote=huanlopan]Reviews on ter_shibboleth_drake_redux’s page:[/quote]
That archive doesn’t have anything that Spirit doesn’t already have. Latest backup is from 29/11/2020, so only stuff from after then is needed.

One thing I noticed, some comments are still showing on the front page on the “recent activity” section. Are they stored separately in the database? Maybe there’s a way to pull them from there?

Nice idea bmFbr! While that list is fully available, it is stored as truncated strings for for display so there is only the first few characters and nothing more.

On (non-)progress:

Unfortunately the database file itself has no traces of the old versions of the comments in it.

A photorec run on the disc image (ext4) has finished and not recovered anything. I am running it again with proper settings (searching for sqlite, keeping corrupted files, …). Afterwards I will see if I can find something with a manual approach using hexdump and grep… If someone has experience with that kind of stuff, please give suggestions!

If that also is unsuccessful, recovering old comments from would be a great idea. But don’t invest any time in that yet please. :slight_smile:

[quote=Spirit]Nice idea bmFbr! While that list is fully available, it is stored as truncated strings for for display so there is only the first few characters and nothing more.
Oh that’s what I was afraid of = I thought that maybe they’d be stored in full and be truncated only on the front end

Bummer about the accident, and bummer about the circumstances that led to it. I thought that was resolved after the kerfuffle in the comment’s of czg’s latest map, jeez.

Good news! I seem to have found all comments leading up to the accident in the raw disc image! Might be a pain to reconstruct them but this looks pretty good.

This is an example what it looks like. This might be fragments of a SQLite database (photorec did not find these) or of a WAL file. The whole “dump” is about 600 Kilobytes and seems to include comments from spring 2020 until the most recent ones some days ago. I selected this extract between rows of 00s.

We could extract the comments with their map, author and text fairly easily but I wonder if the timestamps are also included in there?

This is the highlighted part and the comment would have an ID around 10000 plus a timestamp in unixtime at around 1607875382 (13 December 2020, 17:03):

00 81 35 D6 4E 08 00 04 2B 33 82 1B 08 5F D6 3B 4B 61 64 5F 76 31 5F 38 30 70 31 66 69 6E 61 6C 56 6F 72 65 49 6E 54 68 65 57 69 6C 64 65 72 6E 65 73 73 43 68 72 69 73 74 6D 61 73 20 63 61 6D 65 20 65 61 72 6C 79 21 20 54 48 41 4E 51 51 51 51 59 4F 55 2E 20 44 65 61 72 20 53 61 6E 74 61 2C 20 6E 65 78 74 20 43 68 72 69 73 74 6D 61 73 20 49 20 77 6F 75 6C 64 20 6C 69 6B 65 20 54 48 41 54 20 73 68 6F 74 67 75 6E 20 66 72 6F 6D 20 4E 65 68 61 68 72 61 2E 20 59 65 73 2C 20 79 6F 75 20 6B 6E 6F 77 20 74 68 65 20 6F 6E 65 20 49 20 6D 65 61 6E 20 3A 2D 29 20 82 52 D6 4D 08 00 04 1B 21 84 77 09 5F D6 33 BC

The table schema is:

CREATE TABLE comments ( id INTEGER PRIMARY KEY, timestamp DATE DEFAULT (strftime('%s','now')), zipname TEXT, username TEXT, comment TEXT, registered NUMERIC -- boolean rather, should be 1 or 0 IIRC );

At least the human readable strings follow this order.

Anyone into this kind of low level hackery?

Looking good! The 4 bytes before the map name do look like the timestamp as Integer in Big Endian.

5F D6 3B 4B



which is

$ date -d @1607875403 Sun Dec 13 05:03:23 PM CET 2020