Error creating table: Invalid default value for

2

I was creating a table in the database and the following error appeared:

  

Invalid default value for "updated_at"

Here's 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 am using MySQL client version 5.5.54 . Is it something related to the version? How can I solve the problem?

    
asked by anonymous 05.04.2017 / 18:07

2 answers

8

According to the manual:

  

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initialized 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 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 only valid for TIMESTAMP, and at most one column per table.

That is, only from 5.6.5 onwards.

Until then:

  • could not be used on DATETIME
  • When used in TIMESTAMP it could only have one occurrence per table
  

link


Possible solutions:

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

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

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

    Even if you are going to use a client language, I recommend using NOW() instead of the client language date function, because if at any time you have a problem with timezone or difference between servers, at least created_at and updated_at will be using the same time reference. This applies to other parts of the application: whenever possible, 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 ;
    
05.04.2017 / 19:07
1

MySQL Version

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

At 5.5 the data type must be TIMESTAMP

Edited:

I had suggested in the answer to change the column type from DATETIME to TIMESTAMP , however according to comment below, it is not possible because the table already has a TIMESTAMP column.

    
05.04.2017 / 19:05