Bug #94062 ERROR 1067 (42000): Invalid default value for 'created_date'
Submitted: 25 Jan 2019 13:05 Modified: 26 Jan 2019 7:57
Reporter: Rodrigo Guariento Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.25-0ubuntu0.18.04.2 OS:Ubuntu (18.04.2)
Assigned to: CPU Architecture:Other (x64)
Tags: 0000-00-00 00:00:00, default value, error, timestamp

[25 Jan 2019 13:05] Rodrigo Guariento
Description:
1)

When we try to create a field TIMESTAMP not NULL with default value 0000-00-00 00:00:00 as I'm used to create, it's displayed an error.

Any other database version this doesn't occur.

This database that displays the error is a default package that comes with Ubuntu 18.04 LTS, installed with: $ sudo apt install mysql-server

2)

Other thing: if we remove the DEFAULT '0000-00-00 00:00:00' from the code below, the CREATE TABLE works but a Default is generated automatically with the content CURRENT_TIMESTAMP and Extra with "ON UPDATE CURRENT_TIMESTAMP".

How to repeat:
1)

mysql> CREATE TABLE `mydatabase`.`whatsnew` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `title` VARCHAR(100) NOT NULL,
    ->   `description` LONGTEXT NOT NULL,
    ->   `created_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
    ->   `sent_date` TIMESTAMP NULL,
    ->   PRIMARY KEY (`id`));
ERROR 1067 (42000): Invalid default value for 'created_date'
mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.7.25-0ubuntu0.18.04.2 |
+-------------------------+
1 row in set (0.00 sec)

2)

mysql> CREATE TABLE `mydatabase`.`whatsnew` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `title` VARCHAR(100) NOT NULL,
    ->   `description` LONGTEXT NOT NULL,
    ->   `created_date` TIMESTAMP NOT NULL,
    ->   `sent_date` TIMESTAMP NULL,
    ->   PRIMARY KEY (`id`));
Query OK, 0 rows affected (0.02 sec)

mysql> desc whatsnew;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field        | Type         | Null | Key | Default           | Extra                       |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| id           | int(11)      | NO   | PRI | NULL              | auto_increment              |
| title        | varchar(100) | NO   |     | NULL              |                             |
| description  | longtext     | NO   |     | NULL              |                             |
| created_date | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| sent_date    | timestamp    | YES  |     | NULL              |                             |
+--------------+--------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql>
[26 Jan 2019 7:57] MySQL Verification Team
Hello Rodrigo,

Thank you for the report.
Imho this is duplicate of Bug #75439, please see Bug #75439

regards,
Umesh