So a few days ago I felt pretty h*ckin professional.

I'm an intern and my job was to get the last 2003 server off the racks (It's a government job, so it's a wonder we only have one 2003 server left). The problem being that the service running on that server cannot just be placed on a new OS. It's some custom engineering document server that was built in 2003 on a 1995 tech stack and it had been abandoned for so long that it was apparently lost to time with no hope of recovery.

"Please redesign the system. Use a modern tech stack. Have at it, she's your project, do as you wish."

Music to my ears.

First challenge is getting the data off the old server. It's a 1995 .mdb file, so the most recent version of Access that would be able to open it is 2010.

Option two: There's an "export" button that literally just vomits all 16,644 records into a tab-delimited text file. Since this option didn't require scavenging up an old version of Access, I wrote a Python script to just read the export file.

And something like 30% of the records were invalid. Why? Well, one of the fields allowed for newline characters. This was an issue because records were separated by newline. So any record with a field containing newline became invalid.

Although, this did not stop me. Not even close. I figured it out and fixed it in about 10 minutes. All records read into the program without issue.

Next for designing the database. My stack is MySQL and NodeJS, which my supervisors approved of. There was a lot of data that looked like it would fit into an integer, but one or two odd records would have something like "1050b" which mean that just a few items prevented me from having as slick of a database design as I wanted. I designed the tables, about 18 columns per record, mostly varchar(64).

Next challenge was putting the exported data into the database. At first I thought of doing it record by record from my python script. Connect to the MySQL server and just iterate over all the data I had. But what I ended up actually doing was generating a .sql file and running that on the server. This took a few tries thanks to a lot of inconsistencies in the data, but eventually, I got all 16k records in the new database and I had never been so happy.

The next two hours were very productive, designing a front end which was very clean. I had just enough time to design a rough prototype that works totally off ajax requests. I want to keep it that way so that other services can contact this data, as it may be useful to have an engineering data API.

Anyways, that was my win story of the week. I was handed a challenge; an old, decaying server full of important data, and despite the hitches one might expect from archaic data, I was able to rescue every byte. I will probably be presenting my prototype to the higher ups in Engineering sometime this week.

Happy Algo!

I just had a client complaining on the phone that she read my database design documents and that they are all wrong and need to be done again. Because things like varchar and int are confusing. And nobody understands what they mean. She asked around and nobody understood it.

Ooh, and I should place the customer name in more then one table because it would be handy to have in several places.

Spend a hour on the phone trying to explain that these documents are not intended for her. They are not for her to understand.

I make these documents to build a stable product and in case something bad happens to me its easier to pick up for another developer.

Long story short.. I'm currently making a document that explains the database design... Getting paid for it..... But fucking hell. Somebody save me.

I’m surrounded by idiots.

I’m continually reminded of that fact, but today I found something that really drives that point home.

Gather ‘round, everybody, it’s story time!

While working on a slow query ticket, I perused the code, finding several causes, and decided to run git blame on the files to see what dummy authored the mental diarrhea currently befouling my screen. As it turns out, the entire feature was written by mister legendary Apple golden boy “Finder’s Keeper” dev himself.

To give you the full scope of this mess, let me start at the frontend and work my way backward.

He wrote a javascript method that tracks whatever row was/is under the mouse in a table and dynamically removes/adds a “.row_selected” class on it. At least the js uses events (jQuery…) instead of a `setTimeout()` so it could be worse. But still, has he never heard of :hover? The function literally does nothing else, and the `selectedRow` var he stores the element reference in isn’t used elsewhere.

This function allows the user to better see the rows in the API Calls table, for which there is a also search feature — the very thing I’m tasked with fixing.

It’s worth noting that above the search feature are two inputs for a date range, with some helpful links like “last week” and “last month” … and “All”. It’s also worth noting that this table is for displaying search results of all the API requests and their responses for a given merchant… this table is enormous.

This search field for this table queries the backend on every character the user types. There’s no debouncing, no submit event, etc., so it triggers on every keystroke. The actual request runs through a layer of abstraction to parse out and log the user-entered date range, figure out where the request came from, and to map out some column names or add additional ones. It also does some hard to follow (and amazingly not injectable) orm condition building. It’s a mess of functional ugly.

