MySQL Bugs: #80086: Timestamp cannot accept NULL values anymore to re-initialize value

Bug #80086	Timestamp cannot accept NULL values anymore to re-initialize value
Submitted:	21 Jan 2016 0:03	Modified:	21 Jan 2016 16:55
Reporter:	Georald Camposano	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.10	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any

Description:
Just curious but has this been a change in behavior from previous versions? On an older dev setup I used to have (using 5.6), I was always able to easily get timestamp fields to re-initialize themselves to the current date/time by passing a NULL value to them. Now, doing the same thing makes MySQL complain about my timestamp column not being able to accept a null value.

How to repeat:
Create a table with a timestamp column set not to accept NULL values

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `my_timestamp_column` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1;

Issue an insert statement to populate the table with at least 1 record

INSERT INTO `test` (`my_timestamp_column`) VALUES (NOW())

Try to issue an update on the created row, this time using NULL to populate our timestamp column

UPDATE `test` SET `my_timestamp_column` = NULL  WHERE `id` = 1

Update query produces the following error: (1048: Column 'my_timestamp_column' cannot be null)

Hmm, this may not even be a bug. Did a bit of research and found out about server sql modes

http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

I wonder if the change in behavior I am used to is related to a specific strict mode being enabled by default.

Hello Georald,

Thank you for the report.
This is an expected behavior, - Strict SQL mode for transactional storage engines (STRICT_TRANS_TABLES) is now enabled by default in 5.7, and in 5.6 is was ''. https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition.

Thanks,
Umesh

Thanks, that makes sense. Does this also affect updates though? I tried to run an update query to change all 0000-00-00 00:00:00 values from a timestamp column to null (I changed it to allow null values) and it is still preventing me with the same error

UPDATE `test` SET `my_timestamp_column` = NULL  WHERE `my_timestamp_column` = "0000-00-00 00:00:00"

A select query works though with that WHERE statement

Sorry, I just realized my comment was talking about something slightly different but it still seems to be affected by strict sql mode being enabled. Our production server's mysql server doesn't seem to have strict sql mode enabled so it was allowing datetime and timestamp values of "0000-00-00 00:00:00" to go in. I wanted to fix these columns after importing the database to my dev machine (which has mysql 5.7.10) and change them to NULL for invalid values but I get an error about these columns having an incorrect value with my update script above, eventhough the value was used only in the WHERE statement

Error Code: 1292. Incorrect datetime value: '0000-00-00 00:00:00' for column 'my_timestamp_column'