• Whiskey Tango Foxtrot? Over. (unregistered) in reply to wintermyute

    wintermyute:
    Waiting for someone to say that the Real WTF is that the system was written in Perl...

    Why would that be a WTF? Perl is the A-1 choice for WTF programming worldwide.[;)]

  • (cs) in reply to Robert
    Anonymous:

    Ok, I have an honest question.

    I use a method like this because I need to know the ID number before I write something to the database (like, to create a link to the new object in several tables).  So I generate a random ID, check to make sure it isn't already in use, and return it.

    I know MSSQL has an auto-increment option, but how would I implement a non-WTF solution that returns the ID so I can use it?



    Anonymous ... beware! There's a lot of bad advice in this thread. There is exactly one optimal way to deal with Identity (sequence) in MS SQL 2000 and later:

    Insert into your table
    select scope_identity()

    Do not use max(), do not use @@identity, and look in BOL so you don't copy some incorrect syntax from the kind folks here.
  • Jeff Moss (unregistered) in reply to anon

    No, it froze up everything because it was stuck in an infinite loop, querying the database constantly.

  • hash (unregistered) in reply to Robert

    Run

    SELECT @@IDENTITY As InsertedID

    after the insert query before closing the connection and the last inserted identity on that connection will be returned

  • (cs) in reply to hash

    Again, that's not right.

    SCOPE_IDENTITY(). Please. Read up on it.

  • (cs) in reply to Anonymous Cupboard
    Anonymous:

    See, all you think that it would be easy for the guy to just put in a fix that uses autoincrement/sequences/etc, but guess what...  What will you do about all the keys in the database already, randomly distributed over the number space, and one can only guess how many other tables and rows are joined using those keys!  It's kinda hard to start assigning them sequentially once there are thousands of randomly generated keys already there.

    The solution that he used was the only way to fix it quickly, and perhaps even the only fix even given a moderate amount of time.  A real long term fix would require not only rewriting the id generator, but rekeying the whole database.



    I see your point about this being the only possible "put out the fire" fix. The real fix would probably involve creating an identity column > 9 digits (his new scope) and starting the numbering at 1,000,000,000. Identity columns are NUMERIC, so you can use up to 36 digits. The numbers < 10 digits are no great loss.

    The one thing I'm not sure of is if you can assign numbers to identity colums (as opposed to letting the DB assign them). I think you can, because BCP works just fine if it contains an IDENTITY column in the BCP file. I'm sure someone will correct me otherwise.

    My preferred fix would be GUIDs, though, but the system is already set up using NUMERIC columns, and it'd be even more painful to change that.
  • (cs) in reply to WTF Batman

    all he should have done is define the global variable LENGTH_OF_ID_THINGY_THING to 9, then replaced the 9's with it, then u only need to update that one value to expand next time!!

  • (cs) in reply to Whiskey Tango Foxtrot? Over.
    Anonymous:

    wintermyute:
    Waiting for someone to say that the Real WTF is that the system was written in Perl...

    Why would that be a WTF? Perl is the A-1 choice for WTF programming worldwide.[;)]



    http://www.duk0r.net/matrix/106.jpg
    http://www.duk0r.net/matrix/107.jpg
  • (cs) in reply to sao
    sao:
    all he should have done is define the global variable LENGTH_OF_ID_THINGY_THING to 9, then replaced the 9's with it, then u only need to update that one value to expand next time!!


    He also could have started numbering sequentially at 7 digits, and taken at least that much of the WTF out of the PERL code. Thread-safe issues aside (since this isn't anyway), a select max(id) + 1  would have sufficed for the quick-fix. That way he's not randomly dancing all over the table looking for unused numbers. Goes from O(godOnlyKnows) to O(1). Yay!

    // waiting for someone named 'god' to fill in the blank. Eesh.
  • An apprentice (unregistered) 

    while (length($possibleTranId) < 9) 
    { # ensure length is correct
      $possibleTranId .= int(rand 9) + 1;
    }
    Do unto other developers as they would do to you...

    Btw, is this the first Perl WTF here or did I miss something? I'm waiting for Ruby!

  • Ryan (unregistered) in reply to boohiss

    You don't want to use @@Identity, use SCOPE_IDENTY().   @@Identity will return the last identiy generated during the connection (or could possibly be the last identity the database generated).  SCOPE_IDENTITY() only returns the last generated identity value in the current scope.

  • hash (unregistered) in reply to An apprentice

    The usage alone of ruby in a production project would be a WTF

  • (cs) in reply to nyetter

    Anonymous:
    Truly, the mind boggles.

    (come on, a DB without a sequence? wtf)

     

    Well yeah, but then it wouldn't be random!

  • aprenot (unregistered) in reply to anon

    Anonymous:
    Why on earth would this cause the database AND web servers to freeze? Shouldn't it just log an error everytime it tried to add a new transaction? Surely the web servers should still be able to serve pages even though they can't start a new transaction. . .

    Because it is running in an infinite loop, so every single page access runs forever, not releasing its memory or its thread.  Notice that there are two loops - 1) to create the 6 digit number, and 2) one that continues looping until it finds one that hasn't been used.  Since they had all been running, it continually generates new numbers and continually queries the database.  You get 15 - 20 simultaneous uses of this function and you machine is now (from the users perspective) locked up.  The processor is still going, but there are no resources to emit any kind of response.

    As an aside, this is quite possibly the worst possible method for creating keys, because as the number of available keys slims down, the time it takes to create one goes up exponentially.  I feel really sorry for the users of this system.

  • (cs) in reply to Hugo
    Hugo:
    Anonymous:

    I know MSSQL has an auto-increment option, but how would I implement a non-WTF solution that returns the ID so I can use it?

    If:

    a) the auto-increment MUST be an identity, and

    b) you MUST know the new value before actually inserting,

    then:

    1) Restart reading from the beginning of the specs. You have already read past the WTF, you just didn't realise yet.

    2) If you still want this, use IDENT_CURRENT('tablename') and add 1. Make sure to use a transaction and force a table lock before calling the function. Don't forget to send your code to Alex when you're done, then we can all have a laugh at it.

    Best, Hugo



    Hugo -- that is a GREAT post!  Could not agree more, and very well put.

    Thank you, that made my day.  :)

  • Anonymous Cupboard (unregistered) in reply to Some guy
    Anonymous:
    Anonymous:
    Omnifarious:
    wintermyute:
    Awesome.  I like that the guy didn't actually solve the underlying problem, just kind of swept it a little further under the rug.

    I hope he bragged later about fixing the whole app with "1 keystroke!".

    Waiting for someone to say that the Real WTF is that the system was written in Perl...

    IMHO, as an emergency, fix it in the middle of the night so stuff runs again solution, it's not so bad. Now, if it wasn't brought up with someone and stuck in a "We need to fix this ASAP" list as soon as he got into work the next morning, then its definitely a WTF.



    See, all you think that it would be easy for the guy to just put in a fix that uses autoincrement/sequences/etc, but guess what...  What will you do about all the keys in the database already, randomly distributed over the number space, and one can only guess how many other tables and rows are joined using those keys!  It's kinda hard to start assigning them sequentially once there are thousands of randomly generated keys already there.

    The solution that he used was the only way to fix it quickly, and perhaps even the only fix even given a moderate amount of time.  A real long term fix would require not only rewriting the id generator, but rekeying the whole database.



    Since the original value had a length of 6, just start your new sequence id at 1000000.........bingo.



    Heh, I thought of that while I was typing, but decided to see if people would catch that, seems like some people did :)
  • (cs) in reply to versatilia
    versatilia:
    Omnifarious:
    Anonymous:
    I'm beginning to think all registered users need an auto-reply to all post feature that reposts the same thing over and over on every thread.

    I have noticed a similarly distressing lack of originality in the replies. Yes, we all know that some of you think others should be barred from programming. Yes, we all know that the goggles do nothing and that the WTF is brillant. Please, tell us something we don't know. Those posts are about as stupid as the 'fist' posts.



    So here's the plan... someone create a bot which monitors the RSS feed for new WTFs, and as soon as it's there post something like:

    fist my useless goggles, this is brillant but I don't see the brillant wtf... etc

    Or better, an 'auto troll' to block any post containing 'brillant' and other old jokes!

    oh the googles! they do nothing!!.. what a bunch of newbies, this is what WTF is all about!!

    the stories are (more often than not) so made up! (exageration of some reality)

  • (cs) in reply to Jeff S
    Jeff S:
    Hugo:
    Anonymous:

    I know MSSQL has an auto-increment option, but how would I implement a non-WTF solution that returns the ID so I can use it?

    If:

    a) the auto-increment MUST be an identity, and

    b) you MUST know the new value before actually inserting,

    then:

    1) Restart reading from the beginning of the specs. You have already read past the WTF, you just didn't realise yet.

    2) If you still want this, use IDENT_CURRENT('tablename') and add 1. Make sure to use a transaction and force a table lock before calling the function. Don't forget to send your code to Alex when you're done, then we can all have a laugh at it.

    Best, Hugo



    Hugo -- that is a GREAT post!  Could not agree more, and very well put.

    Thank you, that made my day.  :)

    the original question did not demand knowing the identity before inserting. It required returning the inserted identity so it could be used. scope_identity() works just fine for that. The original question was perfectly legit and has a workable solution. As of Sql Server 2000. The real WTF is that it took so long for SqlServer to provide this solution.

  • Jeff Moss (unregistered) in reply to hash
    Anonymous:
    The usage alone of ruby in a production project would be a WTF


    You don't know what you're talking about.
  • (cs) in reply to Jeff Moss

    Hey did anyone mention yet that you can use SCOPE_IDENTITY() ?

    It scares me how lately many people seem to think these WTF's are little programming challenges and feel the need to present their "solutions".  


  • (cs) in reply to OneFactor
    OneFactor:
    Jeff S:
    Hugo:
    Anonymous:

    I know MSSQL has an auto-increment option, but how would I implement a non-WTF solution that returns the ID so I can use it?

    If:

    a) the auto-increment MUST be an identity, and

    b) you MUST know the new value before actually inserting,

    then:

    1) Restart reading from the beginning of the specs. You have already read past the WTF, you just didn't realise yet.

    2) If you still want this, use IDENT_CURRENT('tablename') and add 1. Make sure to use a transaction and force a table lock before calling the function. Don't forget to send your code to Alex when you're done, then we can all have a laugh at it.

    Best, Hugo



    Hugo -- that is a GREAT post!  Could not agree more, and very well put.

    Thank you, that made my day.  :)

    the original question did not demand knowing the identity before inserting. It required returning the inserted identity so it could be used. scope_identity() works just fine for that. The original question was perfectly legit and has a workable solution. As of Sql Server 2000. The real WTF is that it took so long for SqlServer to provide this solution.



    uh ... that's great, thanks for telling us ... not sure what any of that has to do with what you've quoted, but thanks for clearing that up.
  • Integration Nation (unregistered) in reply to Robert
    Anonymous:

    Ok, I have an honest question.

    I use a method like this because I need to know the ID number before I write something to the database (like, to create a link to the new object in several tables).  So I generate a random ID, check to make sure it isn't already in use, and return it.

    I know MSSQL has an auto-increment option, but how would I implement a non-WTF solution that returns the ID so I can use it?

     

    I've solved that problem numerous times with the same solution: I place a record in the system that's marked that it is temporary in nature.  Call center applications usually have this requirement.  Then when the data is complete, the record is updated with the correct information and the tracking number is also still the PK.

    The other approach for developers who have an asshat architect or users that think two round trips will bring down humanity as we know it is to randomly generate this tracking number at the client (like UPS does) with smart-numbering that makes collisions difficult.  My personal favorite is using the last 1/2 of the client's subnet in the number.   i.e. for machine 192.168.1.54, you could use 168-1-54-CD-0005413 (seperate the number however you want, store it in the DB as a long)... which also works on web apps since the web server knows what IP the client came from, the check digit is calculated from the user's login.  The rest of the number can just hash off the time.

    Just please don't use MSGUIDs, I hate those, and they make lousy PKs.

  • hash (unregistered) in reply to Jeff Moss
    Anonymous:
    Anonymous:
    The usage alone of ruby in a production project would be a WTF


    You don't know what you're talking about.


    Thanks for clearing that up, and great counter argument.
  • (cs)

    The real WTF is this forum sof... oh, wait.  Nevermind.

    Anyway, I find it mildly interesting that this is actually no different from the technique used to generate the prime numbers for public/private key encryption: you pick 512 (or 1024, or 2048) random bits, then see if it's prime.  If not, you pick a new set of bits, then see if it's prime.  If it's not, you pick a third set of bits, ...

    (of course, no application actually verifies true primality; that would take forever. Google 'Rabin-Miller' for more information.)


  • (cs)

    # should work just fine, and give the added
    # benefit of being a random order

    This is what I don't get. What's the benefit of it being random?

    And also, how do you have "random order"?

    Adam

  • (cs) in reply to versatilia
    versatilia:
    Omnifarious:
    Anonymous:
    I'm beginning to think all registered users need an auto-reply to all post feature that reposts the same thing over and over on every thread.

    I have noticed a similarly distressing lack of originality in the replies. Yes, we all know that some of you think others should be barred from programming. Yes, we all know that the goggles do nothing and that the WTF is brillant. Please, tell us something we don't know. Those posts are about as stupid as the 'fist' posts.



    So here's the plan... someone create a bot which monitors the RSS feed for new WTFs, and as soon as it's there post something like:

    fist my useless goggles, this is brillant but I don't see the brillant wtf... etc

    Or better, an 'auto troll' to block any post containing 'brillant' and other old jokes!


    I'd agree for the most part... Except, I never tire of declaring Brilliant! when I come across something asinine ~ what can I say, the guiness guys have done well in their advertising campaign
  • (cs) in reply to Integration Nation

    Anonymous:

    The other approach for developers who have an asshat architect or users that think two round trips will bring down humanity as we know it is to randomly generate this tracking number at the client (like UPS does) with smart-numbering that makes collisions difficult.  My personal favorite is using the last 1/2 of the client's subnet in the number.   i.e. for machine 192.168.1.54, you could use 168-1-54-CD-0005413 (seperate the number however you want, store it in the DB as a long)... which also works on web apps since the web server knows what IP the client came from, the check digit is calculated from the user's login.  The rest of the number can just hash off the time.

    Just please don't use MSGUIDs, I hate those, and they make lousy PKs.

    You know what you described is essentially the GUID algorithm.  But yours is much better, I can tell just by reading about it.  Especially since you have to implement it yourself.  Plus, I wound't want a guarantee of uniqueness like I get with UUID/GUID so I still have to code for collisions anyway.  Much better.

    If you ever have to write a complex object hieararchy out to the database in a single transaction, you'll realize that it's not just one extra round trip.

     

  • Runtime Error (unregistered) in reply to aprenot
    Anonymous:

    As an aside, this is quite possibly the worst possible method for creating keys, because as the number of available keys slims down, the time it takes to create one goes up exponentially.  I feel really sorry for the users of this system.



    The original programmer could have read each record one a time to verify that the key was unique.  That could have made it worse.
  • newby (unregistered) in reply to nyetter

    At my last position, we used a DB called Gupta SQLBase.  It did not have a sequence/auto-increment/identity field.  It sucked bad.  The standard practice was to have a table containing the next id number for all tables.  We would lock the table, select the value, update it, then unlock and go on our way.

  • Dingle (unregistered) in reply to anon
    Anonymous:
    Why on earth would this cause the database AND web servers to freeze? Shouldn't it just log an error everytime it tried to add a new transaction? Surely the web servers should still be able to serve pages even though they can't start a new transaction. . .


    infinite loop, with a query within each iteration
  • KeithSpook (unregistered) in reply to Jeff Moss
    Anonymous:
    Anonymous:
    The usage alone of ruby in a production project would be a WTF


    You don't know what you're talking about.


    No!  He is entirely correct.  Using *any* language in production in a WTF. Have you not been reading TDWTF for long?
  • Tal (unregistered)

    If he/she had used natural keys he wouldn't have this problem...

    go for them Jeff, You had convinced me about using natural keys (really)

  • Oracle Fanboy (unregistered) in reply to mlathe
    mlathe:

    Anonymous:
    Anonymous:
    You could try to get the highest ID (with MAX()) and simply and 1 to that number and use it as your ID.


    Not threadsafe.  The WTF isn't either, but not a good solution

    question: is it thread safe if you do a nested SQL? something like

    insert into foo (
      select (max(id)+1), 'first name', 'last name', '12345'
      from foo
    )

    i think it is.

    Two uncommited transactions doing the same thing insert will get the same max(id) + 1

    Assuming your using a real database that is, not some glorified wrapper around berkely DB files (mysql anyone?)

  • (cs) in reply to JohnO
    JohnO:

    wintermyute:
    Waiting for someone to say that the Real WTF is that the system was written in Perl...

    I was wondering what that was.  What the hell is My?  Local variable declaration?

    Yep - it's one of the two types of local variable declaration in Perl (the other being "local myVariable"). IIRC, My is statically-scoped, Local is dynamically-scoped, and I don't want to even think about how the two interact (fortunately, "local" doesn't seem to be used that much in practice).

  • anonymouse (unregistered)

    You can create your own sequence table if you must. Then you just start a transaction, select from the table, update the sequence and then commit.

  • Moo Goo Gai Pan (unregistered) in reply to Justin Miller
    Anonymous:
    REMEMBER FOLKS:

    Every time you program like that God kills a kitten.


    In that case....I'm gonna write me some WTF's...
  • (cs) in reply to Misthop
    Anonymous:
    Anonymous:
    You could try to get the highest ID (with MAX()) and simply and 1 to that number and use it as your ID.


    Not threadsafe.  The WTF isn't either, but not a good solution


    Any system (correctly) implementing try-write is safe.  Pseudocode:

    sequence number = // highest existing, -1 if none
    do
      sequence number += 1
      // if too big then throw error
      try-write // similar to INSERT in SQL; succeeds if and only if record key is not already in the table
    until write succeeded

  • (cs)

    Nice injection hack

  • John Doe (unregistered) in reply to versatilia
    versatilia:
    Omnifarious:
    Anonymous:
    I'm beginning to think all registered users need an auto-reply to all post feature that reposts the same thing over and over on every thread.

    I have noticed a similarly distressing lack of originality in the replies. Yes, we all know that some of you think others should be barred from programming. Yes, we all know that the goggles do nothing and that the WTF is brillant. Please, tell us something we don't know. Those posts are about as stupid as the 'fist' posts.



    So here's the plan... someone create a bot which monitors the RSS feed for new WTFs, and as soon as it's there post something like:

    fist my useless goggles, this is brillant but I don't see the brillant wtf... etc

    Or better, an 'auto troll' to block any post containing 'brillant' and other old jokes!

    I actually laughed at this!

  • (cs) in reply to Charlie Marlow
    Charlie Marlow:
    From looking at the original code, it does not seem that there were any duplicate keys, but that the key size had exceeded the anticipated length, and that was throwing the system into an infinate loop. I think it would take more than half an hour to fully explore what ramifications changing a primary key would have on a system.


    I'm thinking it's unlikely that it was actually reaching the infinite-loop state -- as soon as the keyspace started getting close to full,  the loop would have started running many more iterations on average,  multiplying the server load by a large factor and bringing the system to its knees, even while the queries were still logically capable of succeeding.

    Can you run a million one-row queries before your webserver times out the request? Apache's default timeout is 300 seconds, meaning that to get the last few possible ids, you'd have to be executing the query over 3,000 times per second on average. At a time when there are probably many other requests stuck in the same loop concurrently (because they've all started taking so long to complete, so more of them will be overlapping).

    So unless the DB server hardware was ridiculously over-specced for the application's normal workload, the system probably would have started dropping requests before it fully ran out of ids. And a dropped request wouldn't consume one of the few remaining possible ids, so it could run in that state for quite a while. Eventually, a few requests would get lucky enough to find those last few ids, and then it'd reach the infinite-loop state. But the wording "...completely freeze up within minutes..." makes me think that they were just seeing it get close enough for requests to start hanging (before the server(s) crashed?).

  • (cs)

    Auto-incremented transaction ID's are just as stupid as creating transaction ID's with such a small number of possible values.  Let's see, my transaction ID is 10000031 so lets spoof a transaction with 10000032 and see what happens. . . 

    And even if transaction IDs aren't exposed now, you can almost guarantee that at some point some moron will accidentally, or purposefully expost them in the future.

    Effective random number generation is CS101.  Why leave such things up to the DB and auto-incrementers?

  • mcguire (unregistered) in reply to JohnO
    JohnO:

    wintermyute:
    Waiting for someone to say that the Real WTF is that the system was written in Perl...

    I was wondering what that was.  What the hell is My?  Local variable declaration?



    Perl originally only had global variables.  Then, someone pointed out to Larry, "WTF?"  Larry didn't want to break existing code (well, too much, anyway), so he added the "local" keyword.  It, of course, indicated that the variable so declared was dynamically scoped.  So, someone else asked of Larry, "WTF?"  Larry replied by adding the "my" keyword, and lexically scoped variables ("Yay!").  Then, he added "our", leaving us to wonder about the second and third person.  WTF?
  • tim (unregistered)

    my mouth hangs open - I don't know what to say to this.

  • craig (unregistered) in reply to Koen

    Except that leaves open the possibility of two simultaneous calls getting the same max value and the first insert to be submitted to the DB will work, while the other will fail.

  • (cs) in reply to Omnifarious

    While we're at it can we ban the phrase 'the real wtf is...'.

  • test (unregistered) in reply to sjfsjf


    Looks like someone found a bug....
  • NZ'er (unregistered) in reply to Charlie Marlow

    Looks like someone found a bug....  (found floating just under No one likes the second page.  on the second page) 

    what only 1............ as pointed out many times here this forum software is the biggist cluster of bugs most of us have ever seen!!!!!

  • miraculixx (unregistered) in reply to JohnO

    If nothing else, at least keep track of ids used in a separate control table, then your new id will be max(id) + 1 each time. Make sure you implement locking on the control table while calculating your next id.

  • Mike Edenfield (unregistered) in reply to JohnO
    JohnO:
    Anonymous:

    Ok, I have an honest question.

    I use a method like this because I need to know the ID number before I write something to the database (like, to create a link to the new object in several tables).  So I generate a random ID, check to make sure it isn't already in use, and return it.

    I know MSSQL has an auto-increment option, but how would I implement a non-WTF solution that returns the ID so I can use it?

    Use UUID/GUID.



    There are a number of ways to pull this off, depending on the front-end language you're using.  This is a very common problem and .NET has implemented a (kinda) solution behind the scenes for you, which basically goes:

    * Put everything into a multi-data-table dataset

  • Mike Edenfield (unregistered) in reply to marvin_rabbit
    marvin_rabbit:
    I admit that I did have to invent a method of assigning transaction ID's before.  (Older versions of FoxPro didn't have any autoinc fields.)

    But when I did it, I made it so that there were around 14 quintillion possible id's.  And I only expected a few hundred new ones per day.

    (Yes, yes, everyone can start in with the "FoxPro is the *real* WTF" comments.  I'm sure that Nostradamus even had a quatrain predicting those.)


    Funny you mention FoxPro.  My last job included a very large GUI app written in Visual Foxpro, where the lead designer thought it would be brillant to make all our primary keys *decimal values*.  Specifically, they were of the form <unique integer value>.<branch number>, like 1.10001, 2.10001, 3.10001, etc.  So we had the joy of figuring out how to get FoxPro to automatically call a VFP function that called a stored procedure that lookup up a table that contained the next value for each branch, build the decimal using some goofy math, and send it back up the chain. 

    I'm fairly sure the phrase "WTF" was spoken loudly and often during the early phases of this project.

Leave a comment on “This Should Work Just Fine”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article