The important columns in the table this query ultimately searches are not indexed, despite it only looking for “create_order” records — the largest of twenty-some types in the table. It also uses partial text matching (again: on. every. single. keystroke.) across two varchar(255)s that only ever hold <16 chars — and of which users only ever care about one at a time. After all of this, it filters the results based on some uncommented regexes, and worst of all: instead of fetching only one page’s worth of results like you’d expect, it fetches all of them at once and then discards what isn’t included by the paginator. So not only is this a guaranteed full table scan with partial text matching for every query (over millions to hundreds of millions of records), it’s that same full table scan for every single keystroke while the user types, and all but 25 records (user-selectable) get discarded — and then requeried when the user looks at the next page of results.

What the bloody fucking hell? I’d swear this idiot is an intern, but his code does (amazingly) actually work.

No wonder this search field nearly crashed one of the servers when someone actually tried using it.

Asdfajsdfk.

I'm trying to sign up for insurance benefits at work.

Step 1: Trying to find the website link -- it's non-existent. I don't know where I found it, but I saved it in keepassxc so I wouldn't have to search again. Time wasted: 30 minutes.

Step 2: Trying to log in. Ostensibly, this uses my work account. It does not. Time wasted: 10 minutes.

Step 3: Creating an account. Username and Password requirements are stupid, and the page doesn't show all of them. The username must be /[A-Za-z0-9]{8,60}/. The maximum password length is VARCHAR(20), and must include upper/lower case, number, special symbol, etc. and cannot include "password", repeated charcters, your username, etc. There is also a (required!) hint with /[A-Za-z0-9 ]{8,60}/ validation. Want to type a sentence? better not use any punctuation!

I find it hilarious that both my username and password hint can be three times longer than my actual password -- and can contain the password. Such brilliant security.

My typical username is less than 8 characters. All of my typical password formats are >25 characters. Trying to figure out memorable credentials and figuring out the hidden complexity/validation requirements for all of these and the hint... Time wasted: 30 minutes.

