Invalid default value for DATETIME column?

Question

I'm trying to execute my DDL.

I'm using docker-compose.

version: '3.3'

services:
  mysql:
    image: mysql:latest
#    command: ['mysqld', '--character-set-server=utf8mb4', '--collation-server=utf8mb4_unicode_ci']
#    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci
#    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    command: --character-set-server=utf8mb4
    container_name: ...
    restart: always
    environment:
      LANG: "c.UTF-8"
      MYSQL_RANDOM_ROOT_PASSWORD: "yes"
      MYSQL_DATABASE: ...
      MYSQL_USER: ...
      MYSQL_PASSWORD: ...
    ports:
      - "127.0.0.1:33306:3306"
    expose:
      - "3306"
    volumes:
      - ...:/var/lib/mysql
#      - ./var/lib/mysql:/var/lib/mysql
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d/

volumes:
  ...:

And here comes my script.

CREATE TABLE IF NOT EXISTS `...`.`user`
(
    `id`         BIGINT(20)   NOT NULL AUTO_INCREMENT,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '생성일시',
    `updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '갱신일시',
    `created_by` BIGINT(20)   NULL     DEFAULT NULL COMMENT '최초생성자식별자',
    `updated_by` BIGINT(20)   NULL     DEFAULT NULL COMMENT '최종갱신자식별자',
    `name`       VARCHAR(255) NOT NULL COMMENT '이름',
    PRIMARY KEY (`id`),
    INDEX `fk_user_user_created_by_idx` (`created_by` ASC),
    INDEX `fk_user_user_updated_by_idx` (`updated_by` ASC),
    CONSTRAINT `fk_user_user_created_by`
        FOREIGN KEY (`created_by`)
            REFERENCES `...`.`user` (`id`),
    CONSTRAINT `fk_user_user_updated_by`
        FOREIGN KEY (`updated_by`)
            REFERENCES `...`.`user` (`id`)
)
#     ENGINE = InnoDB
#     DEFAULT CHARACTER SET = utf8mb4
#     COLLATE = utf8mb4_0900_ai_ci
;

And docker-compose fails.

2020-03-15 02:36:27+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.19-1debian9 started.
2020-03-15 02:36:28+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-03-15 02:36:28+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.19-1debian9 started.
2020-03-15 02:36:28+00:00 [Note] [Entrypoint]: Initializing database files
2020-03-15T02:36:28.170198Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-03-15T02:36:28.170294Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.19) initializing of server in progress as process 46
2020-03-15T02:36:31.835855Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-03-15 02:36:35+00:00 [Note] [Entrypoint]: Database files initialized
2020-03-15 02:36:35+00:00 [Note] [Entrypoint]: Starting temporary server
2020-03-15T02:36:36.003871Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-03-15T02:36:36.003997Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 96
2020-03-15T02:36:36.702331Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-03-15T02:36:36.709090Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-03-15T02:36:36.748444Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.19'  socket: '/var/run/mysqld/mysqld.sock'  port: 0  MySQL Community Server - GPL.
2020-03-15 02:36:36+00:00 [Note] [Entrypoint]: Temporary server started.
2020-03-15T02:36:36.767826Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock'
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
2020-03-15 02:36:41+00:00 [Note] [Entrypoint]: GENERATED ROOT PASSWORD: quaicou7of9ohgaiM6VoDoaveide9eiS
2020-03-15 02:36:41+00:00 [Note] [Entrypoint]: Creating database ...
2020-03-15 02:36:41+00:00 [Note] [Entrypoint]: Creating user ...
2020-03-15 02:36:41+00:00 [Note] [Entrypoint]: Giving user ... access to schema ..

2020-03-15 02:36:42+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/000_common_schema.sql


2020-03-15 02:36:42+00:00 [Note] [Entrypoint]: /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/100_user_schema.sql
ERROR 1067 (42000) at line 1: Invalid default value for 'created_at'

Why am I seeing this for a DATETIME column in MySQL8?

How can I solve this?

Answer 1

Thanks to this post on SO, I found out what's wrong with my script.

I needed to change the script from:

`created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

to this:

`created_at` DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updated_at` DATETIME(6)     NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

As anyone can see, I should've defined the column type as DATETIME(6).