MySQL Bugs: #92551: timestamp column not null without default value become default current timestamp

Bug #92551	timestamp column not null without default value become default current timestamp
Submitted:	24 Sep 2018 22:25	Modified:	25 Sep 2018 6:40
Reporter:	mohamed atef	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.23	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	timestamp column not null without default value become default current timestamp

Description:
in server 5.7.23
when i create table with column timestamp not null without default value
the server create it as not null default current_timestamp on update current_timestamp
but in server 8.0.12 
it created without default value

How to repeat:
in server 5.7.23
create database if not exists db charset 'utf8' collate 'utf8_unicode_ci';
use db;
drop TABLE if exists `tbl1`;
use db;
CREATE TABLE `tbl1` (
  `Id` int unsigned NOT NULL,
  `CreatedAt` timestamp not NULL,
 
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

show create table tbl1;

### result is
CREATE TABLE `tbl1` (
  `Id` int(10) unsigned NOT NULL,
  `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

but in server 8.0.12

create database if not exists db charset 'utf8' collate 'utf8_unicode_ci';
use db;
drop TABLE if exists `tbl1`;
use db;
CREATE TABLE `tbl1` (
  `Id` int unsigned NOT NULL,
  `CreatedAt` timestamp not NULL,
 
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

show create table tbl1;

### result is
CREATE TABLE `tbl1` (
  `Id` int(10) unsigned NOT NULL,
  `CreatedAt` timestamp NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

ddl

Hello mohamed atef,

Thank you for the report.
Imho this is a documented behavior i.e If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

*
No TIMESTAMP column is automatically declared with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. Those attributes must be explicitly specified.

Please see https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html

explicit_defaults_for_timestamp - Default Value (>= 8.0.2) is ON, Default Value (<= 8.0.1) is OFF which can be easily confirmed with:

- 5.7.23

mysql> CREATE TABLE `tbl1` (
    ->   `Id` int unsigned NOT NULL,
    ->   `CreatedAt` timestamp not NULL,
    ->
    ->   PRIMARY KEY (`Id`),
    ->   KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
    ->
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table `tbl1`\G
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `Id` int(10) unsigned NOT NULL,
  `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

- 8.0.12 (lowest version checked 8.0.4)

mysql> show create table `tbl1`\G
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `Id` int(10) unsigned NOT NULL,
  `CreatedAt` timestamp NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)

mysql> show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

So, if you set --explicit_defaults_for_timestamp=ON for 5.7.23:

mysql> use db;
Database changed
mysql> CREATE TABLE `tbl1` (
    ->   `Id` int unsigned NOT NULL,
    ->   `CreatedAt` timestamp not NULL,
    ->
    ->   PRIMARY KEY (`Id`),
    ->   KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
    ->
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table `tbl1`\G
*************************** 1. row ***************************
       Table: tbl1
Create Table: CREATE TABLE `tbl1` (
  `Id` int(10) unsigned NOT NULL,
  `CreatedAt` timestamp NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `CreatedAtidx` (`CreatedAt`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.01 sec)

regards,
Umesh