Step 4: Post-login. The website, post-login, does not work in firefox. I assumed it was one of my many ad/tracker/header/etc. blockers, and systematically disabled every one of them. After enabling ad and tracker networks, more and more of the site loaded, but it always failed. After disabling bloody everything, the site still refused to work. Why? It was fetching deeply-nested markup, plus styling and javascript, encoded in xml, via api. And that xml wasn't valid xml (missing root element). The failure wasn't due to blocking a vitally-important ad or tracker (as apparently they're all vital and the site chain-loads them off one another before loading content), it's due to shoddy development and lack of testing. Matches the rest of the site perfectly. Anyway, I eventually managed to get the site to load in Safari, of all browsers, on a different computer. Time wasted: 40 minutes.

Step 5: Contact info. After getting the site to work, I clicked the [Enroll] button. "Please allow about 10 minutes to enroll," it says. I'm up to an hour and 50 minutes by now. The first thing it asks for is contact info, such as email, phone, address, etc. It gives me a warning next to phone, saying I'm not set up for notifications yet. I think that's great. I select "change" next to the email, and try to give it my work email. There are two "preferred" radio buttons, one next to "Work email," one next to "Personal email" -- but there is only one textbox. Fine, I select the "Work" preferred button, sign up for a faux-personal tutanota email for work, and type it in. The site complains that I selected "Work" but only entered a personal email. Seriously serious. Out of curiosity, I select the "change" next to the phone number, and see that it gives me four options (home, work, cell, personal?), but only one set of inputs -- next to personal. Yep. That's amazing. Time spent: 10 minutes.

Step 6: Ranting. I started going through the benefits, realized it would take an hour+ to add dependents, research the various options, pick which benefits I want, etc. I'm already up to two hours by now, so instead I decided to stop and rant about how ridiculous this entire thing is. While typing this up, the site (unsurprisingly) automatically logged me out. Fine, I'll just log in again... and get an error saying my credentials are invalid. Okay... I very carefully type them in again. error: invalid credentials. sajfkasdjf.

Step 7 is going to be: Try to figure out how to log in again. Ugh.

"Please allow about 10 minutes" it said. Where's that facepalm emoji?

But like, seriously. How does someone even build a website THIS bad?

Poorly designed databases be like

VARCHAR VARCHAR VARCHAR VARCHAR VARCHAR

ARGH. I wrote a long rant containing a bunch of gems from the codebase at @work, and lost it.

I'll summarize the few I remember.

First, the cliche:
if (x == true) { return true; } else { return false; };
Seriously written (more than once) by the "legendary" devs themselves.

Then, lots of typos in constants (and methods, and comments, and ...) like:
SMD_AGENT_SHCEDULE_XYZ = '5-year-old-typo'

and gems like:

def hot_garbage
magic = [nil, '']
magic = [0, nil] if something_something
success = other_method_that_returns_nothing(magic)
if success == true
return true # signal success
end
end

^ That one is from our glorious self-proclaimed leader / "engineering director" / the junior dev thundercunt on a power trip. Good stuff.

Next up are a few of my personal favorites:

Report.run_every 4.hours # Every 6 hours
Daemon.run_at_hour 6 # Daily at 8am

LANG_ENGLISH = :en
LANG_SPANISH = :sp # because fuck standards, right?

And for design decisions...

The code was supposed to support multiple currencies, but just disregards them and sets a hardcoded 'usd' instead -- and the system stores that string on literally hundreds of millions of records, often multiple times too (e.g. for payment, display fees, etc). and! AND! IT'S ALWAYS A FUCKING VARCHAR(255)! So a single payment record uses 768 bytes to store 'usd' 'usd' 'usd'

I'd mention the design decisions that led to the 35 second minimum pay API response time (often 55 sec), but i don't remember the details well enough.

Also:
The senior devs can get pretty much anything through code review. So can the dev accountants. and ... well, pretty much everyone else. Seriously, i have absolutely no idea how all of this shit managed to get published.

But speaking of code reviews: Some security holes are allowed through because (and i quote) "they already exist elsewhere in the codebase." You can't make this up.

Oh, and another!
In a feature that merges two user objects and all their data, there's a method to generate a unique ID. It concatenates 12 random numbers (one at a time, ofc) then checks the database to see if that id already exists. It tries this 20 times, and uses the first unique one... or falls through and uses its last attempt. This ofc leads to collisions, and those collisions are messy and require a db rollback to fix. gg. This was written by the "legendary" dev himself, replete with his signature single-letter variable names. I brought it up and he laughed it off, saying the collisions have been rare enough it doesn't really matter so he won't fix it.

Yep, it's garbage all the way down.

"please use a secure password*"

* But don't make it too secure, 20 Charakters is enough.

Why would you fucking do this? The only reason I can think about is a scenario like this:
"How do we store the passwords in the database?"
"Just like anything else?"
"So I create a VARCHAR(20)?"
"Yeah why not? It's good enough for a name, and you shouldn't use your or anyone else's name as a password, so it should be perfect"

Working on a database priorly designed and maintained by some private agency.

The fuck I'm dealing with!

Boolean values stored as 'TRUE'/'FALSE'. It's varchar, my dudes.

There are no FK relations. Just the values of IDs in a column.

There are no indexes, all on just the PKs, nothing else. Nothing.

Null, what's that? I'm dealing with 'N/A', my dudes.

Unique key, what's that? The table which stores users has all the fields nullable. Email is not unique ( even though that's the required behaviour).

ALL the numeric values are stored as varchar. Varchar, my dudes. Varchar. '1', '1.1'

And finally, the good ole, 1 table to rule them all. Normalisation, fuck that.

And what's the root cause of all this? My PM used to hand them Excel sheets she maintains on her local system. FTW. I don't have a enough explanations.

Where I work, in our database, we use 3 to indicate true and 7 to indicate false and 0 is true but null is false

In another table, we use 'P' to indicate true and 'I' to indicate false and 'Y' is also false and null is false

And the most used table, we also use 'Y' to indicate yes and 'N' to indicate no, but null is also Yes.

We also store integers as varchar in a live table, but stays an integer in all the other tables. I hope I'm not there when the number of digits exceeds the varchar limit.

These are all live and used in production all created by my boss, the head of IT.

I'm moving some old data into a new database.

It contains some dates that *should* be in ISO 8601...

This is some of the trash that I found:
01/01/70
2010-11-05T08:06:48T08:06:03.7
2007-09-13T

Moreover, it has a column which *should* contains numbers, instead it has been defined using varchar, so it contains also some wonderful 'NaN' values.

I really would like to beat the person who set up all this stuff without some basic validation policies.

