MySQL Bugs: #71982: TIMESTAMP(6) DEFAULT CURRENT

Bug #71982	TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP --> invalid
Submitted:	8 Mar 2014 23:13	Modified:	9 Mar 2014 22:04
Reporter:	Rick James	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	1067, 1294, DEFAULT, ON UPDATE, timestamp

Description:
`t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
--> ERROR 1067 (42000): Invalid default value for 't2'

`t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
--> ERROR 1067 (42000): Invalid default value for 't2'

`t2` timestamp(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
--> ERROR 1294 (HY000): Invalid ON UPDATE clause for 't2' column

Removing the (6) lets them work.

Adding (6) to both the DEFAULT and ON UPDATE clauses works:
`t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

How to repeat:
CREATE TABLE `ts_default` (
  `t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `ts_default` (
  `t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `ts_default` (
  `t2` timestamp(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP,
) ENGINE=InnoDB;

Suggested fix:
Plan A:  Document it.

Plan B:  Automatically propagate the (n) into the DEFAULT and ON UDPATE, when appropriate.

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.17 Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > USE w
Database changed
mysql 5.6 > CREATE TABLE `ts_default` (
    ->   `t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.29 sec)

mysql 5.6 > CREATE TABLE `ts_default2` (
    ->   `t2` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.37 sec)

mysql 5.6 > CREATE TABLE `ts_default3` (
    ->   `t2` timestamp(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP(6)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.23 sec)

mysql 5.6 >

Yes, I found that workaround.

But I did not find the explanation that the DEFAULT value must have the same (fsp) as the declaration.  Can you point me to the manual page?

This line comes from mysqldump running on MariaDB 10.3.14.  Apparently it's invalid.

CREATE TABLE IF NOT EXISTS `general_log` (
  `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';