mysql #1067 - Invalid default value for 'created_at'

Question

I'm following an online course which 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.

score 3 · Answer 1 · answered Jan 27 '18 at 18:13

3

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)
)

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

answered Jan 27 '18 at 18:13

Qirel

25,449
7
45
62

Thank you, but unfortunately it gives the same error. – Sandexor Jan 27 '18 at 18:49
I have tested this query, and it works for me. What versions are you running of MySQL, and how do you run this query? – Qirel Jan 27 '18 at 18:55
The MySQL version is: : 5.7.21-0ubuntu0.16.04.1 - (Ubuntu). I use Phpmyadmin to execute the query on the database. I'm working locally on localhost (Linux Mint). – Sandexor Jan 27 '18 at 19:01
This gives me '0000-00-00 00:00:00' as default value. In MySQL 5.7.20 – Zoltan Tolnai Jan 28 '18 at 12:45
This might explain the mismatch in the results for Zoltan and Sandexor : https://stackoverflow.com/a/37696251/5685406 – site80443 May 06 '21 at 15:31

Zoltan Tolnai · Answer 2 · 2018-01-28T12:32:07.753

0

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.

edited Jan 28 '18 at 12:32

answered Jan 27 '18 at 18:09

Zoltan Tolnai

209
2
5

That's not true, the timezone is set on the server, so if none is specified it uses the timezone set on the server. The issue here is that the timestamp cannot be a string. – Qirel Jan 27 '18 at 18:17
Your query created the table, however MySQL gives me this error: #1292 - Incorrect datetime value: '1970-01-01 00:00:01T00:00' for column 'created_at' at row 1. Should I do some change to adapt the query to the Italian GMT(+1)? If this is the case, how could I? Thanks. – Sandexor Jan 27 '18 at 18:54
I edited my answer. I think your SQL mode was different than mine. – Zoltan Tolnai Jan 28 '18 at 12:36

mysql #1067 - Invalid default value for 'created_at'

2 Answers2