My co-workers hate it when I ask this question on a technical interview, but my common one is "what is the difference between a varchar(max) and varchar(8000) when they are both storing 8000 characters"

Answer, you cannot index a varchar(max). A varchar(max) and varchar(8000) both store the data in the table but a max will go to blob storage if it is greater than 8000.

No one ever knows the answer but I like to ask it to see how people think. Then I tell them that no one ever gets that right and it isn't a big deal that they don't know it, as I give them the answer.

Once a month I get a crash for the stupid PHP site I am in charge of because the guy who made the database made the primary key of a table a varchar. It is a number 99.9% of the time but the dumbass customer always enters one entry with a string cuz the dumbass db Dev let them. I emailed the dumbass Dev telling him he is stupid and he said it is part of their philosophy. I told him I still think he is stupid so he emailed my boss.

I emailed him again telling him he is stupid.

Password policy for a big water company site in Spain.

Translation: Between 6 and 10 characters (only letters and numbers, no spaces)

In guess they have a VARCHAR(10) password field in their db?!?

Oh boy. Gotta love having a team member (For a School project) be in charge of the Database functionality/design, who has almost no communication skills and basically no clue about how to store data in a data base.
Im talking dates stored as varchar(5), column sizes being way way to small, overall table design being rather terrible, no primary/foreign keys, pretty much... Actually no, everything was being stored as a varchar.
Not only that, but there was a hell lot of data that needed to be stored that wasn't even accounted for in the DB design. He made some code that could be used by our team members for queries, creating tables, inserting data (etc), almost 2,000 lines of it.... And basically nothing was fucking documented at all. I'm talking comments like "Insert data into cities table" and nothing else. More complicated functions had as much, or less documentation. Complete mess.

Worst technology I've ever worked with?

Microsoft-FUCKING-Access

The error riddled, varchar frenzy, disgusting ui, os and architecture dependent pieces of shit, powered by the cherry on top: fucking VBA, that are applications developed with this monstrosity have kept me awake trying to understand why on earth would anyone that is not dying of cancer already would use such thing to try to build anything.

I had to deal with load of Access applications when I first started at my current company. Whats left now are mainly legacy systems, I killed them one by one and whatever's left will suffer the same punishment.

If you develop in Access you're my enemy and I will destroy you.

declare @username varchar(255)
if @username is null
set @username = suser_name()

.........

WHAT DID YOU EXPECT @USERNAME TO BE RIGHT AFTER YOU DECLARED IT???

I'm debugging someone else's 10 year old legacy .asp web application (shoot me now), and I'm trying to find the most recent records in a database table.

Why is the most recent record from September of last year?

Oh.

Because they're storing the datetime value as varchar (40).

Good thing they were smart enough not to waste disk space by using varchar (255)!

Let's say you have a MySQL database table for jobs. Each job has 1 associated ticket. You want to keep track if the ticket is closed or not. Every sane person creates jobs table, tickets table, keeps bool value for ticket state and relationship between them.

But because our database is designed by a half braindead amoeba, we have one table only, so each job has to be updated individually with a new ticket number and its state. Beacuse it sooo much faster to update (daily!) 13k jobs than just 100 tickets.
As a bonus - if the ticked is closed, the column "ticket_closed" is "No", if it's still open the value is "FALSE". Yes, both as varchar/strings.

Somebody created a new database field of type varchar(10) and then filled it with the string 'NULL' for every row. Now, everybody is having to figure out for themselves that these aren't actual NULL values when running scripts.

When i started my work I encoubtered this db(one of 4): more than 20 tables, some with 200 columns literally... EVERYTHING is a varchar 😓.

I'm slowly designing some normalized tables with real fk on new features and projects and people are like: how the fuck did you implement this feature so fast? the other guy spent 3 months designing this form (and I'm just speechless):

The form was some sort of crazyness shit passing input names as "name-of-property" and a file only to check if(name="string") then store a number value to an array and save it as a "number" (actually varchar) on the db. literally more than 50 if statements to do this.

Everything on a single table that made no sense at all.

Just wtf... At least my boss let me start if from scratch cause he we were always having panick attacks every time he needed to do something with it. 😂😂

I'm seriously working with a system that saves the password AND the confirm password In the database... varchar field, copied info, no wonders it takes half an hour to make query.

