ERROR 1067 (42000) at line 7: Invalid default value for 'LAST_UPDATED'

Question

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;

Answer 1

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:

• Time Zone Support

• The DATE, DATETIME, and TIMESTAMP Types

• Should I use DATETIME or TIMESTAMP?

Answer 2

mysql error 1067 invalid default value for timestamp

1. Start Cmd.
2. Type mysql -u root -p Then Press Enter.
3. And Select Your Database (Ex "USE DATABASE ").

4. Type this

   SET @@session.sql_mode ="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

5. And Then Press Enter.

For more information : https://buddhikal.blogspot.com/2017/01/mysql-error-1067-invalid-default-value.html

Answer 3

Solved the error by changing DATETIME to TIMESTAMP. As indicated in the manual, "Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP".

Answer 4

You can use SET sql_mode = ""; in the beginning of your SQL CODE. The MySql Documentation provides some detail.