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
