Zac Powell Zac Powell - 5 months ago 21
MySQL Question

Importing SQL database Invalid default value for datetime

I am moving a database from one MySQL server to another, however when importing one table I end up with the error:


1067 - Invalid default value for 'date'


the table SQL:

CREATE TABLE IF NOT EXISTS `log` (
`id` int(11) NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`url` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=165 DEFAULT CHARSET=utf8


Importing to MySQL version 5.5.47 from 5.6.21

Answer

In MySQL 5.5 you can only use DEFAULT CURRENT_TIMESTAMP when the column datatype is TIMESTAMP, not DATETIME.

The MySQL 5.5 documentation says:

The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

In 5.6 they extended this to DATETIME, its documentation says:

The exception is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns.

So either change the datatype or remove the default value.

You should always be wary of migrating to an older version of any software, since it's often possible that your code is using new features that didn't exist previously. Updated versions usually provide backward compatibility (at least for a few releases), but there's no way for old versions to deal with unimplemented features from new versions.