Creating a table with default timestamp value has invalid quotes

[pathikrit]

I get this:

2016-02-18T17:43:17.385000-08:00 ERROR Database error 0A000: date/time value "current" is no longer supported
QUERY: CREATE TABLE foo
(
  id                     bigserial not null,
  name              text,
  created_on             timestamptz(6) not null default 'CURRENT_TIMESTAMP(6)'
);
2016-02-18T17:43:17.385000-08:00 FATAL Failed to create the schema, see above.

But, when I remove the single-quotes, it works:

CREATE TABLE foo
(
  id                     bigserial not null,
  name              text,
  created_on             timestamptz(6) not null default CURRENT_TIMESTAMP(6)
);

This my config:

LOAD database
  FROM  mysql://dev:dev@localhost/main
  INTO  postgresql://dev@localhost/main
  WITH  include drop, create tables, no truncate, create indexes, reset sequences, foreign keys
  SET   maintenance_work_mem to '512MB', work_mem to '128MB', search_path to 'main'
  CAST type datetime to timestamptz
  BEFORE LOAD DO $$ CREATE SCHEMA IF NOT EXISTS main; $$
;

[dimitri]

Can you provide me with the MySQL table definition so that I can reproduce the bug locally then fix it?

[pathikrit]

Here it is:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_on` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_on` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

[pathikrit]

I know PostgreSQL does not support the ON UPDATE as MYSQL does - but just putting it there for you. Also, INT NOT NULL AUTO_INCREMENT should map to SERIA and not BIGSERIAL I think.

[dimitri]

Seems I can't quite reproduce your situation here:

    -> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '6),
  `updated_on` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
  PR' at line 4

And with minimal tweaking, still failing:

 CREATE TABLE `default_current_timestamp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_on` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    -> ERROR 1067 (42000): Invalid default value for 'created_on'

[pathikrit]

Had a slight typo. See now:

sql> CREATE TABLE `default_current_timestamp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_on` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_on` datetime(6) DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
[2016-02-19 22:29:15] completed in 525ms

[dimitri]

Just tried a blind fix that should do, please report back! (was hesitant to do so but the fix is very well contained so it should be ok).

[dancek]

I have a similar issue with pgloader installed from Homebrew on macOS.

$ pgloader --version
pgloader version "3.4.1"
compiled with SBCL 1.4.4

I get the error

2018-04-24T12:57:08.497000+01:00 ERROR Database error 0A000: date/time value "current" is no longer supported
QUERY: CREATE TABLE mydb.file_migrationdata
(
  id                bigserial not null,
  creation_time     timestamptz not null default 'current_timestamp()',
  importid          bigint default NULL,
  modification_time timestamptz not null default 'current_timestamp()',
  original_id       numeric not null,
  container_name    varchar(255) not null
);

The table definition (created by mysqldump) is like:

CREATE TABLE `file_migrationdata` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `creation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `importId` int(11) DEFAULT NULL,
  `modification_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `original_id` bigint(20) NOT NULL,
  `container_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_FILE_MIGRATIONDATA_ID` (`id`),
  KEY `IDX_ORIGINAL_ID` (`original_id`),
  KEY `IDX_CONTAINER_NAME` (`container_name`),
  CONSTRAINT `FK_FILE_MIGRATIONDATA_ORIGINAL_ID` FOREIGN KEY (`original_id`) REFERENCES `file_metadata` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please inform me if there is any other information that would be helpful.

[dimitri]

I see your using pgloader 3.4.1, please compile from source and try again, I believe the error is fixed in the source repository.

[dancek]

Yes, the issue goes away by running the HEAD version.

[lidragonli]

I have the similar issue too.

pgloader --version
pgloader version "3.4.2~devel"
compiled with SBCL 1.3.14.debian

I used the latest docker image.

master /Dockerfile latest 8 days ago 2018-04-29T17:06:36.387Z

I got this error:

ERROR Database error 0A000: date/time value "current" is no longer supported
QUERY: CREATE TABLE act_hi_identitylink
(
  id_             varchar(64) not null,
  timestamp_      timestamptz not null default 'current_timestamp(3)',
  type_           varchar(255) default NULL,
  user_id_        varchar(255) default NULL,
  group_id_       varchar(255) default NULL,
  task_id_        varchar(64) default NULL,
  proc_def_id_    varchar(64) default NULL,
  operation_type_ varchar(64) default NULL,
  assigner_id_    varchar(64) default NULL,
  proc_def_key_   varchar(255) default NULL,
  tenant_id_      varchar(64) default NULL
);
2018-05-07T09:12:06.691000Z FATAL Failed to create the schema, see above.

The mariadb create statement:

CREATE TABLE `act_hi_identitylink` (
  `ID_` varchar(64) COLLATE utf8_bin NOT NULL,
  `TIMESTAMP_` timestamp(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3),
  `TYPE_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `USER_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `GROUP_ID_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `TASK_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `PROC_DEF_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `OPERATION_TYPE_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ASSIGNER_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `PROC_DEF_KEY_` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `TENANT_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_USER` (`USER_ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_GROUP` (`GROUP_ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_TENANT_ID` (`TENANT_ID_`),
  KEY `ACT_IDX_HI_IDENT_LNK_PROC_DEF_KEY` (`PROC_DEF_KEY_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

I already tried with casting rule, but still the same error.

      type timestamp 
              when default "CURRENT_TIMESTAMP"
              with extra on update current timestamp
        to "timestamp with time zone"
             drop default drop not null drop extra
            using zero-dates-to-null

[r0bis]

I can confirm that with more recent version

pgloader version "3.6.2" compiled with SBCL 2.0.1

it works well

[fjitexvietnam]

Xin giup tôi :

-- Cấu trúc bảng cho bảng wp_404_to_301

CREATE TABLE wp_404_to_301 (
id bigint(20) NOT NULL,
date datetime NOT NULL,
url varchar(512) NOT NULL,
ref varchar(512) NOT NULL DEFAULT '',
ip varchar(40) NOT NULL DEFAULT '',
ua varchar(512) NOT NULL DEFAULT '',
redirect varchar(512) DEFAULT '',
options longtext DEFAULT NULL,
status bigint(20) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Đang đổ dữ liệu cho bảng wp_404_to_301