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>
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>