MySQL minimum timestamp not allowed

If you need to set a default for a timestamp field and your server settings don’t allow the use of a zero timestamp (see my article on NO_ZERO_DATE) then the next best option is ‘1970-01-01 00:00:01’. I was trying to do this recently and the server was preventing the creation of a table

(simplified version)

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` timestamp NOT NULL DEFAULT '1970-01-01 00:00:01',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

ERROR 1067 (42000): Invalid default value for 'created'

It took some digging to sort out the problem, in the end I realised in one of several config files there was a directive default-time-zone=Europe/London

The only difference is UTC does not use daylight savings time.
Changing this to default-time-zone=UTC and double checking in MySQL.

mysql> show variables like '%time_zone%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| system_time_zone | UTC   |
| time_zone        | UTC   |
+------------------+-------+
2 rows in set (0.00 sec)

And the test table above was created fine.