MySQL: Invalid default value for TIMESTAMP

Question

I get the error

ERROR 1067 (42000) at line 5459: Invalid default value for 'start_time'

when running the following query

DROP TABLE IF EXISTS `slow_log`;
CREATE TABLE IF NOT EXISTS `slow_log` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

I am using MySQL 5.7.18

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for osx10.10 (x86_64) using  EditLine wrapper

According to the MySQL 5.7 documentation, the following syntax is

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

What is wrong with the SQL syntax above?

Atencio

Not sure if it is the problem, but do you need NOT NULL with DEFAULT? Also try removing (6). – Detonator

I agree, that the NOT NULL can be the issue, because during insertion the field is initially null to get changed after the insert to the current_timestamp. – Pity

How does one end up with stuff like int(10) !?! – Goodbye

Answer 1

Interestingly, both these work:

`start_time` timestamp(6), 

And:

`start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

You can use the latter -- leave the precision specifier out of the definition.

But the right method is:

`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

As explained in the documentation:

If a TIMESTAMP or DATETIME column definition includes an explicit fractional seconds precision value anywhere, the same value must be used throughout the column definition. This is permitted:

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

This is not permitted:

CREATE TABLE t1 (
  ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(3)
);

Puto

Answer 2

Try:

mysql> DROP TABLE IF EXISTS `slow_log`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `slow_log` (
    ->   `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    ->                                      ON UPDATE CURRENT_TIMESTAMP(6),
    ->   `user_host` mediumtext NOT NULL,
    ->   `query_time` time(6) NOT NULL,
    ->   `lock_time` time(6) NOT NULL,
    ->   `rows_sent` int NOT NULL,
    ->   `rows_examined` int NOT NULL,
    ->   `db` varchar(512) NOT NULL,
    ->   `last_insert_id` int NOT NULL,
    ->   `insert_id` int NOT NULL,
    ->   `server_id` int unsigned NOT NULL,
    ->   `sql_text` mediumtext NOT NULL
    -> ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';
Query OK, 0 rows affected (0.00 sec)

Stingy

This is not very useful if this is in production and the table can't simply be dropped. You also didn't explain what exactly you're doing differently and why. – Stewpan