Seriously, WTF?

[Toby Corkindale]

On Tue, May 06, 2008 at 07:43:52AM +0100, MacGyveR wrote:
> On Tuesday 06 May 2008, Toby Corkindale wrote:
> > 1)
> > MySQL lets you set DEFAULT values for everything *except* DATETIME.. WTF?
> > You *can* have TIMESTAMP default to something, however it seems to return
> > different formats of data depending on the versions of MySQL you're
> > running, thus confounding attempts to work on multiple versions (and
> > breaking DateTime::Format::MySQL too).
> > How do you guys deal with this? Am I missing something stupid?
> >
> > http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
> >
> >
> > 2)
> > MySQL's 'REPLACE INTO' command. What were they thinking?
> > I suppose it seemed like a good idea at the time, but try this at home
> > kids: create table A. Create table B which has a foreign key into table A,
> > with 'on delete cascade' set. Nowever do a REPLACE INTO table A for an
> > existing row.. Note that table B has now deleted all related rows.
> > Wonderful!
> >
> >
> > (I've been having to work with MySQL for over a year now and it STILL keeps
> > coming up with new ways to fall to pieces when I least expect it.. On the
> > other hand, I don't think Postgres (my preferred DB) would do the sort of
> > replication needed at this company. When oh when will they sort that out?)
> 
> 1) you could use a trigger so solve your date problem, but that is a bit icky 
> (mysql 5+)

Sadly, I'm stuck on the abhorrent-in-the-eyes-of-all 4.0.xx versions; I
want to try and remove obstacles to upgrading one day when the higher powers
allow though, and so using TIMESTAMP is ruled out as its behaviour and format
changes between almost every version of 3/4.0/4.1/5.0/etc!
I wish they'd fixed the DATETIME DEFAULT issue in 5.0 actually, as it might
have been a help to push towards upgrading to it.

> 2) i thought the docs state that replace is just a delete(if needed) then 
> insert using the primary key given. In that case it behaves the same was as 
> if the two commands were issued separately. but i completely agree here, a 
> sort of half feature that can't be used anywhere sensible :-(

It's like they realised that they didn't have decent transaction support, so
made an atomic delete/insert method instead.
And then poor fools used MySQL, and used these non-standard commands as the
only way to do things, and then poor fools like me came along and had to
support it later.

Toby