Error on creating Mysql database table

Question

Below is the query that I userd to execute to create a table:

CREATE TABLE `users_authentication` (
`id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`token` varchar(255) NOT NULL,
`expired_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But it shows an error message like this:

1067 - Invalid default value for 'expired_at'

Perhaps you need column data type timestamp? – jarlh Apr 12 '17 at 07:55 — jarlh, Apr 12 '17 at 07:55
I executed the same query on my machine it is working. – dsharew Apr 12 '17 at 08:10 — dsharew, Apr 12 '17 at 08:10
@jarlh I wanna add date and time thats all – Shihas Apr 12 '17 at 08:17 — Shihas, Apr 12 '17 at 08:17
@dsharew but its not working for me – Shihas Apr 12 '17 at 08:17 — Shihas, Apr 12 '17 at 08:17

score 2 · Answer 1 · edited May 23 '17 at 12:10

2

Use TIMESTAMP instead of DATETIME. In some versions of MySQL, it is not possible to use CURRENT_TIMESTAMP with DATETIME.

So, you can replace your script for this, for MySQL versions previous to 5.6.5:

CREATE TABLE `users_authentication` (
`id` int(11) NOT NULL,
`users_id` int(11) NOT NULL,
`token` varchar(255) NOT NULL,
`expired_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created_at` TIMESTAMP NOT NULL,
`updated_at` TIMESTAMP NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Check here: How do you set a default value for a MySQL Datetime column?

The MySQL doc:

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 at most one TIMESTAMP column per table.

MySQL Doc

edited May 23 '17 at 12:10

Community

1
1

answered Apr 12 '17 at 07:55

fingerprints

2,751
1
25
45

**"Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause"** This is the error now its showing – Shihas Apr 12 '17 at 08:15
And Mysql Server version: 5.5.42 – Shihas Apr 12 '17 at 08:35
check again the doc `and for at most one TIMESTAMP column per table`. You can add only one timestamp column with current date by default. – fingerprints Apr 12 '17 at 08:35
1

Good. This is what i did :) – Shihas Apr 12 '17 at 08:52

score 0 · Answer 2 · answered Apr 12 '17 at 07:54

0

CURRENT_TIMESTAMP is only acceptable on TIMESTAMP fields. DATETIME fields must be left either with a null default value, or no default value at all - default values must be a constant value, not the result of an expression.

Reference - data-type-defaults

answered Apr 12 '17 at 07:54

Mayank Pandeyz

25,704
4
40
59

so can you please make this query executable somehow? – Shihas Apr 12 '17 at 08:22

Prashant Kumar Singh · Answer 3 · 2017-10-09T05:53:25.433

-1

CREATE TABLE users_authentication(
    id int(30) NOT NULL,
    user_id int(30) NOT NULL,
    token varchar(40),
    expired_at TIMESTAMP NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
);

We cant use three TIMESTAMP like TIMESTAMP NOT NULL so we have to change this by setting different default value for all One Mysql Table with Multiple TIMESTAMP Columns

edited Oct 09 '17 at 05:53

answered Oct 08 '17 at 20:43

Prashant Kumar Singh

61
9

now check and if u not satisfy then i can add my screenshot query@GertArnold – Prashant Kumar Singh Oct 09 '17 at 05:56

Error on creating Mysql database table

1067 - Invalid default value for 'expired_at'

3 Answers3