Mysql – ERROR 1067 (42000) at line 7: Invalid default value for ‘LAST_UPDATED’

MySQLmysql-5.5

Running MySQL 5.5, got the error above when I tried to import a .sql file

I've checked the SQL_MODE of my server and nothing similar to 'NO_ZERO_DATE' is found

As well, I've took out the text of 'STRICT_MODE' from my my.ini file and restarted the server, yet this error still persists

Here's the .sql file being imported

CREATE TABLE  `street_segment` (
  `ID` int(8) NOT NULL,
  `PROVINCE_CODE` varchar(2) NOT NULL,
  `POSTAL_CODE` varchar(6) NOT NULL,
  `MUNICIPALITY_NAME` varchar(30) default NULL,
  `STATUS` varchar(16) NOT NULL,
  `LAST_UPDATED` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DIRECTORY_AREA_NAME` varchar(30) default NULL,
  `STREET_NAME` varchar(255) default NULL,
  `STREET_TYPE_CODE` varchar(6) default NULL,
  `STREET_DIRECTION_CODE` varchar(2) default NULL,
  `ADDRESS_SEQ_CODE` int(8) default NULL,
  `STREET_ADDRESS_FROM_NUMBER` varchar(6) default NULL,
  `STREET_ADDRESS_TO_NUMBER` varchar(6) default NULL,
  `NUMBER_SUFFIX_FROM` varchar(6) default NULL,
  `NUMBER_SUFFIX_TO` varchar(6) default NULL,
  `SUITE_NUMBER_FROM` varchar(6) default NULL,
  `SUITE_NUMBER_TO` varchar(6) default NULL,
  `STREET_NAME_ACCENT_F` int(1) default 0,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Best Answer

This error can occur when you attempt to import a file into MySQL Server 5.6.4 and earlier, when the database was created on and exported from MySQL 5.6.5 or later.

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table."

— https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

It has been suggested that the workaround was to use TIMESTAMP rather than DATETIME for this column, since that appears to work, but that solution may fail for multiple reasons. Additionally, though it gives the initial appearance of working for this specific table, MySQL Server versions before 5.6.5 have other limitations and implicit behavior for TIMESTAMP columns such that this workaround would not be usable in every case.

Notably, in all reasonably modern (5.x and up) versions of MySQL Server the TIMESTAMP and DATETIME data types are not fully interchangeable. DATETIME columns support a broader range of values than TIMESTAMP and TIMESTAMP is subject to the database connection's notion of time zones, with automatic time zone conversions, while DATETIME is not.

Since the database was created on 5.6 or later, for optimum compatibility, you should be loading it onto a server that is also 5.6 or later. Throughout the 5.x series, things are generally backward-compatible; that is, it is generally possible to restore a dump file on a later version of the server, but due to the introduction of new features, the reverse is not as true.

See also:

Related Question