Error creating table: Invalid default value for

Question

I went to create a table in the bank and appeared following error:

Invalid default value for "updated_at"

Here is the table to create:

CREATE TABLE  `user` (

 `id` INT( 11 ) NOT NULL ,
 `uuid` CHAR( 12 ) NOT NULL ,
 `fullname` VARCHAR( 512 ) DEFAULT NULL ,
 `username` VARCHAR( 128 ) DEFAULT NULL ,
 `email` VARCHAR( 512 ) DEFAULT NULL ,
 `device` TEXT,
 `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
 `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = MYISAM DEFAULT CHARSET = latin1;

The database is created on a machine with Amazon Linux AMI, and I’m using client’s version Mysql: 5.5.54. Is it something related to the version? How can I solve the problem?

Answer 1

According to the manual:

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.

Which is more or less:

Since Mysql 5.6.5, TIMESTAMP and DATETIME columns can be automatically initialized and updated with current date and time. Before 5.6.5 this was valid only for TIMESTAMP, and at most in one column per table.

That is, only the 5.6.5 onwards.

Until then:

• could not be used in DATETIME
• when used in TIMESTAMP could only have one occurrence per table

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

Possible solutions:

• The most obvious, but not always possible, is to update the server;

• Alternatively, you could consider dealing in a client language by adding the NOW() or CURRENT_TIMESTAMP in the original query.

  UPDATE tabela SET username = "Joao", updated_at = NOW()
  

  Even if using a client language, I recommend using NOW() instead of the client language date function, because if at any time you have any problems with Timezone or difference between servers, at least the created_at and updated_at will be using the same time frame. This goes for other parts of the application: whenever possible, choose to use a time-only reference for everything.

• A third possibility would be to create a TRIGGER that would do it for you:

  DELIMITER ;;
  CREATE TRIGGER `user_bu` BEFORE UPDATE ON `user` FOR EACH ROW
  BEGIN
     SET NEW.updated_at = NOW();
  END;;
  DELIMITER ;
  

Answer 2

Version of Mysql

Only from the 5.6 you can use DATETIME using the DEFAULT automatic updating.

In the 5.5 the type of data needs to be TIMESTAMP

Edited:

Had suggested in the reply to change the type of the column of DATETIME for TIMESTAMP, however as comment below, it is not possible because the table already has a column TIMESTAMP.