Little Bobby Tables Award

Can you win it??

back up and running! #announcement blog

If you are having trouble posting or seeing comments, send an email to admin@fstdt.org with your username, the browser you are using, and if possible, the IP address and ISP you're using when comments don't work. Also include any browser extensions you are using. If you prefer IM, I can be reached on Skype (search spikedee@openmbx.org) or AIM (screenname 'a nerd of sorts'). I will always fix the troll bouncer so you can comment and/or see comments again. Someone's priorities are way mixed up if keeping trolls out is more important to them than letting people use their site.

Also email or post any other errors and issues you encounter in the Site / Off-Topic Thread'!

I'm so sorry for all the downtime and any other problems!! I feel really bad for not giving you any tangible new features for the wait. I made a large number of fairly significant changes to the database, a few of them particularly major changes, like completely redoing the pubadmin table in anticipation for the FSTDT rewrite and also because of how extravagantly disorganized the pubadmin data was stored. (I discuss the details of that later in this post.)

Many changes also had to be made to the behind-the-scenes FSTDT code to accommodate for the database changes, and in one case something had to be all but completely rewritten. (When that happens, I just go ahead and get a head start on the rewrite's transition from C# back to VB — the current C# version is itself a transition from an earlier VB version — it's like some kind of bizarre cycle. I write the new code in VB, since then it will be usable in the actual big rewrite itself. A number of other changes unrelated to those necessary to accommodate the database changes were made, but none of them are major.

But the biggest change to the FSTDT server-side code, though, is that the new troll-detection and auto-cleanup system Piège-à-Pèpe (PàP) I've been talking about is finally in place and has a real name!! Much of PàP is still turned off on the server side for that reason, but everything on the client side is operational. It still does more than enough to be useful and justify going live with it.
_

Known issues

oopz

I scrapped the original Banned IPs table and replaced it with one that supports wildcards and does away with the surrogate key. This is not an "issue," but I sure as hell accidentally made one while I was working on this. I intended to export the original banned IP list and import it into the new table. Unfortunately, however, my executing a query with a single misplaced colon deleted the entire list of IPs. Since the old table had not been cleaned out in literally 8 years, I doubt this will be a major issue. Like half or even more of those lost IPs were probably Mabus in the first place. Yes, he really was that fucking crazy.

[aside]Gotta love SQL and how it will execute literally anything without asking the user if they're sure they wanna do that, not even if it is something that just screams "What this does is almost certainly a mistake, and YOU WILL REGRET EVER EXECUTING THIS!!!" If you know SQL, can you guess what the offending statement was and where I put the misplaced semicolon? If you can, I'll give you the prestigious Little Bobby Tables Award and put your name on it at the top of the page for all to marvel and envy.[/aside]

Losing the banned IP list means there's a small chance we'll see a slight increase in the number of trolls over the next few days. When and if they show their ugly troll faces, their IPs will be re-collected again and added back to the database. Piège-à-Pèpe will also help deal with some of it, so any issues with this should be fully resolved over the next few days. Still, if you see 'em, report 'em in the Site / Off-Topic Discussion Thread.
_

Proxy Penalization

This is not an issue so much as something worth noting. PàP heavily penalizes known Tor nodes and IPs from a certain major Turkish telecommunications company. If this is a problem for you because you accidentally trigger other penalization from PàP such that it incorrectly considers you a troll, then use another browser and/or a different proxy to access FSTDT. If you're completely stuck, contact me. (See info above.)

Though not done by PàP, a certain other hotbed of seediness that calls itself a web host and proxy server provider has been completely banned. If you're posting from an IP there, you're probably The Frog or someone else up to no good anyway.
_

The Croaking Cloaking Croat

PàP could quite possibly have trouble recognizing the Croat because the information and behavior PàP uses is all over the place for him. I don't know if it's deliberate or the result of him using a fuckton of different devices. He also avoids obvious Tor nodes, (likely because they were already banned here), but I know a Tor exit note emitted his verbal diarrhea at least once — and possibly twice or more if an anonymous commenter was him as I suspect.

[aside]On the other hand, Le Frog Français is much easier to spot. We will most likely be seeing much less of him as soon as his first IP is added to the fresh, new, and now-empty banlist. He unwittingly gives PàP a decent amount of information to work with and seems to think otherwise. That means his comments will be tossed out and their originating IP banned before they ever even reach the server code that adds comments. Try harder, bro.[/aside]
_

"Summary" of database changes (and relevant FSTDT code changes)

Cleanup

I removed a ton of dead weight from the database (empty tables and a surprisingly large number of columns created for abandoned features). Doing this also required removing references to said dead weight from the FSTDT code itself; despite never actually using it, quite a bit of code (especially related to user accounts) queried the database for it. Trying to do that now that it's no longer there in the database would throw a Kerblewy error.
_

New Banned-IPs table

As I mentioned earlier, the old Banned-IPs table has been replaced with a new one that supports wildcards and does away with the needless surrogate key, instead using the IP addresses themselves as the primary key.
_

New banned-strings list

The banned-strings list was also scrapped and replaced with something supporting wildcards. Since you're most likely not aware of the banned-strings table, what it does is that if you try to post a comment with a string in the table, the comment is simply ignored and not posted, and the quote page the comment was posted from acts like nothing happened. (It does not ban or monitor people who try to post comments with the banned strings; it just discards comments that contain them.) Nearly all of the banned strings are for automatically discarding obvious spam that resembles nothing a real user would ever actually post. Believe it or not, this pathetically simplistic technique actually catches almost all of the spam that somehow makes it past the "Check here" button.

Note that the names of both Le Frog Français and the Literal Motherfucker are in the banned strings list now, since saying them is like speaking Lord Voldemort's name. A few of other tics and words related to them are also banned. These are mostly Frogface's.
_

Stored procedures

In addition to the stored procedures I wrote working on the pubadmin table, I also wrote stored procedures to handle all of the other specific database-related tasks that the FSTDT code has to do, such as getting the comments for a quote and paginating them, logging in, submitting a quote, etc. (Aside from the occasionally mentioned exception, none of it is actually used in the FSTDT code right now, and the current codebase won't be updated to do so. This move was in preparation for the new upcoming rewrite I keep talking about. It will interact with the database exclusively using stored procedure insofar as possible. Unless you have good reason not to, I am ridiculously passionate about storing procedures in databases rather than using inline queries that reference the structure of the database, but getting into that will launch me way beyond the point.
_

Pubadmin database overhaul

I redesigned the entire pubadmin database table. Note that I didn't say I restructured the table, as that would imply I just shuffled things around a bit, maybe tided up a few loose ends, or made a lot of changes but very minor ones, etc. — no, I redesigned the pubadmin table. Doing this is where the protracted downtime came from and why the site had to be brought down.

I completely rethought not only how to store pubadmin data but also how to approach the task of handling the pubadmin system itself, both in database design and the site's code.

This post and "summary" is already long enough, so I won't go into detail, but I will say that this was probably the most needed and overdue change I made: the old pubadmin table was there and indeed stored pubadmin data, but pubadmin data was also strewn across three other unrelated tables besides the correct one. That data had to be moved in the process of redoing the pubadmin table and the columns that contained it deleted.

For those who don't know about how awful making structural changes to a large database is, this task necessitated creating a temporary table, copying the to-be-changed table's data into the temporary table, dropping (deleting) the to-be-changed table, creating a new table like it with the desired changes, copying the data from the temporary table to the new table, and finally dropping the temporary table. This obviously isn't technically changing the table but making a whole new one.

When your database is stored on a shared database server with 100 or so other databases, and the table you want to 'change' has over 100,000 rows and 15-20 columns, and its indexes have not be defragmented in a while, you're gonna be waiting a couple hours, and that's with the whole process being the only transactions taking place on the database and with you the only user accessing it. Having other users accessing the database would cause the transactions to be even slower to the point of timing out because they took too long.

And that is why we had to go down for maintenance and why we were down for so long.

[aside]If you can't change a table's structure, then it would seem a new table should be able to be created in "just" four steps (rename the original table, create the new table, transfer the data to the new table, and drop the renamed original table). You can do this when it doesn't violate any foreign-key constraints that create relations between data and connect their tables. Ignoring these constraints can compromise the referential integrity of your database and cause its relations to fall into such a state of disarray that they may as well not exist.[/aside]
_

Rewriting the pubadmin code-behind file

Since I completely redid the pubadmin table, I had to gut almost all of the FSTDT pubadmin code-behind file because it was coded to read and write data using the old table. I bet every query in the gutted code would have failed when attempted on the new pubadmin table. (Not having that happen is one of the major advantages of using stored procedures that tell the database what you want rather than telling it how to do it.)

The overwhelming majority of the gutted code was just that: literally gutted, i.e. removed from the codebase altogether. The remainder was kept in the code, but it was still gutted in the sense that it was commented out so it wouldn't be used. The parts I commented out and kept were ones that can be easily translated to VB and used in the FSTDT rewrite. By saving that code, I'll have a concrete launchpad from which to begin the process of writing the rewrite's pubadmin code.

[aside]Already having something tangible like that to work with from the get-go makes starting a project from scratch a lot easier for me, even though this is mostly just a psychological thing, and I often wind up not even using or replacing those parts. Am I weird for this?[/aside]

So quotes could be approved, the code-behind file for the PublicAdmin.aspx file had to be almost completely rewritten aside from the repeater controls that show similar quotes to check for dupes. They still worked because they were using stored procedures when I had my first glance at the database back in September of last year. The rest of the rewritten code also uses stored procedures and saved views, unlike the original code, which used a combination of inline SQL and a separate data access class that also used inline SQL itself.

PublicAdmin.aspx also had to be updated to remove controls that interacted with the not-reimplemented functionality, like issues and public admin being able to actually be used by the public again. (These issues will be fixed in the FSTDT rewrite at the latest.) Mods will probably immediately notice these changes on the page.

Mods, everything in the above paragraphs probably needs to be combed through for bugs because it was literally written in half a day. Report any issues you find, SVP.
_

Anyway, bear with me while I get any kinks worked out, and please let me know if you notice any. Post them in the Site / Off-Topic Thread, or if you think they can be exploited or pose a potential security issue, send a message to the email above instead or shoot me an IM.

Again, sorry for all the down time! I'll try not to do anything like this again.

34 comments

Confused?

So were we! You can find all of this, and more, on Fundies Say the Darndest Things!

To post a comment, you'll need to Sign in or Register. Making an account also allows you to claim credit for submitting quotes, and to vote on quotes and comments. You don't even need to give us your email address.