What is your database horror story?

I have three:

1.) Over 1 million lines of T-SQL that ran the entire application. VBScript backend that was a light wrapper around calls to the database. I was working on that system last year.

2.) ETL process done completely in SQL. When requirements changed instead of using existing columns they would create a new column using the same name but purposely misspelled. For example 'Member Responsibility' would be 'Member Responsability' and the original column would be ignored.

3.) Company used Entity Framework for database access. When somebody was running unit tests they ended up dropping a production database. Luckily it was a database with low volume of writes so backups were not very out of date.

Fixing corrupt data pages in MS SQL. Manually. One record at a time. Using obscure tools. It was a medical records database. Pure nightmare.

This probably isn't the type of war story you were expecting, but...

Many years ago I was the leader of an application maintenance team. The application had an n-tier client-server architecture. The database was DB2, running on an MVS system (with CICS).

We had some records that had incorrect information in them about the business process (basically what state they were in in the workflow). The team had to reset the business process info to the correct value. We wrote an UPDATE statement, and tested it in our non-production environment. Everything looked good.

In order to perform the clean up, we needed to create some new JCL to run the UPDATE statement in production. Because we had not yet learned better, the process involved re-keying the SQL into a new JCL file. Unfortunately, when the developer re-keyed the statement into the new JCL, he accidentally inserted a ';' before the WHERE clause.

The morning after the job was supposed to run, I asked the developer whether it had worked. "Yes," he told me. I asked him how many records had been updated in the end. He checked. Then he told me a number that was so big I knew something must have gone wrong. As you have probably already realized, we had updated every record in the table. We had changed the state of every workflow in our system.

We were very fortunate that we had a nightly backup AND I had a more senior manager who realized that we could repair the records using the data in the backup without doing a full restore. The org lost about a day of productivity, and we learned to never re-enter code that had been successfully tested. We parameterized our JCL after that.

Everything is a char.

All application logic handled by SSIS packages. No validation or sanitization of course.

Username: admin Password: admin This on a database with the data of millions. I got them to change it ...

Using MongoDb instead of a regular sql database. This ended up making the product extra shitty. Which in turn caused the eventual downfall of the company.

One table per user. As in everything related to a user in one single table. What is normalization?

So many more ...

Running out of autoincrement primary keys. Happened at two companies I was at. Can't say I was responsible for the first one. The second one caught me off guard, I didn't notice we were incrementing by 2's because all the id's were odd. If they were all even I would have picked up on it. Because of it we ran out in half the expected time so the plan we had got thrown out and we just winged it, in-place on prod machines. Luckily it was only for 'like's on a photo sharing platform, so we just black-holed the clicks, doing a local increment in the view for the person clicking until all the migrations completed many hours later.