mysql＃1067 - 'created_at'的默认值无效

I'm following an online course wich includes creating a Wordpress plugin. There was a problem when I tried, following the instructions (and using the provided code to exclude any error on my side) to create a custom table. This is a section of the original code:

    $sql = "CREATE TABLE {$wpdb->prefix}ssp_survey_responses (
        id mediumint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        ip_address varchar(32) NOT NULL,
        survey_id mediumint(11) UNSIGNED  NOT NULL,
        response_id mediumint(11) UNSIGNED  NOT NULL,
        created_at TIMESTAMP DEFAULT '1970-01-01 00:00:00',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE INDEX ix (ip_address,survey_id)
        ) $charset_collate;";

I tried to insert the SQL directly in the MySQL database, cleaning the code:

CREATE TABLE ssp_survey_responses (
        id mediumint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        ip_address varchar(32) NOT NULL,
        survey_id mediumint(11) UNSIGNED  NOT NULL,
        response_id mediumint(11) UNSIGNED  NOT NULL,
        created_at TIMESTAMP DEFAULT '1970-01-01 00:00:01',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE INDEX ix (ip_address,survey_id)
        )

and MySQL gives this error

#1067 - Invalid default value for 'created_at'

Might the local settings of MySQL (italian) be creating any problem? The MySQL version is: : 5.7.21-0ubuntu0.16.04.1 - (Ubuntu). PHP Version 7.0.22. Wordpress version: 4.9.2. Thank you.

You need to edit your default timestampt according to your timezone like this (assuming GMT+1):

CREATE TABLE ssp_survey_responses (
    id mediumint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ip_address varchar(32) NOT NULL,
    survey_id mediumint(11) UNSIGNED  NOT NULL,
    response_id mediumint(11) UNSIGNED  NOT NULL,
    created_at TIMESTAMP DEFAULT '1970-01-01 01:00:01',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY  (id),
    UNIQUE INDEX ix (ip_address,survey_id)
    )

If your timezone is GMT+X (where X is a positive number) then the timestamp converted back to GMT would be a negative number, which is indeed invalid. Here, this timestamp is converted back to 00:00:01 GMT which is stored as 0.

A timestamp is the amount of seconds since January 1st, 1970. Which means that this is really an integer, and not a string. If you want the default to be at that point (1970-01-01 00:00), specify it to be DEFAULT 0. A string cannot be passed into a timestamp.

CREATE TABLE ssp_survey_responses (
    id mediumint(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ip_address varchar(32) NOT NULL,
    survey_id mediumint(11) UNSIGNED  NOT NULL,
    response_id mediumint(11) UNSIGNED  NOT NULL,
    created_at TIMESTAMP DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY  (id),
    UNIQUE INDEX ix (ip_address,survey_id)
)