MySQL Bugs: #41137: TIMESTAMP column with no default value

Bug #41137	TIMESTAMP column with no default value
Submitted:	30 Nov 2008 16:17	Modified:	16 Jan 2014 18:01
Reporter:	Viktor Štujber	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
For some reason, it seems MySQL won't let you create a TIMESTAMP column with no default value. If you omit the DEFAULT part of the ADD COLUMN query, MySQL automatically fills in "default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP". This is different from other column types where not specifying a default makes the column mandatory in INSERT statements.

If you're using a TIMESTAMP column to just store date/time information (as a timezone-aware alternative to DATETIME), having a forced default value might not be what you want.

How to repeat:
Try
ALTER TABLE `test` ADD `Timestamp` TIMESTAMP NOT NULL;

Then
SHOW CREATE TABLE `test`;

And
see that MySQL added the abovementioned default & on-update attribute.

Many thanks for writing a bug report.

Which MySQL version did you use?

This was last tried on MySQL 5.0.75 (FreeBSD).
I do not have 5.1 or higher available for testing, sorry.

Many thanks for writing a feature request.

The behaviour of timestamp is documented. Just an example you will find here:

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

"In a CREATE TABLE  statement, the first TIMESTAMP column can be declared in any of the following ways: "
...
"With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP."

Indeed, that's why it is a feature request and not a bugreport :)

Summary: It is impossible to have a TIMESTAMP column without default value. Sometimes, I might not want some of my timestamp columns to have a default value.
Rationale: All other column types have normal consistent behavior w.r.t. default values - if you don't specify it, there isn't any, and the column cannot be omitted in insert queries.

Any chance we can reopen this? It'd be nice to be able to have a TIMESTAMP field that was required to be provided at insert time.

Seems to be a very old bug, not sure why its not fixed.....
Workaround I found was:

ALTER TABLE `table_name` MODIFY COLUMN `column_name` TIMESTAMP NOT NULL DEFAULT 0;
ALTER TABLE `table_name` ALTER  COLUMN `column_name` DROP DEFAULT;

It seems this is addressed in MySQL 5.6.6 via the 'explicit_defaults_for_timestamp' option and the deprecation notice on each server startup. Yay, finally. Note that the option only affects newly created columns.