Wrote a SQL stored procedure today to do a complicated query. Decided to make it so that I could pass multiple records into the stored procedure in comma separated format, but the damned thing would only pull the first record. The query worked fine outside the procedure but it wouldn't pull anything more than the first record. After deleting and recreating and spending 30 minutes trying to figure out what was wrong I realized I changed the length of the wrong parameter. Set the correct one to varchar max and it was all good. 30 minutes of my life I will never get back.🐘💨

Yes its completely necessary to have a spring server with a mysql database with docker containers all over your ass for 3 fucking endpoints and a (url, varchar, varchar) schema. Fuck you. How the fuck do i run all this shit and how do you expect me to create a frontend for something that has no documented endpoints?? Fuck you.

In other news, im now a senior.

Ladies and gents, it was a 🍺 day, today.

I spent more hours than I care to say today tracking down an issue in our web workflow, even looping in our only web dev to help me debug it from his side. There ended up being multiple bugs found, but the most annoying of them was that the json data being pulled back was truncated because a certain someone, in their migration script, set their varchar variable to a size of 1000 and then proceeded to store a json string that was 2800+ characters in length.

C'mon man!

I got nothing productive done today. Hate, hate, hate days like this!

Beer me.

At work I inherited some databases, were most of the tablecolumns are all varchar. No ID's, and everything is in one table. No relation tables no reference data. Because "we don't trust the users who fill the tables to understand relationships". And.. wtf.

Sometimes lack of confidence in one area reveals oversight cockyness in stronger areas:

Set up a simple login system from Unity engine to php to mysql db, using android device ID as the login id. Set up database column to accept 32 length varchar for MD5 hashed strings, as I knew the method I was getting the android device ID was automatically being hashed that way and more or less was what I wanted anyway.

Spend 2 days wondering why it would insert the logins with 0 issue, but could never retrieve them. Due to lack of web development and PHP skills, I assumed I was screwing up the handling of mysqli_num_rows() (to check whether I was inserting or selecting in the query) or simply screwing up my SQL queries.

Rewrite the code a few times, even went back to a method I had used in the past.

Today it dawned on me that my testing machines deviceID had been getting trimmed to the 32 character limit. Turns out I didn't account for my workstations device ID to be automatically hashed like the android device id is.

For 2 days I was obtaining and sending a 40 character string to a 32 character limit varchar and blaming my lack of PHP skills........

Back to my niche I go!

I can't convince my team that a good database model promotes a good API design and a good UI/UX experience.

Instead, I have to work with a ridiculous table setup.

Imagine, if you will, a table (table B) that references another table (table A) via a foreign key. The FK is a string in both tables. And table A only has one column, which is labeled as "name".

The schema i have to look at it kind of like this:

Table parent
Name varchar(10) primary key

Table child
Name varchar (10),
parent_name varchar(10),
Foreign key (parent_name) references parent (name)

Sorry if the syntax is wrong, a little frustrated having to look at it...

Am I crazy to want to change this table design? Am I missing something? I feel like I'm taking crazy pills, because this is just scratching the surface of the problems I have to deal with.

Here's something that should be an unspoken rule in the dev space:

DON'T MAKE A LESSON OF YOUR TEAMMATE'S MISTAKES (OR WHAT YOU CONSIDER A MISTAKE).

More context:
I defined a column as `mediumText` in a Laravel app. What the asshole team lead did after seeing that in my PR, is taking that snippet and blast that out in the company dev slack channel on full display, saying - "Guys we shouldn't take more space in the database than we need to. If the value for the column would be < 255 characters please take VARCHAR not MEDIUMTEXT".

I mean, what an asshole !!! You could've told me that privately and I'd have happily changed it. The reason I did so, is to be safeguard myself from the future, in case the value length increases or decreases. Last thing I want to see in the logs is "Column size is too short for column {COLUMN}, value truncated".

But nah nah, let's take a screenshot of that snippet and `teach` everyone else what should and shouldn't be done according to your `standards`.

Fucking piece of shit team lead.

How do you pronounce VARCHAR?

var-char or
var-kar

I just don't get people designing database tables using VARCHAR/VARCHAR2 for columns that have numeric values in them, then use them as row identifiers.

I simply don't.

Oh this defect is great!

