Helpful error message



  • ORA-03149: Invalid Oracle error code

    Well, thanks, Oracle, but you're giving me the error code, why don't you just give me a valid one?

    Some searching revealed that this is due to an Oracle bug in recent versions and it should have generated a numeric overflow error instead.

    The actual cause was that in a field that should have values between 0 and 100, the source (which was an IEEE double type) had the value INF. (It's a ratio, you can guess the rest.) So I put logic in the ETL to say that if it was more than 1000, just set it to null.

    (Next failure: the 3-digit column I provided for customer age is not enough to store the record for the customer who is 1196.05 years old. Gotta love test data.)


  • SockDev

    @Scarlet_Manuka said in Helpful error message:

    the customer who is 1196.05 years old

    Glad to see Enoch is still around :slight_smile:



  • @RaceProUK My colleague wanted to know why I was being racist against elves.


  • kills Dumbledore

    @Scarlet_Manuka said in Helpful error message:

    the 3-digit column I provided for customer age

    1. is that a three character text column, which would be a massive :wtf:, or a numeric with a maximum of 999, which is a weird limit but whatever
    1. Why store age rather than DOB, which doesn't mean the age field gets out of date and has to be updated once a year?


  • @Jaloopa It's a number(5, 2) column, i.e. numeric with 3 digits before the decimal point and 2 after. That's the way Oracle number columns are specified, not in terms of any particular number of bits, so 999 (or in this case 999.99) is quite a normal sort of limit. (Well, it was (5, 2); it's (7, 2) now, at least in the test environment.)

    This isn't a customer table, so DOB isn't particularly useful. You can think of it as an event table, and the age of the customer at the time of the event is more relevant than their age now. Plus, I store age of customer because that's the data I get. When you're given an XSD and told "build a warehouse but I don't know what queries we will want to run", the default is pretty much just storing the data you get and planning to update it as necessary later.

    This warehouse as a whole is probably going to be more used for in progress data than reporting on old data, so concerns about old records being harder to interpret are not as important as they would be in another context.


  • :belt_onion:

    @Scarlet_Manuka you're just lucky it wasn't an Ora-600 Space Leak.
    or ORA-07445: exception encountered: core dump.



  • @darkmatter I have "ORA-600 and ORA-7445 Error Look-up Tool" bookmarked. That probably tells you all you need to know about how much fun my life is.

    (Actually, it's been a long time since I've had to use that page. Let's hope it stays that way.)

    For the uninitiated, those errors don't mean anything specific; they give you a bunch of codes which you then have to go and look up with the above mentioned tool to find out what the actual error was.


  • :belt_onion:


  • :belt_onion:

    @Scarlet_Manuka I once had a query that could reliably cause a Space Leak by adding a GROUP BY to the end. If there was only 1 row in the result set without GROUP BY, the GROUP BY would cause a Space Leak.



  • I thought the point of paying thousands for a database was that you had someone to bitch to when something broke.



  • @anonymous234 said in Helpful error message:

    I thought the point of paying thousands for a database was that you had someone to bitch to when something broke.

    You do. And usually they'll fix it.

    ... fucking eventually. My experience with Oracle Support has been mixed-- sometimes it takes months to work through a fairly simple issue, sometimes you'll get the one guy who goes, "Oh, I know exactly what that problem is", points you to a Metalink article you somehow didn't find in four hours of searching, and gives you the workaround that'll get you going immediately.


  • :belt_onion:

    @heterodox said in Helpful error message:

    You do. And usually they'll fix it.

    The biggest Oracle problem we ever had was where a unique partitioned index on a large table corrupted and was no longer unique (HOW THE FCK?)

    So basically you could not query, update, delete, etc ANY data on those corrupted partitions because it would return bad results/fail due to unique key violations.

    Their answer: yeah that can happen on sufficiently large tables, create a new table, reload the data, and drop the old table.

    ................!!!!!!!!!!!!!!!!!!!!!!!!!!


  • Impossible Mission - B

    @darkmatter said in Helpful error message:

    Their answer: yeah that can happen on sufficiently large tables, create a new table, reload the data, and drop the old table.

    This reminds me of the UPS Customer Support lady who acknowledged that they hadn't delivered my package because they had the incorrect address for me somehow, "but don't worry, we've already sent out a postcard explaining how you can take delivery of your package."

    Sometimes you just need to think about whether what you're about to say is fundamentally absurd in the context of known facts. And sometimes people fail to do that.


  • Discourse touched me in a no-no place

    @masonwheeler said in Helpful error message:

    they hadn't delivered my package because they had the incorrect address for me somehow, "but don't worry, we've already sent out a postcard explaining how you can take delivery of your package."

    :headdesk:



  • @masonwheeler said in Helpful error message:

    Sometimes you just need to think about whether what you're about to say is fundamentally absurd in the context of known facts. And sometimes people fail to do that.

    :man:: "E-mail's down."
    :telephone:: "Please use <this> e-mail address to create a ticket in the ops' ticketing system."
    :man:: "..."


  • :belt_onion:

    @masonwheeler it wasn't a sure thing that sufficiently large tables index constraints would fail, so drop/reload did indeed work. But that gave no guarantee that the index would remain unique in the future.... kind of DEFEATING THE ENTIRE POINT OF HAVING A UNIQUE CONSTRAINT ON THE PRIMARY KEY


  • Impossible Mission - B

    @darkmatter ok, maybe I misread then. The first thing I thought when reading that was, "if you copy the data over from the corrupted table into the new table, the new table will hold corrupted data, (or trigger a constraint violation when you try,) so how does this actually help at all?"



  • @Scarlet_Manuka said in Helpful error message:

    @Jaloopa It's a number(5, 2) column, i.e. numeric with 3 digits before the decimal point and 2 after. That's the way Oracle number columns are specified, not in terms of any particular number of bits, so 999 (or in this case 999.99) is quite a normal sort of limit. (Well, it was (5, 2); it's (7, 2) now, at least in the test environment.)

    As I read somewhere (I think in the docs) it doesn't store bits. It's a BCD format. You can get a length byte, a sign byte, an exponent byte, and some number of mantissa bytes (1 byte per 2 decimal digits). And I don't think it actually uses odd exponents so 1.1 would require 2 mantissa bytes.


  • :belt_onion:

    @masonwheeler said in Helpful error message:

    if you copy the data over from the corrupted table into the new table, the new table will hold corrupted data, (or trigger a constraint violation when you try,) so how does this actually help at all?"

    assume that i bothered to fix the whole not-unique data thing by way of an ETL process in between those steps :)



  • @darkmatter said in Helpful error message:

    The biggest Oracle problem we ever had was where a unique partitioned index on a large table corrupted and was no longer unique (HOW THE FCK?)

    Reminds me of an issue we encountered shortly after we started using an Exadata machine. Certain queries would execute normally from our own machines, but our Informatica jobs would sometimes get all the records and sometimes get only some of the records. Turned out to be, if I remember properly, cell indexes being corrupted - the immediate workaround was to rebuild the affected indexes (they were able to give us a way to identify which indexes were corrupted) and the medium-term workaround was to add a special setting to our session initialisation which stopped the problem from recurring.


  • :belt_onion:

    @heterodox said in Helpful error message:

    @masonwheeler said in Helpful error message:

    Sometimes you just need to think about whether what you're about to say is fundamentally absurd in the context of known facts. And sometimes people fail to do that.

    :man:: "E-mail's down."
    :telephone:: "Please use <this> e-mail address to create a ticket in the ops' ticketing system."
    :man:: "..."

    Sort of like when your network connection isn't working, and you can't figure out why, and Windows' suggestion is 'search for help online'.



  • @El_Heffe said in Helpful error message:

    @heterodox said in Helpful error message:

    @masonwheeler said in Helpful error message:

    Sometimes you just need to think about whether what you're about to say is fundamentally absurd in the context of known facts. And sometimes people fail to do that.

    :man:: "E-mail's down."
    :telephone:: "Please use <this> e-mail address to create a ticket in the ops' ticketing system."
    :man:: "..."

    Sort of like when your network connection isn't working, and you can't figure out why, and Windows' suggestion is 'search for help online'.

    Here's one I haven't seen yet but probably will soon: "The wi-fi password is on our website."



  • Does this fit in this thread? From a YouTube page:
    0_1497913513854_736e9c41-68d6-4bb1-9977-0d97f4acb596-image.png
    I love the line "Please make sure it wasn't preloaded for nothing."



  • @LB_ said in Helpful error message:

    Does this fit in this thread? From a YouTube page:
    0_1497913513854_736e9c41-68d6-4bb1-9977-0d97f4acb596-image.png
    I love the line "Please make sure it wasn't preloaded for nothing."

    Looks like YouTube is trying to pre-fetch DNS or something.



  • @RaceProUK said in Helpful error message:

    @Scarlet_Manuka said in Helpful error message:

    the customer who is 1196.05 years old

    Glad to see Enoch Methuselah is still around :slight_smile:

    FTFY, unless I :whoosh:ed on some other reference.


  • SockDev

    @djls45 said in Helpful error message:

    @RaceProUK said in Helpful error message:

    @Scarlet_Manuka said in Helpful error message:

    the customer who is 1196.05 years old

    Glad to see Enoch Methuselah is still around :slight_smile:

    FTFY, unless I :whoosh:ed on some other reference.

    Methusela died, but Enoch is still alive



  • @RaceProUK said in Helpful error message:

    @djls45 said in Helpful error message:

    @RaceProUK said in Helpful error message:

    @Scarlet_Manuka said in Helpful error message:

    the customer who is 1196.05 years old

    Glad to see Enoch Methuselah is still around :slight_smile:

    FTFY, unless I :whoosh:ed on some other reference.

    Methusela died, but Enoch is still alive

    Oh, derpy-dur. :facepalm: I should've caught that.



  • @RaceProUK said in Helpful error message:

    @Scarlet_Manuka said in Helpful error message:

    the customer who is 1196.05 years old

    Glad to see Enoch is still around :slight_smile:

    Aaaand it turns out that (by my guess, anyway, having had a look at the other values in the column) the "age of customer" field is being measured in months.

    :whywouldyoudothat.jpg:

    (and no, babies or young children would definitely not be customers for this item)


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.