MySQL Invalid default value for timestamp when no default value is given.

Question

Look at the following sql.

CREATE SCHEMA IF NOT EXISTS `scheduler`;
USE `scheduler` ;
CREATE TABLE IF NOT EXISTS `scheduler`.`JobHistory` (
  `Id` INT NOT NULL AUTO_INCREMENT,
  `Job` INT NOT NULL,
  `StartTime` TIMESTAMP NOT NULL,
  `FinishTime` TIMESTAMP NOT NULL,
  PRIMARY KEY (`Id`),
  INDEX `fk_JobHistory_Job_idx` (`Job` ASC));

It is throwing ErrorCode: 1067. Invalid default value for 'Finish Time' But I'm not giving any default value for finish time, also there is another time stamp StartTime which is exactly same and I'm not getting any exception for that one.

Answer 1

Although @jsnplank is right that timestamps are treated differently and you should consider using datetime datatype for these 2 particular columns, however, he fails to explain the error message.

The error message is most likely the result of a combination of how mysql treats timestamp fields when no default value is provided and your sql mode settings.

1. You define both timestamp columns as not null, without any specific default value set. This means that the 1st timestamp column's default value will be current_timestamp() and will also be updated to current_timestamp() whenever the record changes. This is why the 1st timestamp field does not generate an error message, no matter which of the 2 is the 1st one.

   However, the 2nd not null timestamp column's default value will be '0000-00-00 00:00:00' if you do not explicitly define a default value.

   See this blog post for more details.

2. Probably no_zero_date sql mode is also enabled on your server either explicitly or as part of strict sql mode. This sql mode generates an error if you want set '0000-00-00 00:00:00' as a default value or would like to insert this value into any date field.

So, you can use timestamp data type in your table, but make the 2nd one either nullable or provide 0 or any valid date (such as the epoch) as an explicit default value.

Since you are marking start and end dates with these fields, uding datetime instead of timestamp as datatype may be a good idea.