Off Topic, But A Good Read: Tracking Down A Crash That Only Happens On Wednesdays

I’ve chased bugs like this before. Wish I had documented some of them. :slight_smile:

http://gyrovague.com/2015/07/29/crashes-only-on-wednesdays/

Not off topic, IMO.

The moral of the story is never think that you can do a better job of creating a unique primary key than the database. You will always do a worse job than what the database can do on its own.

The corollary is to store all dates in SQLDateTime format. No exceptions.

I’d consider one caveat to that: If you need a unique ID for a table that is synchronized across multiple instances of a database, then you will always produce better unique identifiers than the database engine could ever hope to. UUID is your friend.

But yeah, store dates in the SQLDateTime format. I’m so glad that I mostly never have to deal with C type structures that you have to allocate the correct length for beforehand.

Horray for lazy programming!

UUID - usually unique ID :stuck_out_tongue:

Usually great but then your hot standby’s can’t communicate with the master of the cluster and you get duplicate UUID’s in one instance and … OMG !
Panic
Call the hardware & db vendor to figure out why they generated a duplicate
And then spend weeks fixing it :frowning:

That was long ago & far away but talk about painful for all parties involved

@Norman Palardy - stoppit. I’m within about 2 weeks of releasing the next generation (huge, massive, major upgrade) of our flagship product, and I’ve converted everything to use UUIDs instead of rowIDs because of database distribution.

Be gone with your curses and hexes. :slight_smile:

(I only need UUIDs to be unique across a given customer’s db schema, and in heavy testing both internal and in the hands of some early beta testers we’ve had 0 problems… knock on wood)

dum dee dum dee dum dee dum … :slight_smile:
sorry …

I’ve done a lot of fun things in my career and also a lot of things that at first glance make perfect sense

until they don’t :slight_smile:

in our case we found a suitable fix - basically appended the nodeid so the uuid even told us where it came from and we could be 100% sure they would never be duplicated even if the uuid might in some ultra remote case
so we would take the uuid and append “-” + nodeid (more or less)
that way when they could regain communications we could merge all the data & not have any duplicates - ever

Hmm… interesting approach. In my case, I’m using the UUIDs as keys for 2-way data synchronization between clients and a cloud server, so they need to match across all instances of a given customer’s schema… thus adding the node would not be an option.

Luckily, if I ever do run into a duplicate UUID situation, the damage it can cause will be limited to the ability to synchronize data, and I can manually adjust UUIDs as needed to triage that one case.

ours were basically a series of “hot standbys” that could all be used at any time and should one fail the entire group of them stayed up and the system remained up & responsive - just some data from a remote node might not be up to date

it wasn’t really a cluster in the sense of machine clusters like you might have with Vaxes

and when they could all communicate all data was synched n-ways (we had 50 or so servers in the group across the country)

clients picked the most local reachable server to use and would fall back to increasingly less & less local ones

all kinds of fun stuff :stuck_out_tongue:

[quote=203356:@Bob Keeney]Not off topic, IMO.

The moral of the story is never think that you can do a better job of creating a unique primary key than the database. You will always do a worse job than what the database can do on its own.

The corollary is to store all dates in SQLDateTime format. No exceptions.[/quote]

To which I would add that all dates stored should be UTC, or make sure that time zone information is included.

This assumes that the database can’t create the UUID for you. The times we’ve needed it we could get it from the database.

Depends on the situation, but yeah, timezones will come back to bite you if you’re not careful.

Definitely
Some DB’s have crummy abilities to deal with time or assume that all times are implicitly UTC
And then you get bit when you figure out thats the case and NOT tz info is saved unless you do it yourself OR normalize to UTC