Coding Horrors: MySQL, Dates and Times
An excerpt from MySQL Reference Manual:
Illegal DATETIME, DATE, or TIMESTAMP values are converted to the “zero” value of the appropriate type (’0000-00-00 00:00:00′ or ‘0000-00-00′).
What the fuck. Really, come on. The purpose of a database is to store valid and integral data. Again. Valid. And. Integral. Data. It is valid, integral and meaningful that counts. Not just any rubbish. If I want rubbish, I can just read back random bytes from the harddisk. If someone tries to write crap into a database, which the database cannot handle, the database must refuse the operation, and give error.
Really, I wonder what the hell designers of MySQL were thinking when they implemented this specific feature. Pardon me, the word “feature” is not appropriate. The correct word would be “disaster”. It is a disaster indeed, because it silently causes data loss. One might argue that it is developers’ responsibility to validate their data, and make sure they provide the database with valid input. While this is definitely true, it is no excuse for the database to accept graciously any garbage, and on top of that convert it to something which has nothing to do with what it is supposed to be. That is a major fail in design, and major fail to achieve a primary goal.
The behavior above is true for MySQL versions from 4.1 to 6.0.
This little story only proves that MySQL is only good as a backend for small and simple websites. If you mean serious business, please reconsider. There are a bunch of free databases out there, all of them much, much better and more mature. Some of them even open source.
And yes, I know there is a general setting in MySQL 5.02 and greater to make it give errors. And yes, I don’t think it makes the fail lesser.
May 27th, 2009 at 5:30 pm
Where is the problem? If you do not like it, stop it. It is so simple.
Also you can use triggers to do extra validation.
This is supported for compability.
In general, you have to check values in your code, or in your data base. Some times in both places. If you do a good check, this will never become an issue.
If your code send data to data base, whitout validation, and database reise error, then you must have a code that handle this situation. I think will be much bether, to have a validation before send data to the database.
I am agree thet this is not a very nice think, but if you folow good programing practices, this will never be an issue.
May 28th, 2009 at 10:27 am
Gatakka, you didn’t read my post carefully, did you? Not only you failed to see the point, but you also rebuff your first statement with your last one.
Let me go though it again. One of the primary functions of a database management system (DBMS) is to guarantee the integrity of the data. That means to keep data immutable during storage, retrieval and copying. Inserting arbitrary values when input data cannot be handled is a severe breach of the integrity rules.
This is basic computer science. The problem is a failure in the very design of MySQL. No talks about “good programming practices” can change that.
Compatibility is weak excuse for me in this case, too because compatibility is not the Holy Graal for MySQL, either. Just consider the password function, for example.