So, this morning one of the production DB's went down. The DBA's didn't tell us what happened, but it was down until sometime around lunch time.

About 2 hours after everything had been restored to normal, I got a call from my boss, telling me that my app had barfed for unknown reasons. After checking out the appserver logs, I found this:

</p><p>com.sybase.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '1568316946041014268' to a NUMERIC field .
</p><p>

Shit. No way that field could get so high, it was around 1984 last time I checked. This was on an insert, and the overflowed field came from a related table. So I trace that value back to the source, and I find this "value" on the identity PK:

50000000000000000000000000000002007

Damn. That thing looks like either the GDP for every single country in the planet, or the US national debt. I don't even know how to read the damn number (50 million yotta-somethings?) and I'm pretty sure this is the problem. Sure enough, I find that this is way over the upper limit for a signed Long (64-bit integer) so we take action.

The sad thing is that Sybase likes to do this whenever it goes down, and this identity had been already limited in its jump increments. This "jump" should've been in 10,000 increments, not ... whatever that number is.

What irks me is that it isn't the only issue with this DBMS. PreparedStatements sometimes fail silently without any errors, or convert numbers to 0's. Urrrgh....

danixdefcon5:

50000000000000000000000000000002007

Damn. That thing looks like either the GDP for every single country in the planet, or the US national debt.

Or the number of Pesos in a Dollar.  Or the number of Dollars in an Amero.

 

Oh, and this should probably be in Sidebar, not GD. 

morbiuswilters:

danixdefcon5:

50000000000000000000000000000002007

Damn. That thing looks like either the GDP for every single country in the planet, or the US national debt.

Or the number of Pesos in a Dollar.  Or the number of Dollars in an Amero.

 

Oh, and this should probably be in Sidebar, not GD. 

 

it's obviously the number of fetish sites on the internet.

That's five septillion, two thousand and five.

Five-buncha-zeroes-two-thousand-and-seven.

danixdefcon5:

</p><p>com.sybase.jdbc.SybSQLException: Arithmetic overflow during implicit conversion of NUMERIC value '1568316946041014268' to a NUMERIC field .
</p><p>
Shit. No way that field could get so high, it was around 1984 last time I checked.

Well, if it's been 24 years since you checked that field I'm not surprised it's higher than you thought...

henke37:

That's five septillion, two thousand and seven.

FTFY

Got to love IDENTITY BURNING SET FACTOR. What's really cool about it is that is specifies the percentage of the max ids to burn on restart. So if you have a numeric(20) and IDENTITY BURNING SET FACTOR at its minimum (0.01 IIRC), it would add 10^16 + 1 and insert it. So you have: 1 2 3 10000000000000001 10000000000000002 10000000000000003 20000000000000001 I've spent MANY hours fixing this. After the 3rd or 4th time it happened, I built a script to automatically backup, drop, and rebuild the tables. In the version 11 release we were using, there was no way to safely reset the identity counter without rebuilding the table. AWESOME. I will never willingly use Sybase again. I avoid MS SQL as well because of the common heritage. The page-level locks were a nice feature as well. Great for making an expensive server respond like a workstation. Also makes for easy deadlocks.

But MS SQL doesn't have any silly "features" like that.  Don't avoid it just because 10 years ago it had some of the same code as Sybase...

 It could be worse...you could also be using PowerBuilder...

Meh:

In the version 11 release we were using, there was no way to safely reset the identity counter without rebuilding the table. AWESOME.

Guess what release we're using. You just described the DBA's "solution" to this problem. Of course, they also set the IDENTITY BURNING to some low value so it won't do these wild jumps. In theory.

Meh:

I will never willingly use Sybase again.

I've been thinking that ever since I started working with this. Oh, and it shares the same lack of OFFSET/LIMIT support MSSQL has. (And still lacks, it seems MS doesn't seem to mind about that.)

Meh:

The page-level locks were a nice feature as well. Great for making an expensive server respond like a workstation. Also makes for easy deadlocks.

The appserver's full of SQLExceptions stating deadlocks. Fortunately, most of my code uses tables that aren't shared with any other apps, and it seems my app doesn't do deadlock-prone stuff, even when used by many concurrent users.

 

Waldo2k2:

 It could be worse...you could also be using PowerBuilder...

You know, our flagship product is written in it.... and I dread every day time I open the IDE. 

APH:

You know, our flagship product is written in it.... and I dread every day time I open the IDE.

 

Heh, ALL of our products are written in it (well, all but one). The only thing worse than the IDE itself, is using it alongside Visual Source Safe.

Makes me want to commit Hari Kari with my keyboard.

It's OVER NINE THOUSAAAAAAND!

danixdefcon5:

it shares the same lack of OFFSET/LIMIT support MSSQL has. (And still lacks, it seems MS doesn't seem to mind about that.)

 

[MS|T]-SQL has TOP. Less powerful than LIMIT, but it gets the same croppin' job done.

But supporting LIMIT would prevent lots of ID NOT IN statements. :\

jpaull:

henke37:

That's fifty decillion, two thousand and seven.

FTFY

FTFY (assuming short scale, UK long scale version: fifty thousand quintillion, two thousand and seven, EU long scale version: fifty quintilliard, two thousand and seven)

dhromed:

danixdefcon5:

it shares the same lack of OFFSET/LIMIT support MSSQL has. (And still lacks, it seems MS doesn't seem to mind about that.)

 

[MS|T]-SQL has TOP. Less powerful than LIMIT, but it gets the same croppin' job done.

But supporting LIMIT would prevent lots of ID NOT IN statements. :\

 

SQL 2005and later have ROW_NUMBER() OVER (Partition BY Field, Order By Field) and RANK() OVER (Partition BY Field, Order By Field) which can then be used in the where statement.  Both are much more powerful than LIMIT.

 

lpope187:

SQL 2005 and later have ROW_NUMBER() OVER (Partition BY Field, Order By Field) and RANK() OVER (Partition BY Field, Order By Field) which can then be used in the where statement.  Both are much more powerful than LIMIT.

 

I am delighted and will invesitgate.

Meh:

Got to love IDENTITY BURNING SET FACTOR. What's really cool about it is that is specifies the percentage of the max ids to burn on restart. So if you have a numeric(20) and IDENTITY BURNING SET FACTOR at its minimum (0.01 IIRC), it would add 10^16 + 1 and insert it.

From the Sybase docs:

"The default value, 5000, represents .05%, or .0005 [of the available keyspace]."

Meh:

After the 3rd or 4th time it happened, I built a script to automatically backup, drop, and rebuild the tables.

From the same documentation, that's the best practice.

dhromed:

danixdefcon5:

it shares the same lack of OFFSET/LIMIT support MSSQL has. (And still lacks, it seems MS doesn't seem to mind about that.)

 

[MS|T]-SQL has TOP. Less powerful than LIMIT, but it gets the same croppin' job done.

But supporting LIMIT would prevent lots of ID NOT IN statements. :\

So does Sybase. Still, lack of OFFSET does require WTF-worthy stuff to emulate that feature.

TwelveBaud:

Meh:

After the 3rd or 4th time it happened, I built a script to automatically backup, drop, and rebuild the tables.

From the same documentation, that's the best practice.

Which is TRWTF. That's NOT a fucking best practice, it's a fucking workaround hack.