• poo (unregistered)

    begin transaction?

  • Adam Robinson (unregistered)

    Jane could hear the murderer chasing her through the woods, his heavy boots crushing every fallen limb and smallish woodland creature in his path. Just as she was on the edge of escape, he rounded the corner and...

    Jane's day was about to get much, much worse.

    The end!

  • highphilosopher (unregistered)

    The murderer could hear his victim just in front of him, the blood rushing through his veins hot like the wind. As he rounded the corner he gasped in horror as the shotgun in her hands made that all to familiar sound.

    the murderer's day was about to get much, much worse.

    the end

    captcha suscipere -- to peer suspiciously.

  • Drew (unregistered)

    So you restore from back up and move on with your life? Or is there a bigger WTF at work here?

    Also, Time Servers aren't hard to set up.

  • Not so cool (unregistered)

    While foraging on the ground, the squirrel heard two tall, two-legged mammals running through the woods, making quite a racket. He scurried partway up a tree to ready his escape, but curiousity got the better of him.

    While it debated whether to flee around to the far side of the tree or not, he saw the larger mammal closest to him leap to one side, revealing the other two-legged mammal pointing a strange thing in his direction.

    The squirrel's day was about to get much, much worse...

  • baka0815 (unregistered)

    Never remove any data if there could be the slightest possibility you'll need it later.

  • Fenris (unregistered) in reply to Drew
    Drew:
    So you restore from back up and move on with your life? Or is there a bigger WTF at work here?

    Also, Time Servers aren't hard to set up.

    Forget Time Servers, how hard is to keep two dates in a record, business date, and real date. As business date is meaningless, always keep a real date around.

  • SQL cowboy (unregistered)

    rollback transaction

    and FIRST !

  • BSDGuy (unregistered) in reply to SQL cowboy

    date();

  • Spivonious (unregistered)

    The acorn was peacefully sitting on his tree branch, as he had done the past three months of his life. "What a wonderful day" he thought, as he welcomed the morning sun. Suddenly he felt the pressure of a squirrel's feet on his branch.

    The acorn's day was about to get much, much worse.

  • Frank (unregistered)

    Am I the only one who fears the statement "Restore from backup", knowing full well that the jackass who is in charge of the backups isn't necessarily always fully competent and most companies refuse to take the downtime / setup the environment to test the backup sets fully?

  • Alex (unregistered) in reply to poo
    poo:
    begin transaction?
    BEGIN TRANSACTION doesn't help when what you do is normal. If you purposefully delete data (Remember. The server thought all it deleted was outdated) either you don't do a transaction at all or you would have commited at the end: You *WANTED* to delete thingys.
  • Jay (unregistered)

    Putting aside the fact that such a big system must have had backups...

    Purging old data at that point might be one of the worst WTFs I've heard. The deleting of Transactional data should be managed by an admin app by someone responsible!

  • Nice try (unregistered) in reply to baka0815
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

  • (cs)

    The programmer settled back in his chair and surfed his daily sites, reading comics and checking news, then clicked on the link to thedailywtf.com, hoping to see a good CodeSOD or an entertaining Interview Tale. Just as he was sipping his coffee, the "Classic WTF" banner appeared on his monitor . . .

    The programmer's day was about to get much, much worse.

    The end!

  • highphilosopher (unregistered) in reply to Nice try
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    UPDATE [table] SET [deleted] = 1 WHERE [CreatedDate] < '20091110 00:00:00'

    Like that.

  • A Developer (unregistered) in reply to Nice try

    INSERT INTO TableName_BAK SELECT * FROM TABLENAME WHERE TRANS_DATE < (SYSDATE - INTERVAL '3' YEAR); DELETE FROM TableName WHERE TRANS_DATE < (SYSDATE - INTERVAL '3' YEAR); COMMIT;

  • Gramma (unregistered)

    I think I like the philosopher's method over the developer's.

  • Brompot (unregistered) in reply to A Developer

    Assuming each statement takes a significant amount of time using 'SYSDATE' twice may produce a set of records that are deleted, but not copied.

    So you put sysdate into a variable and then use that variable in your statements.

  • Anonymous (unregistered)

    Seem to be a lot of classics lately. Still, this was always one of my favourites. It perfectly represents that "oh...... shit" moment when you realise that a well thought out feature was not so well thought out after all. For fifteen years, no-one had thought twice about the "purge items older than 3 years" functionality. But one trivial operator error was all it took for that functionality to wipe out a store's entire transaction history. Suddenly, with the power of hindsight, the auto-purge mechanism is shown to be a gaping hole in the entire system's data integrity. Ouch. Facepalm just doesn't cut it.

  • The guy (unregistered) in reply to Nice try
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    In SQL:

    Don't delete anything automatically! Only delete based on a real user's explicit request. At the very minimum, ask the user if they really want to delete the data...

    Automatically deleting data without a prompt instead of archiving or something else non-destructive is TRWTF.

  • (cs)

    so... copy the one day's data somewhere else, restore the db from backup, import one day's data as yesterday. voila.

  • Anonymous (unregistered) in reply to poo
    poo:
    begin transaction?
    The transaction did begin. And then it ended. All normally. This was normal functionality, not an error condition or exceptional circumstance. The software was doing exactly what was programmed to. Transactions have no relevance to this story.

    Post #4

  • SCB (unregistered) in reply to Nice try
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    My turn...

    DELETE FROM [table] WHERE 1 = 0

  • Ike (unregistered) in reply to Jay

    Hasn't anyone heard of validating the input data? How about "That transaction date is three years in the future! Did you really want to use it?" YES NO FILE_NOT_FOUND

  • YourName* (unregistered) in reply to Brompot
    Brompot:
    Assuming each statement takes a significant amount of time using 'SYSDATE' twice may produce a set of records that are deleted, but not copied.

    So you put sysdate into a variable and then use that variable in your statements.

    iirc sysdate is read-consistent in an oracle transaction.

  • Swa (unregistered)

    if you want to remove stuff 3 years old from a database (i suspect for performance & size restrictions?), you archive it to another database first and then remove it from the primary database. it really aint all that hard.

    as for the date screw-up on programmer side? i've seen worse.

    the suggested solution of getdate() functions (or variations thereof) might also go haywire if for instance your motherboard fried, you swap it with another unit, but your battery was dead. you didn't bother to check the bios, you boot up with the years old default date & blam, you could be in trouble. now, with this particular app it wouldn't be an issue (since it only deletes "3y older than x"), but relying on getdate() isn't perfect either.

    tl;dr: you shouldn't base a delete transaction on a getdate() statement.

  • bored (unregistered) in reply to dpm
    dpm:
    The programmer settled back in his chair and surfed his daily sites, reading comics and checking news, then clicked on the link to thedailywtf.com, hoping to see a good CodeSOD or an entertaining Interview Tale. Just as he was sipping his coffee, the "Classic WTF" banner appeared on his monitor . . .

    The programmer's day was about to get much, much worse.

    The end!

    Win!

  • (cs) in reply to Swa
    Swa:
    tl;dr: you shouldn't base a delete transaction on a getdate() statement.

    No, you shouldn't. Even if the date was only one year in the future, not three, you'd still purge a year's worth of records. Worse, that one you wouldn't necessarily notice right away, making it that much harder to figure out WTF happened.

  • Patrick (unregistered) in reply to Drew
    Drew:
    So you restore from back up and move on with your life? Or is there a bigger WTF at work here?
    What backup? We don't need no steenkin backup! Not when we have a database server! Just run one of those undelete things.
  • (cs)

    Guys, we're in a bit of a jam. We accidentally posted in 2009 an article already published in 2006.

  • (cs) in reply to lolwtf
    lolwtf:
    Guys, we're in a bit of a jam. We accidentally posted in 2009 an article already published in 2006.
    (Applause)

    I wondered if anyone else would notice that.

  • (cs) in reply to dpm

    Did you notice that they deleted the comments from all previous WTFs too?

  • Ken B (unregistered) in reply to Nice try
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.
    Now express that in SQL
    REVOKE DELETE ON TransactionTable FROM *
  • Harrow (unregistered)

    The client's MIS manager squinted at me skeptically. "Are you sure? Nothing like this has ever happened before."

    "I'm quite sure. Your data is gone," I said. "It's not in the working set and it's not in the archived set. That kind of thing worries me. What if it's nowhere?"

    The manager dismissed my concerns with an airy wave of his paw. "Pish. Here at Initrode, we backup." He leaned back expansively. "I've been doing daily backups religiously for fifteen years."

    "Have you ever done a restore?"

    The manager sat forward. "A what?"

    The MIS manager's day was about to get much, much worse.

    -Harrow.

  • Patrick (unregistered) in reply to Gramma
    Gramma:
    I think I like the philosopher's method over the developer's.
    They both have their pros/cons.

    Philosopher's method is quick and doesn't require a second table as a mirror. However, if columns are added/changed, the entire dataset will have to be updated. The table's file will also have to be backed up in ever-increasing storage, and retrieval will have more and more data to sort through.

    Developer's method can use the "Archive" storage engine (if using MySQL) for the second table. After a certain number of records, another archive table can be created incrementally, so future backups don't have to worry about data files who's values don't change. However, archive tables are uneditable, so changes to the table structure will have to result in a new table. Also, restoring from the archive will have to be a copy-to-active-table > copy-remaining-to-new-archive > delete-old-archive operation.

    Automated data pruning is only good for information that was intended to be temporary in the first place, like session IDs. Doing it on a "hot" dataset based on random values(read: user input) is bad bad bad.

  • Patrick (unregistered)

    So, you can submit comments fine by pressing Enter on the captcha field, but clicking the submit button breaks it. Interesting.

  • (cs) in reply to SCB
    SCB:
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    My turn...

    DELETE FROM [table] WHERE 1 = 0

    Almost the same as what I was going to do... DELETE FROM [table] WHERE HellTemp <= 0
  • averageGuy (unregistered)

    How about:

    • Setting the default business date to today or tomorrow depending on a flag in the site configuration so the user is less likely to screw it up
    • Validating that the business date is reasonable (for example: today +/- 1 day)
  • Crabs (unregistered)

    I don't understand why they didn't use SYSDATE (or similar) to figure out when to purge the data. Why use the transaction date? Why would you purge data 3 years before a transaction date, which you can set to anything, instead of 3 years before the actual date (or at least the date on the server)?

    I won't worry about the fact that their purging data. There's tons of reasons that they could be doing that, even though it's not "best practice".

  • Anonymous (unregistered) in reply to Patrick
    Patrick:
    So, you can submit comments fine by pressing Enter on the captcha field, but clicking the submit button breaks it. Interesting.
    No, it may look that way but you'll find that there is no rhyme or reason to the errors, you just get them periodically. Sometimes it takes 4 or 5 submission attempts before it actually works and submits your post. The rest of the time you'll see the "an error has occured" page. It's been like this for a few months now.
  • Outtascope (unregistered) in reply to Patrick
    Patrick:
    So, you can submit comments fine by pressing Enter on the captcha field, but clicking the submit button breaks it. Interesting.

    I'm sure the enter button will be fixed in no time.

    Captcha genitus - Condition caused by the over "use" of internet porn.

    (Yep, enter seems fixed, 3rd try)

  • (cs) in reply to Nice try
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    delete from table_foo where field_bar is null and field_bar is not null;

  • Bob (unregistered)

    I'm with highphilosopher, never delete anything, ever. Storage is cheap.

    insert into archive (select * from mytable where deleted=1)

    delete from mytable where deleted=1

  • (cs) in reply to lolwtf
    lolwtf:
    Guys, we're in a bit of a jam. We accidentally posted in 2009 an article already published in 2006.

    It's 2009? Great, now I can finally return that toaster I bought back in 2006.

  • Zach Bora (unregistered) in reply to Fenris
    Fenris:
    Drew:
    So you restore from back up and move on with your life? Or is there a bigger WTF at work here?

    Also, Time Servers aren't hard to set up.

    Forget Time Servers, how hard is to keep two dates in a record, business date, and real date. As business date is meaningless, always keep a real date around.

    Except when your system date is wrong, then you`re fckup...

    I've had a similar problem myself. The user date system was dd/mm/yy instead of mm/dd/yy and when it saved it was saved in the future. We usually prefer the yyyy-mm-dd format, it can't go wrong... except if your clock is set to a.m. instead of am, but that's another story.

  • (cs) in reply to Bob

    The system date is only as good as the date on the server. Stranger things can screw up with that at exactly the wrong time.

  • Blue Collar (unregistered)
    [image]

    I bet ya it wasn't the getdate function... i bet it was the Cashier doing SQL Injection from that REGISTER!!

  • Woof (unregistered) in reply to Nice try
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    delete from sql_keyword_table where keyword like '%delete%';

  • JohnB (unregistered) in reply to SarahE
    SarahE:
    SCB:
    Nice try:
    baka0815:
    Never remove any data if there could be the slightest possibility you'll need it later.

    Now express that in SQL

    My turn...

    DELETE FROM [table] WHERE 1 = 0

    Almost the same as what I was going to do... DELETE FROM [table] WHERE HellTemp <= 0
    It could happen: H-ll, MI

    Still trying after multiple reports of "spam" (fixed by replacing an "e" with a "-")

Leave a comment on “Classic WTF: Don't Worry, We'll Fix It!”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article