Inherited an app that allows people to type in the name of their beneficiary in a form.

The database column is varchar(50) and the form has no size limit...beautiful

Fuck this shit! We had bug on website when tinymce was showing broken tables and could not save them correctly. So, the first thing you think about is tinymce is fucked up and you have to either upgrade, downgrade or fix it fucking yourself. Well, I spend more than hour tryingto figure this out. Then I found out that some fucker set column length in DB where data are stored to varchar 800!!! WTF, are you fucking serious?

I'm currently working on a new (for me) legacy database that store percentile numbers in a varchar column . Seriously who the fuck had the that brilliant idea!!!

When every related field has a god damn different way of working with the data on hand..

For example:

`tht_date` ("Y-m-d", Date) - expiration date on the product, hence, there can be multiple of the same products with a different THT

`tht_alert` ("-2 months", varchar, DateTime modify mutation string) - sending an alert when this interval is hit, and being the activator of the tht_date field (unless value is "none")

`tht_minimum` ("28", integer, quantity of days before tht_date) - to lock them from being sent out/collected.

...

How would you expect this ×not× to become a friggin' spaghetti when trying to resolve the best row ID?

These values are in the wrong spot in the first place, then they also act entirely different in relation to eachother..

I hate the person that set this up, for doing this. When is the madness going to stop...

FFS!!

So yesterday at a client location, our support guy called me and said this thing is trimming the characters whenever I save it. It was a ckeditor in our application, so basic troubleshooting was to check the system configuration for that page and the ckeditor configuration.
Checked the system configuration, ckeditor configuration, found nothing.
Out of curiosity, checked the schema for the table in which the data is stored, so one of the idiots took the backup of original table and appended it with the date time on which it was backed up. And created a new table with field data type of varchar with a 255 limit.
This was in UAT server as well as Production server. Changed the field type to text again in UAT. Asked to team to get the same thing done on Production server as well.

I wrote a stored procedure and declared the input as varchar instead of varchar(100). everything seemed to be working. later on we noticed that the procedure only saved the first character of the input (a user form). unfortunately we found out first when the monthly form reports where issued. a whole month of incomplete forms from our users. the client wasn't happy.

We complain a lot about others. I'll toss out my own dork up from today.

Customer: "Can we add more than X widgets? There seems to be a limit."

Me: "Wut? Lol no I didn't limit.... wait a second."

SQL... field type... varchar

-hand to forehead-

Wow spent another 20-25 mins mapping out the stack calls to see why varchar was trying to be converted to Nchar which isn't supported and found in my mapper class one fucking method had rs.getNString() which I swear I never touched the mapper class since I made it and was working just fine!!!! I swear IntelliJ is straight up fucking with me

Level of fuckity fuck mood.

After changing dozens of build plans in Bamboo, the build system of poo...

How to verify that nothing has gone wrong?

Poking the database, you'll be surprised that Bamboo stores the buildplan definition as XML.

Another surprise: Some of the keys / values have typos.

Yeah. You read that right. There are typos inside the XML...

Now together with Postgres, we can use XPATH and have some fun.

UNNEST(COALESCE(XPATH('/configuration/buildTasks/taskDefinition[userDescription[contains(text(),"Bleep")]]', build_definition.xml_definition_data::xml)::varchar[], ARRAY['']))

Lovely wrapping via coalesce for some null safety.

Now we get da task definitions for fields having user description text containing bleep.

Wrapping it in two REGEXP_REPLACE to strip out stupid identifiers....

REGEXP_REPLACE(REGEXP_REPLACE(...., '<id>\d+</id>', ''), '<oid>\d+</oid>', ''))

Then wrap that in MD5.

Boom. Lots of MD5 sums to help you identify if the configs are identical for a task or not.

Now wrapping that in another select to group by the MD5 and filter out the non identical ones.

I hate it how sometimes one has to seemingly do a full 2 hour dance for something as stupid as validation.

I'm pretty glad though for XML and XPATH.

Cause otherwise that would have been a whole can of worms I don't wanna think about....

When that external api stores firstnames as varchar 13 m)

I thought I had to VARCHAR in non-SQLite RDBMS. I was wrong. There is TEXT.

1. PostGres seems to work better with TEXT.
2. There is an ORM that forces VARCHAR (255) by default.
3. But you can go over the limit without throwing errors.