Re: Getting started with Postgres or MySQL
On Wednesday 31 January 2007 14:20, Ron Johnson wrote:
> On 01/31/07 16:50, Joshua J. Kugler wrote:
> > On Wednesday 31 January 2007 13:19, Roberto C. Sanchez wrote:
> >>> How do I get started here?
> >>
> >> First. Please do not use MySQL, unless you don't care about your data.
> >
> > Please stop this MySQL vs. PostgreSQL bashing. Each has their place. If
> > users of MySQL don't care about their data, then I guess Bayer, Colgate,
> > Ensembl Genome Browser, Genome Sciences Center (GSC), The Institute for
>
> [snip]
>
> > Feedster, Flickr, Freshmeat.net, Technorati, Wikipedia, and YouTube
> > (among many others, see http://www.mysql.com/customers/ ) don't care
> > about their data. These are companies that live and die by their data,
> > I'm sure they care about it very much. Each has their place. MySQL has
> > come a long way since its 3.23 days. Read the manual. Form your own
> > opinions. A lot has changed.
>
> I supplied a link to the official MySQL docs. I'll quote from them
> now, if you'd like:
>
> http://dev.mysql.com/doc/refman/5.0/en/constraint-invalid-data.html
> MySQL allows you to store certain incorrect date values
> into DATE and DATETIME columns (such as '2000-02-31' or
> '2000-02-00'). The idea is that it's not the job of the
> SQL server to validate dates. If MySQL can store a date
> value and retrieve exactly the same value, MySQL stores
> it as given. If the date is totally wrong (outside the
> server's ability to store it), the special ?zero? date
> value '0000-00-00' is stored in the column instead.
In that same document, they give the reason for doing so:
"The reason for using the preceding rules in non-strict mode is that we can't
check these conditions until the statement has begun executing. We can't just
roll back if we encounter a problem after updating a few rows, because the
storage engine may not support rollback. The option of terminating the
statement is not that good; in this case, the update would be “half done,”
which is probably the worst possible scenario. In this case, it's better
to “do the best you can” and then continue as if nothing happened."
And also provide a way (from 5.0.2 on) of enabling the "traditional" strict
behavior. So, with one config option, MySQL will now reject all invalid data
(providing you're using transactional engines, for reasons described above).
As for your assertion that those who spec MySQL don't know what they're doing,
I do believe the techs that work at MySQL are some pretty sharp cookies. I
think they know what they're doing, and I'm sure they know how to either
enable the above config option, or check their data before it gets to their
database.
All products have their gotchas. One should be familiar with the product
against which one is writing, or weird things will bite you down the road.
j
--
Joshua Kugler
Lead System Admin -- Senior Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE
PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111
Reply to: