MySql - stores '0000-00-00 00:00:00' in place of default date value

Question

I have a table in mysql database with a datetime column. The default value of this column is set to '1900-01-01 00:00:00'.

However I have noticed that out of 4000 rows inserted, 1 row has this value set at '0000-00-00 00:00:00". I am very much confused about how this can happen despite of setting the default value.

Is this a known bug or issue in MySql? This happens approximately once in 4000 rows. Can anyone provide any help here?

Answer 1

If your table looks like this:

create table foo (
  name VARCHAR(50),
  dob DATETIME DEFAULT '1900-01-01 00:00:00'
);

... it should work just fine.

You should note that default value does not imply mandatory. You can still insert a different value or update the row to your liking:

insert into foo (name) values ('Jim'), ('Joe'), ('Jack');
insert into foo (name, dob) values ('Jill', '1999-12-31 23:59:59');
update foo set dob = current_timestamp where name='Jack';

That's what probably happened.

As of having a value of 0000-00-00 00:00:00, it's known bug/feature that MySQL offers. Depending of your SQL mode, you're allowed to store invalid or incomplete dates.

Edit: To avoid the insertion of 0000-00-00 dates you need to harden the SQL mode:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE foo SET dob='invalid' WHERE name='Jim';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE foo SET dob='invalid' WHERE name='Joe';
ERROR 1292 (22007): Incorrect datetime value: 'invalid' for column 'dob' at row 2
mysql>
mysql> SELECT * FROM foo;
+------+---------------------+
| name | dob                 |
+------+---------------------+
| Jim  | 0000-00-00 00:00:00 |
| Joe  | 1900-01-01 00:00:00 |
| Jack | 1900-01-01 00:00:00 |
+------+---------------------+
3 rows in set (0.00 sec)