Default error for datetime field in Mysql

Question

I had a problem trying to run my database script on the server. The error already gives in the first table that the script generates:

CREATE TABLE IF NOT EXISTS `categoria` (
`cd_categoria` int(255) NOT NULL,
  `titulo` varchar(100) NOT NULL,
  `cd_status` int(1) NOT NULL,
  `dh_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `dh_alteracao` datetime NOT NULL
) ;

The mistake that happens is this:

Error Code: 1067. Invalid default value for 'dh_timestamp'

I don’t understand what happened because I need my script to store in the column dh_timestamp the current date that if there is a record of anything. How can I resolve this?

Answer 1

Cannot set default value as now() or CURRENT_TIMESTAMP (which are synonyms) for datetime columns in versions prior to Mysql 5.6, in which case the simplest solution is to change the type of datetime for timestamp. If the change of type is not feasible another solution as dictated by Interest in the comments is to create a Trigger to set the default value, that answer of Soen shows how to do.

Recommended reading:

Documentation: timestamp initialization

Difference between datetime x timestamp?

How to know the time a record was added to the bank?

PHP & mysql: Year 2038 Bug: What is it? How to Solve it?