Welcome to the second installment in a series on MySQL Gotchas. Part one addressed the MySQL Numeric Overflow Gotcha. Today’s article will explore the perils of working with dates in MySQL.
Gotchas for the Uninitiated
People are not always clear on the difference between a gotcha and a bug. A bug is a flaw in a program or system that causes it to produce incorrect and/or unexpected results whereas a gotcha is a feature or function, which does indeed work as the author intended, but not as we the Database Administrators (DBAs) might expect it to. In other words, the makers of the product designed it with certain assumptions that may not align with those of some or perhaps most users of the product.
One of the most fertile sources of gotchas is a design philosophy that espouses a relaxed data and/or syntax checking style that allows bad data and/or operations to occur. The idea behind this approach is sound; by minimizing the amount of error throwing, the designers open the door for more batch processing while reducing the need for human intervention. The trade-off is that relaxed checking by the tool transfers the burden of validation to the developer or application that interacts with it.
Date Handling in MySQL
MySQL espouses a “relaxed” approach to date handling by automatically converting numbers and strings into a proper Date when inserting into or updating a DATE, DATETIME, or TIMESTAMP column. Although MySQL is fairly rigid in terms of string date formats – “yyyy-mm-dd” is the only acceptable format - any punctuation character(s) may be used as the separator between date parts. For example, “2004-08-15” and “2004!!!08!!!15” are both valid. In fact, MySQL will happily accept a date string that contains no separators at all, such as “20130410”.
Let’s put MySQL’s date handling philosophy to the test by creating a test table:
CREATE TABLE `datetest` ( `id` int(11) NOT NULL, `a_date` date NOT NULL DEFAULT '1970-01-01', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
A salient point with regards to our table is that, although it does not accept null values, it nevertheless still has a default date. You might recognize it as the UNIX epoch date. The idea is that it will take the place of invalid dates (at least in theory). In actual fact, there doesn’t appear to be any condition that will set the a_date column value to our default date.
Here are four test records, where the first contains a valid date, the second an empty string, the third a string of spaces, and the fourth, a string which contains the word “null”:
1, ‘2012-02-21’ 2, ‘’ 3, ‘ ‘ 4, ’null’
The first record goes in without a hitch, but the next three insert the special MySQL zero date of “0000-00-00” – not quite what we expected!
It should be noted that MySQL does raise a warning when an invalid date is encountered:
INSERT INTO datetest VALUES (2, ''); 1 row(s) affected 1 warning(s): 1265 Data truncated for column 'a_date' at row 2 0.031 sec
Tightening the Reins on Date Permissions
If you’re not a fan of the zero date, there is some recourse to get rid of them.
The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date, but its effect is highly dependent on whether or not strict mode is enabled. If NO_ZERO_DATE is set while not in strict mode, '0000-00-00' is permitted but a warning is produced:
INSERT INTO datetest VALUES (5, '0000-00-00') 1 row(s) affected 1 warning(s): 1264 Out of range value for column 'a_date' at row 1 0.000 sec
If NO_ZERO_DATE is enabled while in strict mode, '0000-00-00' is not permitted so that attempting to insert it produces an error. Moreover, an error will also result if the date is invalid in any way, for instance a blank string, or having a day/month combination of Feb 31 st.
Note that, as of MySQL 5.7.4, the NO_ZERO_DATE mode does nothing. It’s been rolled into strict SQL mode. In MySQL version 5.6, a deprecated warning appears:
SET sql_mode = 'NO_ZERO_DATE'; 0 row(s) affected 1 warning(s): 1681 'NO_ZERO_DATE' is deprecated and will be removed in a future release. 0.000 sec
The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is non-zero but the month or day part is 0. Hence, this mode affects dates such as '2014-00-01' or '2014-01-00', but not '0000-00-00'. The effect of NO_ZERO_IN_DATE also depends on whether strict mode is enabled.
Like NO_ZERO_DATE, as of MySQL 5.7.4, the NO_ZERO_IN_DATE mode does nothing and has been rolled into strict SQL mode.
Prior to version 5.0.2, the server merely required that month and day values be in the range 1 to 12 and 1 to 31, respectively, regardless of the month. When strict mode was disabled, dates such as '2013-02-31' were handled like any other invalid date, that is to say that they were converted to '0000-00-00' and a warning was generated. By default, when strict mode is enabled in version 5.6, invalid dates generate an error, but this behavior may be overridden by enabling ALLOW_INVALID_DATES.
Why would anyone want to allow such dates? One possible reason is to allow applications to store the year, month, and day in three different fields or to store date parts such as month and year.
TRADITIONAL SQL Mode
You can also forgo the whole loosey-goosey handling of dates by enabling TRADITIONAL SQL Mode. It combines STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER to emulate the behavior of stricter database servers.
SET SESSION sql_mode = 'TRADITIONAL'; INSERT INTO datetest VALUES (7, '0000-00-00') Error Code: 1292. Incorrect date value: '0000-00-00' for column 'a_date' at row 1 0.015 sec
In today’s article, we saw how MySQL’s “relaxed” rules for inputting dates can lead to potential gotchas. That was followed up with how to turn off MySQL’s default date handling to match that of other database providers.