MySQL Bugs: #73347: TIMESTAMP Initialization and the NULL Attribute

Bug #73347	TIMESTAMP Initialization and the NULL Attribute
Submitted:	21 Jul 2014 17:00	Modified:	28 Jul 2014 21:39
Reporter:	John Smith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6.19-log MySQL Community Server (GPL)	OS:	Linux (Red Hat Enterprise Linux Server release 6.4 (Santiago))
Assigned to:		CPU Architecture:	Any
Tags:	insert, null, timestamp

Description:
According to Oracle's MySQL documentation,  you can initialize or update any TIMESTAMP column to the current date and time by assigning it a NULL value, unless it has been defined with the NULL attribute to permit NULL values. However, this does not seem to working. 

show create table timetest;
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| timetest | CREATE TABLE `timetest` (
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------+

INSERT timetest (updatedAt) VALUE(NULL);
ERROR 1048 (23000): Column 'updatedAt' cannot be null

I tested in MySQL 5.5 and it works as expected. 

How to repeat:
Using MySQL 5.6.19, 

create a table with a timestamp column that does not allow nulls and defaults to the current timestamp.
Insert a column into the table specifying null.

Thank you for the report.
I cannot repeat described behavior with reported/later versions.

## 5.6.19

### with sql mode disabled

mysql> use test
Database changed
mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE TABLE `timetest` (
    ->   `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT timetest (updatedAt) VALUE(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from timetest;
+---------------------+
| updatedAt           |
+---------------------+
| 2014-07-24 02:26:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

### with default sql_mode

mysql> set sql_mode=default;
Query OK, 0 rows affected (0.00 sec)

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

mysql> drop table timetest;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `timetest` (   `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT timetest (updatedAt) VALUE(NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from timetest;
+---------------------+
| updatedAt           |
+---------------------+
| 2014-07-24 02:57:24 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from timetest;
+---------------------+
| updatedAt           |
+---------------------+
| 2014-07-24 02:57:24 |
+---------------------+
1 row in set (0.00 sec)

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

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

mysql> CREATE TABLE `timetest` (
    ->   `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT timetest (updatedAt) VALUE(NULL);
Query OK, 1 row affected (0.04 sec)

mysql> select * from timetest;
+---------------------+
| updatedAt           |
+---------------------+
| 2014-07-24 01:48:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.21-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

Would you be able to try with the community edition?

Both are same, will test and let you know.

Thanks,
Umesh

I just did a fresh install of MySQL 5.6.19 (community) and am having the same issue: 

[04:21 PM] [root@servername] ~ $ mysql -A 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94675
Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

dbadmin@localhost[(none)]> use test;
Database changed
dbadmin@localhost[test]> CREATE TABLE `timetest` (
    ->   `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

dbadmin@localhost[test]> INSERT timetest (updatedAt) VALUE(NULL);
ERROR 1048 (23000): Column 'updatedAt' cannot be null
dbadmin@localhost[test]>

I am closing this ticket. I discovered the issue. The problem is due to the explicit_defaults_for_timestamp variable. When this is turned on it prevents MySQL from being able to use NULL to set the default.