• Gruntled Postal Worker (unregistered)

    Of course, depending on a bug is not a good idea, but...

    "As an interesting side note, there are about 1.1 million rows in the PRODUCTS table. 700,000 of these products have not been sold in over a year. In other words, more than 50% of the data in the PRODUCTS table is obsolete, but can't be purged! Sheer genius!"

    Seriously, why would you want to delete product records? What if a customer calls and says "I bought product A12345_Q 18 months ago, and it turns out to have a life threatening defect" - "I'm sorry sir, we have no product with id A12345_Q in our database. Are you making this up?"

    I don't know the OP's business case, and deleting the records might be entirely valid is this case, but usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.

  • norgthefat (unregistered)

    As a the leader of a Zombie horde, I take offense at being compared with a Plasmoid!

  • Ben (unregistered)

    No one could explain to the VP that if something is sensitive they could move it to a readonly archive rather than delete it? And that you can have permissions in a DBMS to prevent deletions rather than a buggy trigger?

  • norgthefat (unregistered) in reply to Gruntled Postal Worker
    Gruntled Postal Worker:
    Of course, depending on a bug is not a good idea, but...
    "As an interesting side note, there are about 1.1 million rows in the PRODUCTS table. 700,000 of these products have not been sold in over a year. In other words, more than 50% of the data in the PRODUCTS table is obsolete, but can't be purged! Sheer genius!"

    Seriously, why would you want to delete product records? What if a customer calls and says "I bought product A12345_Q 18 months ago, and it turns out to have a life threatening defect" - "I'm sorry sir, we have no product with id A12345_Q in our database. Are you making this up?"

    I don't know the OP's business case, and deleting the records might be entirely valid is this case, but usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.

    Well, many places have 'online' table that gets backed up daily (or more), and an 'archive table' (or 'obsolete products') table (that never gets updated) that you don't want to be included in the daily backups....all a part of data warehousing

  • (cs) in reply to Gruntled Postal Worker
    Gruntled Postal Worker:
    Of course, depending on a bug is not a good idea, but...
    "As an interesting side note, there are about 1.1 million rows in the PRODUCTS table. 700,000 of these products have not been sold in over a year. In other words, more than 50% of the data in the PRODUCTS table is obsolete, but can't be purged! Sheer genius!"

    Seriously, why would you want to delete product records? What if a customer calls and says "I bought product A12345_Q 18 months ago, and it turns out to have a life threatening defect" - "I'm sorry sir, we have no product with id A12345_Q in our database. Are you making this up?"

    I don't know the OP's business case, and deleting the records might be entirely valid is this case, but usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.

    Or, you know, archive the obsolete data in a separate table.

  • (cs) in reply to norgthefat
    norgthefat:
    Well, many places have 'online' table that gets backed up daily (or more), and an 'archive table' (or 'obsolete products') table (that never gets updated) that you don't want to be included in the daily backups....all a part of data warehousing
    If the VP of Security is afraid of "rogue processes" deleting the products table, then I would venture to guess this is not one of those places.
  • Jill (unregistered)

    9 months? Was the VP having a baby or deep undercover with the zombie plasmoids?

  • (cs) in reply to Jill
    Jill:
    9 months? Was the VP having a baby or deep undercover with the zombie plasmoids?
    The "mm" clearly indicates that nine days passed between comments, regardless of which side of the pond you're on.
  • CyberShadow (unregistered)

    TRWTF is using a live database for unit testing. What if a bug in the test caused it to actually delete all rows from the table?

  • ER (unregistered)

    shouldn't the unit test fail?

  • grrr (unregistered) in reply to CyberShadow
    CyberShadow:
    TRWTF is using a live database for unit testing. What if a bug in the test caused it to actually delete all rows from the table?

    Have you thought that he might be working on a dev database that is likely to resemble a clone of the live database...

    quibus:- quine-rebus. A rebus that generates itself as the answer.

  • Chubber (unregistered)

    I thought that TRWTF was redacting the names in the comments. "<name redacted>" used to work for a client of mine in the past. Due to some unspecified, but obviously very serious, transgressions, we actually had to go over all of the source code in our projects and remove any mention of his name, initials, etc. from the comments. Then the spec documents, etc. It was very Orwellian.

    CAPTCHA: secundum - That lacky who always seconds a motion.

  • Bill's Kid (unregistered) in reply to dpm
    dpm:
    Jill:
    9 months? Was the VP having a baby or deep undercover with the zombie plasmoids?
    The "mm" clearly indicates that nine days passed between comments, regardless of which side of the pond you're on.

    No. The "01" and "10" clearly indicated that 9 days had passed. The "mm" indicated that the month had been redacted.

  • Ben (unregistered) in reply to Gruntled Postal Worker
    Gruntled Postal Worker:
    I don't know the OP's business case, and deleting the records might be entirely valid is this case, but usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.

    You run into a worse problem with updates.

    One small company wanted me to design a schema that would (among other things) store invoices, so my first proposal had the invoice storing a copy of each product's basic info at the time of purchase. They could not comprehend that if the invoice simply stored a product ID, the price might change and invalidate all the invoice records. I offered a more normalized solution with a history of products, but they claimed I was introducing redundant tables. I also offered a no update policy: create a new product ID any time anything changed, but they weren't happy with that.

    I think I've since figured out why they resisted it. Storing historical data is inherently complicated, and that complexity is clearly portrayed by the schema. The schema, of course, represents the actual functioning of the business. When managers see a complex schema and can't understand it, it causes cognitive dissonance: they must know how the business operates, so how could it be that they don't understand a formal representation of that?

  • Bill C. (unregistered) in reply to toth

    "Or, you know, archive the obsolete data in a separate table."

    Then you have to write a completely separate set of queries for anything about them, foreign keys to it are merely convention, and you have to write a bunch of code to move it over.

    The data probably isn't hurting anything where it is, and if you're running out of space, moving to another table will only help if it's in another tablespace.

  • cbrink (unregistered)
    700,000 of these products have not been sold in over a year. In other words, more than 50% of the data in the PRODUCTS table is obsolete, but can't be purged! Sheer genius!"

    Sorry - There is a difference between marking a product inactive and deleting it. Purging historical data is almost always the wrong thing.

  • BGerrissen (unregistered)

    Unit test SHOULD fail. Might as well omit unit testing entirely if you hack to make them work...

  • nah (unregistered) in reply to BGerrissen
    BGerrissen:
    Unit test SHOULD fail. Might as well omit unit testing entirely if you hack to make them work...
    1. Insert test data.
    2. Perform unit test.
    3. Delete test data.
  • (cs)

    Why is the VP so involved in the low-level implementation details here? So he/she insists that the trigger prevents deletes as it used to. That seems easy enough, and you don't even have to reintroduce a bug to do it. So why is the VP making mandates on how the trigger enforces this rule?

    Are we sure this wasn't a case of some crybaby original developer/admin who didn't like his poorly-written trigger being corrected, so he went and tattled to the VP of security?

  • Kef Schecter (unregistered) in reply to Bill's Kid
    Bill's Kid:
    dpm:
    Jill:
    9 months? Was the VP having a baby or deep undercover with the zombie plasmoids?
    The "mm" clearly indicates that nine days passed between comments, regardless of which side of the pond you're on.

    No. The "01" and "10" clearly indicated that 9 days had passed. The "mm" indicated that the month had been redacted.

    Yes, meaning that if the month was redacted, clearly the two digits that followed were the day. Jill thought the date was in dd/mm/yyyy format because she presumably didn't notice the "mm" in the redacted date.

  • (cs)

    Running "Unit Tests" on a production database?

  • norgthefat (unregistered) in reply to JamesQMurphy
    JamesQMurphy:
    norgthefat:
    Well, many places have 'online' table that gets backed up daily (or more), and an 'archive table' (or 'obsolete products') table (that never gets updated) that you don't want to be included in the daily backups....all a part of data warehousing
    If the VP of Security is afraid of "rogue processes" deleting the products table, then I would venture to guess this is not one of those places.

    Right...I sometimes make the assumption that these places are using 'best practices'...how silly of me.

  • Avery Orwellian (unregistered) in reply to Chubber
    Chubber:
    I thought that TRWTF was redacting the names in the comments. "<name redacted>" used to work for a client of mine in the past. Due to some unspecified, but obviously very serious, transgressions, we actually had to go over all of the source code in our projects and remove any mention of his name, initials, etc. from the comments. Then the spec documents, etc. It was very Orwellian.
    And I'll tell whoever will listen, it was unfair to blame and fire me!
  • (cs) in reply to Bill's Kid
    Bill's Kid:
    dpm:
    Jill:
    9 months? Was the VP having a baby or deep undercover with the zombie plasmoids?
    The "mm" clearly indicates that nine days passed between comments, regardless of which side of the pond you're on.

    No. The "01" and "10" clearly indicated that 9 days had passed. The "mm" indicated that the month had been redacted.

    If the month was redacted, how can anyone be sure how many months passed? Perhaps this was 1 month and 9 days? 2 months and 9 days?

    It seems the year has been redacted too.

  • Someone who can't be bothered to login from work (unregistered) in reply to Chubber
    Chubber:
    I thought that TRWTF was redacting the names in the comments. "<name redacted>" used to work for a client of mine in the past. Due to some unspecified, but obviously very serious, transgressions, we actually had to go over all of the source code in our projects and remove any mention of his name, initials, etc. from the comments. Then the spec documents, etc. It was very Orwellian.

    CAPTCHA: secundum - That lacky who always seconds a motion.

    I belong to a group which once changed the members' forum so you couldn't mention the name of one of an ex-member.

  • F (unregistered) in reply to CyberShadow
    CyberShadow:
    TRWTF is using a live database for unit testing. What if a bug in the test caused it to actually delete all rows from the table?
    Just restore from backup. After all, there's bound to be one ... somewhere ...
  • Someone who can't be bothered to login from work (unregistered) in reply to boog
    boog:
    Why is the VP so involved in the low-level implementation details here? So he/she insists that the trigger prevents deletes as it used to. That seems easy enough, and you don't even have to reintroduce a bug to do it. So why is the VP making mandates on how the trigger enforces this rule?

    Are we sure this wasn't a case of some crybaby original developer/admin who didn't like his poorly-written trigger being corrected, so he went and tattled to the VP of security?

    My guess would be a small company with people who had delusions of grandeur and rejoiced under titles like "VP" when they were, in fact, probably only one or two steps removed from the low-level work.

  • airdrik (unregistered) in reply to boog
    boog:
    Bill's Kid:
    dpm:
    Jill:
    9 months? Was the VP having a baby or deep undercover with the zombie plasmoids?
    The "mm" clearly indicates that nine days passed between comments, regardless of which side of the pond you're on.

    No. The "01" and "10" clearly indicated that 9 days had passed. The "mm" indicated that the month had been redacted.

    If the month was redacted, how can anyone be sure how many months passed? Perhaps this was 1 month and 9 days? 2 months and 9 days?

    It seems the year has been redacted too.

    I think it reasonable to assume just 9 days - an appropriate turn-around time for someone paying attention to notice the change made and unmake it (such that the first developer will notice that the change unmade broke his unit test).

    TRWTF is "Unit" tests which require specific data in the database. Are they testing inserting and deleting from the database? If not they should be spoofing/mocking the data access to provide the data that they need for the unit test. If they really need specific data in the data base then it is no longer a unit test because it isn't testing a single unit.

  • michael (unregistered) in reply to boog
    boog:
    Why is the VP so involved in the low-level implementation details here?
    I used to work for one of the last "real" investment banks and there my colleague who was at the end of the IT foodchain was also a VP.it's only a title and doesnt mean sh*t in some companies.
  • evil mutant zombie plasmoid (unregistered)

    whistles

    Just as soon as my SQL book from evil-mutant-zombie-amazon.com comes in, you're in real trouble. As long as my hands haven't decomposed by then.

    What, a branch of amazon.com? No no, not like the bookstore, you know. Just a partner who happens to be an evil mutant zombie Amazon. We zombies have to stick together. It's not easy being green, what with the putrefaction. Now if I can keep talking, the air I'm breathing should hold off the anaerobic organisms eating away at my hands.

    So who else is preparing to be a zombie programmer for Halloween? Bonus points for learning FORTRAN, and/or letting me eat your brains.

  • Anonymous Guy (unregistered)

    I agree that the data should be archived or marked as 'deleted' in the table. Deleting data such as products is never a good idea long term without some way of getting to it.

    So what happens if this occur in the test case:

    1. Trigger removed
    2. Unforeseen failure occurs so 3 doesn't run
    3. Trigger added (if ok only or whatever occured in step 2 is caught properly).

    Hopefully 2 is either impossible or your not doing this on an environment that cares whether the trigger is there or not...i dont know much about triggers but if its one trigger for insert/update/delete then you do need it to run...unless the trigger is useless to begin with :)

  • souttheslim (unregistered)

    As a the leader of a Plasmoid horde, I take offense at being compared with a Zombie!

  • (cs)

    Jody, Jody, six feet four Jody can't delete those products any more Now Susie's with Jody 'cause she's a whore

  • mea37 (unregistered)

    TRWTF is the apparent attitude that a story like this would contain only one WTF.

    The VP's involvement is a WTF. The usage of the "VP" title in the commit log makes clear that it's a position of true VP authority, so he/she has no business in the nuts and bolts of the code.

    The use of a trigger to prevent deletes by virtue of a bug is a WTF. If you want to reject deletes, write code to do it explicitly with a sensible error message.

    The use of a trigger to prevent deletes at all may be a WTF; depends on the permission system for the database, but generally if you don't want to grant delete access to a table you don't have to.

    Preventing deletes in a test environment (or, alternatively, testing in a production environment) is a WTF. Test code must be free to do whatever it needs to set up test cases. Of course, having a production-like trigger during the test case execution is fine - in fact, not doing so would also be a WTF. What's needed is a "set up mode" where the delete is legal - and the submitter found a way to get that. If app code is able to turn triggers on and off in production, that again is a WTF.

    As for the physical vs. logical delete policy, assuming that we know enough about the system and environment to make the "right" call is a WTF. An operational system might be well served by having a "current products" table, and probably shouldn't be bothered with deep historical queries anyway. Not every database is meant to preserve every bit of information it ever sees.

    Refering to this test case as a unit test is a WTF, but running this type of test as part of the development process is not a WTF.

  • mea37 (unregistered) in reply to Anonymous Guy
    Anonymous Guy:
    if its one trigger for insert/update/delete then you do need it to run...unless the trigger is useless to begin with :)

    It's pretty clearly used for logging writes to the underlying table. So no, you don't "need" it to run (though running even briefly without it defeats the purpose for which it was created) as the app would function and just not capture log events; and no, it isn't "useless".

  • Zach Bora (unregistered)

    Wheres my dailywtf? I feel like I`ve been stolen.

  • Fred (unregistered) in reply to Gruntled Postal Worker
    Gruntled Postal Worker:
    usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.
    This needs to be tattooed on the ass of every mindless zombie who knee-jerk deletes anything they don't want to see any more.

    Databases collect facts. If they were true when you collected them, they will be true forever. (On April 12 the price of rice was 1.23.)

    If they were false when you collected them, then you might want to fix the error, but deleting them is rarely the right solution.

    And for those who are habitually "purging" data to save space, try to recall that the price of disk is around ten cents a gigabyte and falling. In other words, in the time it takes you to think "I wanna delete some stuff" you've already eaten up more of your employer's money in salary than they will allegedly save by reusing the disk space.

    If you're deleting for performance... try an index. They're amazing! Or get a real computer with a real database.

    I'm thinking one could almost make the case that data should be written to write-once-never-modify media.

  • (cs) in reply to evil mutant zombie plasmoid
    evil mutant zombie plasmoid:
    Just as soon as my SQL book from evil-mutant-zombie-amazon.com comes in, you're in real trouble. As long as my hands haven't decomposed by then.
    If you're in need of brains, you can order them online. No SQL books though, I'm afraid.
  • Jeff (unregistered) in reply to Someone who can't be bothered to login from work
    Someone who can't be bothered to login from work:
    I belong to a group which once changed the members' forum so you couldn't mention the name of one of an ex-member.
    TopCod3r?
  • DeGustibusNonDisputandumEst (unregistered) in reply to CyberShadow

    No, TRWTF is SQL.

  • (cs) in reply to Fred

    A cheap SATA disk might be 10 cents per GB, but the cost of an FC disk attached to a SAN, with RAID overhead, sparing overhead, virtualization overhead, plus sufficient backup hardware to backup the data in some son/father/grandfather method and restore the data within the timeframes of an SLA becomes quite a bit greater.

    For managed data, this sounds reasonable, I don't disagree. But for shared storage with perhaps 1000 people all putting files with no oversight, not so much.

  • (cs) in reply to airdrik
    airdrik:
    TRWTF is "Unit" tests which require specific data in the database. Are they testing inserting and deleting from the database? If not they should be spoofing/mocking the data access to provide the data that they need for the unit test. If they really need specific data in the data base then it is no longer a unit test because it isn't testing a single unit.
    Excellent point; just because a test is automated does not make it a unit test. It's surprising how many people seem to confuse the two terms when it comes to testing.

    Due to this confusion I've actually worked with developers who argued against the use of unit tests in complex systems which they believed were too complex for effective unit testing. I just couldn't convince these people that unit tests are only supposed to test a single module (or unit) at a time.

  • longLiveTehZombies (unregistered) in reply to nah
    nah:
    BGerrissen:
    Unit test SHOULD fail. Might as well omit unit testing entirely if you hack to make them work...
    1. Insert test data.
    2. Perform unit test.
    3. Delete test data.

    +1 internehts for you.

    captcha: suscipit - I suscipit that the poster has no idea what he is doing other than bragging about how he fixed it.

  • (cs) in reply to Fred
    Fred:
    Gruntled Postal Worker:
    usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.
    This needs to be tattooed on the ass of every mindless zombie who knee-jerk deletes anything they don't want to see any more.

    Databases collect facts. If they were true when you collected them, they will be true forever. (On April 12 the price of rice was 1.23.)

    If they were false when you collected them, then you might want to fix the error, but deleting them is rarely the right solution.

    And for those who are habitually "purging" data to save space, try to recall that the price of disk is around ten cents a gigabyte and falling. In other words, in the time it takes you to think "I wanna delete some stuff" you've already eaten up more of your employer's money in salary than they will allegedly save by reusing the disk space.

    If you're deleting for performance... try an index. They're amazing! Or get a real computer with a real database.

    I'm thinking one could almost make the case that data should be written to write-once-never-modify media.

    One of the tables my team uses sees enough writes to keep 4 app machines maxxed out on disk I/O (reading MSMQ) nearly all day. I suppose you'll tell me we should not delete that data, either?

  • Larry (unregistered) in reply to Sutherlands
    Sutherlands:
    One of the tables my team uses sees enough writes to keep 4 app machines maxxed out on disk I/O (reading MSMQ) nearly all day. I suppose you'll tell me we should not delete that data, either?
    Well, since you're recording the GPS coordinates of every cell phone in the country once every 10 seconds, yes I wish you would delete it.
  • Herby (unregistered)

    As was shown in the original description, if you REALLY want to change things (delete, etc...) there IS a way. Not really obvious (at first glance), but there is a way.

    If the aforementioned VP didn't want deletes, he might have a (albeit weird) reason. Who knows, the data "saved" might be used for bribery or extortion (at the VP level it might be possible).

    Our programmer DID find a way to get his perceived task done (turn off the necessary check), so it all boils down to:

    When there is a will, there IS a way!

    Back to WTF's, the real one is the VP (say no more!).

  • (cs) in reply to Herby
    Herby:
    Back to WTF's, the real one is VB (say no more!).
    FTFY
  • Fred (unregistered) in reply to Sutherlands
    Sutherlands:
    Fred:
    Gruntled Postal Worker:
    usually if you don't need records any more, you mark them 'disabled' rather than attempting to change history.
    This needs to be tattooed on the ass of every mindless zombie who knee-jerk deletes anything they don't want to see any more.

    Databases collect facts. If they were true when you collected them, they will be true forever. (On April 12 the price of rice was 1.23.)

    If they were false when you collected them, then you might want to fix the error, but deleting them is rarely the right solution.

    And for those who are habitually "purging" data to save space, try to recall that the price of disk is around ten cents a gigabyte and falling. In other words, in the time it takes you to think "I wanna delete some stuff" you've already eaten up more of your employer's money in salary than they will allegedly save by reusing the disk space.

    If you're deleting for performance... try an index. They're amazing! Or get a real computer with a real database.

    I'm thinking one could almost make the case that data should be written to write-once-never-modify media.

    One of the tables my team uses sees enough writes to keep 4 app machines maxxed out on disk I/O (reading MSMQ) nearly all day. I suppose you'll tell me we should not delete that data, either?
    Have you come up with a way to delete it that does not cause additional writes?

  • Gondolf (unregistered)
    insert a row into PRODUCTS, work with it, and then delete it when the test was complete

    This is TRWTF.

  • Actually read the comments (unregistered)

    Did anyone commenting about "you should never REALLY delete rows, blah, blah blah" actually read the comments for the trigger posted with TFA? Did you not see the mention of the "PRODUCTS_AUDIT" table that almost certainly stored all the records that were deleted from the main PRODUCTS table?

Leave a comment on “It's Not A Bug, It's Data Security”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article