Pgloader: MySQL binary(16) to Postgres uuid type fails because of \x (hexadecimal character escape)

[djvdorp]

• [x] pgloader --version

pgloader version "3.5.2"
compiled with SBCL 1.3.1.debian

• [x] did you test a fresh compile from the source tree?
  yes, same issue and output:

pgloader version "3.6.213edbe"
compiled with SBCL 1.3.1.debian

• [x] did you search for other similar issues?
  yes

• [x] how can I reproduce the bug?

  1. mysql source table definition and data (mysqldump with --hex-blob)

+-----------------+------------+------+-----+---------+-------+
| Field           | Type       | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| encrypted_value | blob       | YES  |     | NULL    |       |
| nonce           | tinyblob   | YES  |     | NULL    |       |
| uuid            | binary(16) | NO   | PRI | NULL    |       |
| salt            | tinyblob   | YES  |     | NULL    |       |
+-----------------+------------+------+-----+---------+-------+

--
-- Table structure for table `encryption_key_canary`
--

DROP TABLE IF EXISTS `encryption_key_canary`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `encryption_key_canary` (
  `encrypted_value` blob,
  `nonce` tinyblob,
  `uuid` binary(16) NOT NULL,
  `salt` tinyblob,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `encryption_key_canary`
--

LOCK TABLES `encryption_key_canary` WRITE;
/*!40000 ALTER TABLE `encryption_key_canary` DISABLE KEYS */;
INSERT INTO `encryption_key_canary` VALUES (
  0x1F36F183D7EE47C71453850B756945C16D9D711B2F0594E5D5E54D1EC94E081716AB8642AA60F84B50F69454D098122B7136A0DEB3AF200C2C5C7500BDFA0BD9689CCBF10A76972374882B304F7F15A227E815989FC87EEB72612396F569C662E72A2A7555E654605A3B83C1C753297832E52C5961E81EBC60DC43D929ABAB8CB14601DEFED121604CEB26210AB6D724,
  0x044AA707DF17021E55E9A1E4,
  0x88C2982F428A46B7B71B210618AE1658,
  0xAE7F18028E7984FB5630F7D23FB77999C6CA7CF5355EF0194F3F16521EA7EC503F566229ED8DC5EFBBE9C12BA491BDDC939FE60FA31FB9AF123B2B4D5B7A61FE
);
/*!40000 ALTER TABLE `encryption_key_canary` ENABLE KEYS */;
UNLOCK TABLES;

1. postgres destination table definition (as defined by software vendor)

--
-- Name: encryption_key_canary; Type: TABLE; Schema: public; Owner: xxx
--

CREATE TABLE public.encryption_key_canary (
    encrypted_value bytea,
    nonce bytea,
    uuid uuid NOT NULL,
    salt bytea
);

ALTER TABLE public.encryption_key_canary OWNER TO xxx;

1. put a uuid in mysql binary(16) type column
2. run pgloader with command file below
3. this will try to CAST this binary(16) to type bytea which is not the same as uuid: WARNING Source column "public"."encryption_key_canary"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."encryption_key_canary"."uuid".
4. the destination type is uuid in postgres, not bytea, as detected correctly
5. it won't work because of the \x prefix that is there after conversion by pgloader: ERROR Database error 22P02: invalid input syntax for uuid: "\x88c2982f428a46b7b71b210618ae1658" CONTEXT: COPY encryption_key_canary, line 1, column uuid: "\x88c2982f428a46b7b71b210618ae1658"

LOAD DATABASE
        FROM mysql://xxx.rds.amazonaws.com/credhub
        INTO postgresql://xxx.rds.amazonaws.com/credhub

 WITH data only

 ALTER SCHEMA 'credhub' RENAME TO 'public'

 BEFORE LOAD DO
 $$ set session_replication_role = replica; $$

 AFTER LOAD DO
 $$ set session_replication_role = default; $$
;

• [x] pgloader output you obtain

WARNING Source column "public"."encryption_key_canary"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."encryption_key_canary"."uuid".
ERROR Database error 22P02: invalid input syntax for uuid: "\x88c2982f428a46b7b71b210618ae1658"
CONTEXT: COPY encryption_key_canary, line 1, column uuid: "\x88c2982f428a46b7b71b210618ae1658"

• [x] data that is being loaded, if relevant

none, because it has aborted

• [x] How the data is different from what you expected, if relevant
  if pgloader would strip off the \x hexadecimal character escape it should work fine, tested that manually by doing this in psql, see below:

credhub=> insert into encryption_key_canary (uuid) values ('\x88c2982f428a46b7b71b210618ae1658');
ERROR:  invalid input syntax for uuid: "\x88c2982f428a46b7b71b210618ae1658"
LINE 1: insert into encryption_key_canary (uuid) values ('\x88c2982f...

vs

credhub=> insert into encryption_key_canary (uuid) values ('88c2982f428a46b7b71b210618ae1658');
INSERT 0 1

if I would know how to manually strip off the \x in pgloader command file I would have done that as a workaround for this specific (edge) case, but unfortunately I am not aware on how that would work.

[dimitri]

The error message should be explicit enough. Your input data is a MySQL binary strings, which is converted to a bytea representation by pgloader. The target column is UUID, which is not compatible with bytea representation. The \x prefix is added by pgloader when preparing the data, you need to instruct pgloader about the conversion here.

Try using the following user-defined casting-rule:

cast column encryption_key_canary.uuid to uuid using varbinary-to-string

[djvdorp]

@dimitri thank you, that's very helpful! I tried the user-defined casting-rule you provided (actually I opened this ticket about encryption_key_canary.uuid but there is 15 columns with similar issues, so I repeated the casting-rule above 14 more times). However, it seems not completely solved here yet for one of the other examples:

2019-02-11T15:59:31.579000Z ERROR Error while formating a row from "public.encryption_key_canary":
2019-02-11T15:59:31.580000Z ERROR Illegal :UTF-8 character starting at position 0.
2019-02-11T15:59:31.581000Z ERROR Argument X is not a NUMBER: NIL
2019-02-11T15:59:31.581000Z WARNING PostgreSQL warning: there is no transaction in progress
2019-02-11T15:59:31.597000Z ERROR Database error 22P02: invalid input syntax for uuid: "\x1f2af3ce1450466196155184abd3304c"
CONTEXT: COPY permission, line 1, column uuid: "\x1f2af3ce1450466196155184abd3304c"

I did create matching rule:

cast column permission.uuid to uuid using varbinary-to-string

Is there any other info I need to provide to make the case more clear and get this working together?

[dimitri]

Can you provide output when using --debug on this one?

[djvdorp]

Absolutely! Stripped out connection strings:

sb-impl::*default-external-format* :UTF-8
tmpdir: #P"/tmp/pgloader/"
2019-02-11T17:33:14.019000Z NOTICE Starting pgloader, log system is ready.
2019-02-11T17:33:14.053000Z INFO Starting monitor
2019-02-11T17:33:14.067000Z INFO Parsed command:
LOAD DATABASE

 WITH include no drop, truncate, data only

 ALTER SCHEMA 'credhub' RENAME TO 'public'

cast column encryption_key_canary.uuid to uuid using varbinary-to-string
cast column certificate_credential.uuid to uuid using varbinary-to-string
cast column credential.uuid to uuid using varbinary-to-string
cast column credential_version.uuid to uuid using varbinary-to-string
cast column credential_version.credential_uuid to uuid using varbinary-to-string
cast column credential_version.encrypted_value_uuid to uuid using varbinary-to-string
cast column encrypted_value.uuid to uuid using varbinary-to-string
cast column encrypted_value.encryption_key_uuid to uuid using varbinary-to-string
cast column password_credential.uuid to uuid using varbinary-to-string
cast column password_credential.password_parameters_uuid to uuid using varbinary-to-string
cast column permission.uuid to uuid using varbinary-to-string
cast column rsa_credential.uuid to uuid using varbinary-to-string
cast column ssh_credential.uuid to uuid using varbinary-to-string
cast column user_credential.uuid to uuid using varbinary-to-string
cast column user_credential.password_parameters_uuid to uuid using varbinary-to-string

 BEFORE LOAD DO
 $$ set session_replication_role = replica; $$

 AFTER LOAD DO
 $$ set session_replication_role = default; $$
;

2019-02-11T17:33:14.122000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.122000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.132000Z NOTICE Executing SQL block for before load
2019-02-11T17:33:14.139000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.139000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.143000Z DEBUG BEGIN
2019-02-11T17:33:14.143000Z SQL set session_replication_role = replica;
2019-02-11T17:33:14.207000Z SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
  select c.table_name, t.table_comment,
         c.column_name, c.column_comment,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'credhub' and t.table_type = 'BASE TABLE'



order by table_name, ordinal_position;
2019-02-11T17:33:14.237000Z SQL MySQL: sending query: -- params: db-name
--         table-type-name
--         only-tables
--         only-tables
--         including
--         filter-list-to-where-clause incuding
--         excluding
--         filter-list-to-where-clause excluding
SELECT s.table_name, s.constraint_name, s.ft, s.cols, s.fcols,
       rc.update_rule, rc.delete_rule

FROM
 (
  SELECT tc.table_schema, tc.table_name,
         tc.constraint_name, k.referenced_table_name ft,

             group_concat(         k.column_name
                          order by k.ordinal_position) as cols,

             group_concat(         k.referenced_column_name
                          order by k.position_in_unique_constraint) as fcols

        FROM information_schema.table_constraints tc

        LEFT JOIN information_schema.key_column_usage k
               ON k.table_schema = tc.table_schema
              AND k.table_name = tc.table_name
              AND k.constraint_name = tc.constraint_name

      WHERE     tc.table_schema = 'credhub'
            AND k.referenced_table_schema = 'credhub'
            AND tc.constraint_type = 'FOREIGN KEY'




   GROUP BY tc.table_schema, tc.table_name, tc.constraint_name, ft
 ) s
             JOIN information_schema.referential_constraints rc
               ON rc.constraint_schema = s.table_schema
              AND rc.constraint_name = s.constraint_name
              AND rc.table_name = s.table_name;
2019-02-11T17:33:14.323000Z NOTICE Prepare PostgreSQL database.
2019-02-11T17:33:14.338000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.338000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.339000Z DEBUG BEGIN
2019-02-11T17:33:14.363000Z SQL -- params: table-type-name
--         including
--         filter-list-to-where-clause for including
--         excluding
--         filter-list-to-where-clause for excluding
    select nspname, relname, c.oid, attname,
           t.oid::regtype as type,
           case when atttypmod > 0 then atttypmod - 4 else null end as typmod,
           attnotnull,
           case when atthasdef then def.adsrc end as default
      from pg_class c
           join pg_namespace n on n.oid = c.relnamespace
           left join pg_attribute a on c.oid = a.attrelid
           join pg_type t on t.oid = a.atttypid and attnum > 0
           left join pg_attrdef def on a.attrelid = def.adrelid
                                   and a.attnum = def.adnum

     where nspname !~ '^pg_' and n.nspname <> 'information_schema'
           and relkind in ('r', 'f', 'p')
           and ((n.nspname = 'public' and c.relname ~ '^certificate_credential$')
           or (n.nspname = 'public' and c.relname ~ '^credential$')
           or (n.nspname = 'public' and c.relname ~ '^credential_version$')
           or (n.nspname = 'public' and c.relname ~ '^encrypted_value$')
           or (n.nspname = 'public' and c.relname ~ '^encryption_key_canary$')
           or (n.nspname = 'public' and c.relname ~ '^flyway_schema_history$')
           or (n.nspname = 'public' and c.relname ~ '^password_credential$')
           or (n.nspname = 'public' and c.relname ~ '^permission$')
           or (n.nspname = 'public' and c.relname ~ '^rsa_credential$')
           or (n.nspname = 'public' and c.relname ~ '^ssh_credential$')
           or (n.nspname = 'public' and c.relname ~ '^user_credential$'))


  order by nspname, relname, attnum;
2019-02-11T17:33:14.375000Z SQL -- params: including
--         filter-list-to-where-clause for including
--         excluding
--         filter-list-to-where-clause for excluding
  select n.nspname,
         i.relname,
         i.oid,
         rn.nspname,
         r.relname,
         indisprimary,
         indisunique,
         pg_get_indexdef(indexrelid),
         c.conname,
         pg_get_constraintdef(c.oid)
    from pg_index x
         join pg_class i ON i.oid = x.indexrelid
         join pg_class r ON r.oid = x.indrelid
         join pg_namespace n ON n.oid = i.relnamespace
         join pg_namespace rn ON rn.oid = r.relnamespace
         left join pg_constraint c ON c.conindid = i.oid
                                  and c.conrelid = r.oid
                                  -- filter out self-fkeys
                                  and c.confrelid <> r.oid
   where n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
         and ((rn.nspname = 'public' and r.relname ~ '^certificate_credential$')
           or (rn.nspname = 'public' and r.relname ~ '^credential$')
           or (rn.nspname = 'public' and r.relname ~ '^credential_version$')
           or (rn.nspname = 'public' and r.relname ~ '^encrypted_value$')
           or (rn.nspname = 'public' and r.relname ~ '^encryption_key_canary$')
           or (rn.nspname = 'public' and r.relname ~ '^flyway_schema_history$')
           or (rn.nspname = 'public' and r.relname ~ '^password_credential$')
           or (rn.nspname = 'public' and r.relname ~ '^permission$')
           or (rn.nspname = 'public' and r.relname ~ '^rsa_credential$')
           or (rn.nspname = 'public' and r.relname ~ '^ssh_credential$')
           or (rn.nspname = 'public' and r.relname ~ '^user_credential$'))

order by n.nspname, r.relname
2019-02-11T17:33:14.392000Z SQL -- params: including (table)
--         filter-list-to-where-clause for including
--         excluding (table)
--         filter-list-to-where-clause for excluding
--         including (ftable)
--         filter-list-to-where-clause for including
--         excluding (ftable)
--         filter-list-to-where-clause for excluding
 select n.nspname, c.relname, nf.nspname, cf.relname as frelname,
        r.oid, conname,
        pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
        (select string_agg(attname, ',')
           from pg_attribute
          where attrelid = r.conrelid
            and array[attnum::integer] <@ conkey::integer[]
        ) as conkey,
        (select string_agg(attname, ',')
           from pg_attribute
          where attrelid = r.confrelid
            and array[attnum::integer] <@ confkey::integer[]
        ) as confkey,
        confupdtype, confdeltype, confmatchtype,
        condeferrable, condeferred
   from pg_catalog.pg_constraint r
        JOIN pg_class c on r.conrelid = c.oid
        JOIN pg_namespace n on c.relnamespace = n.oid
        JOIN pg_class cf on r.confrelid = cf.oid
        JOIN pg_namespace nf on cf.relnamespace = nf.oid
   where r.contype = 'f'
         AND c.relkind in ('r', 'f', 'p')
         AND cf.relkind in ('r', 'f', 'p')
         AND n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
         AND nf.nspname !~ '^pg_' and nf.nspname <> 'information_schema'
         and ((n.nspname = 'public' and c.relname ~ '^certificate_credential$')
           or (n.nspname = 'public' and c.relname ~ '^credential$')
           or (n.nspname = 'public' and c.relname ~ '^credential_version$')
           or (n.nspname = 'public' and c.relname ~ '^encrypted_value$')
           or (n.nspname = 'public' and c.relname ~ '^encryption_key_canary$')
           or (n.nspname = 'public' and c.relname ~ '^flyway_schema_history$')
           or (n.nspname = 'public' and c.relname ~ '^password_credential$')
           or (n.nspname = 'public' and c.relname ~ '^permission$')
           or (n.nspname = 'public' and c.relname ~ '^rsa_credential$')
           or (n.nspname = 'public' and c.relname ~ '^ssh_credential$')
           or (n.nspname = 'public' and c.relname ~ '^user_credential$'))

         and ((nf.nspname = 'public' and cf.relname ~ '^certificate_credential$')
           or (nf.nspname = 'public' and cf.relname ~ '^credential$')
           or (nf.nspname = 'public' and cf.relname ~ '^credential_version$')
           or (nf.nspname = 'public' and cf.relname ~ '^encrypted_value$')
           or (nf.nspname = 'public' and cf.relname ~ '^encryption_key_canary$')
           or (nf.nspname = 'public' and cf.relname ~ '^flyway_schema_history$')
           or (nf.nspname = 'public' and cf.relname ~ '^password_credential$')
           or (nf.nspname = 'public' and cf.relname ~ '^permission$')
           or (nf.nspname = 'public' and cf.relname ~ '^rsa_credential$')
           or (nf.nspname = 'public' and cf.relname ~ '^ssh_credential$')
           or (nf.nspname = 'public' and cf.relname ~ '^user_credential$'))

2019-02-11T17:33:14.397000Z SQL -- params pkey-oid-list
--        fkey-oild-list
with pkeys(oid) as (
  values(54613),(54633),(54629),(54639),(54621),(54623),(54615),(54617),(54637),(54619),(54625),(54638),(54611),(54627),(54631),(54635)
),
     knownfkeys(oid) as (
  values(57417),(57422),(57427),(57432),(57437),(57442),(57447),(57452),(57457),(57462)
),
  pkdeps as (
  select pkeys.oid, pg_depend.objid
    from pg_depend
         join pkeys on pg_depend.refobjid = pkeys.oid
   where     classid = 'pg_catalog.pg_constraint'::regclass
         and refclassid = 'pg_catalog.pg_class'::regclass
)
 select n.nspname, c.relname, nf.nspname, cf.relname as frelname,
        r.oid as conoid, conname,
        pg_catalog.pg_get_constraintdef(r.oid, true) as condef,
        pkdeps.oid as index_oid
   from pg_catalog.pg_constraint r
        JOIN pkdeps on r.oid = pkdeps.objid
        JOIN pg_class c on r.conrelid = c.oid
        JOIN pg_namespace n on c.relnamespace = n.oid
        JOIN pg_class cf on r.confrelid = cf.oid
        JOIN pg_namespace nf on cf.relnamespace = nf.oid
  where NOT EXISTS (select 1 from knownfkeys where oid = r.oid)
2019-02-11T17:33:14.399000Z DEBUG fetch-pgsql-catalog: 11 tables, 16 indexes, 10+0 fkeys
2019-02-11T17:33:14.399000Z WARNING Source column "public"."certificate_credential"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."certificate_credential"."uuid".
2019-02-11T17:33:14.399000Z WARNING Source column "public"."credential"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."credential"."uuid".
2019-02-11T17:33:14.399000Z WARNING Source column "public"."credential_version"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."credential_version"."uuid".
2019-02-11T17:33:14.399000Z WARNING Source column "public"."credential_version"."version_created_at" is casted to type "numeric" which is not the same as "bigint", the type of current target database column "public"."credential_version"."version_created_at".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."credential_version"."credential_uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."credential_version"."credential_uuid".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."credential_version"."encrypted_value_uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."credential_version"."encrypted_value_uuid".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."encrypted_value"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."encrypted_value"."uuid".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."encrypted_value"."encryption_key_uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."encrypted_value"."encryption_key_uuid".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."encrypted_value"."updated_at" is casted to type "numeric" which is not the same as "bigint", the type of current target database column "public"."encrypted_value"."updated_at".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."flyway_schema_history"."installed_rank" is casted to type "bigint" which is not the same as "integer", the type of current target database column "public"."flyway_schema_history"."installed_rank".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."flyway_schema_history"."checksum" is casted to type "bigint" which is not the same as "integer", the type of current target database column "public"."flyway_schema_history"."checksum".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."flyway_schema_history"."installed_on" is casted to type "timestamptz" which is not the same as "timestamp without time zone", the type of current target database column "public"."flyway_schema_history"."installed_on".
2019-02-11T17:33:14.400000Z WARNING Source column "public"."flyway_schema_history"."execution_time" is casted to type "bigint" which is not the same as "integer", the type of current target database column "public"."flyway_schema_history"."execution_time".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."password_credential"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."password_credential"."uuid".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."password_credential"."password_parameters_uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."password_credential"."password_parameters_uuid".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."permission"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."permission"."uuid".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."rsa_credential"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."rsa_credential"."uuid".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."ssh_credential"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."ssh_credential"."uuid".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."user_credential"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."user_credential"."uuid".
2019-02-11T17:33:14.401000Z WARNING Source column "public"."user_credential"."password_parameters_uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."user_credential"."password_parameters_uuid".
2019-02-11T17:33:14.408000Z NOTICE ALTER TABLE "public"."certificate_credential" DROP CONSTRAINT IF EXISTS certificate_secret_uuid_fkey CASCADE;
2019-02-11T17:33:14.409000Z NOTICE ALTER TABLE "public"."credential_version" DROP CONSTRAINT IF EXISTS credential_encrypted_value_uuid_fkey CASCADE;
2019-02-11T17:33:14.410000Z NOTICE ALTER TABLE "public"."credential_version" DROP CONSTRAINT IF EXISTS credential_uuid_fkey CASCADE;
2019-02-11T17:33:14.410000Z NOTICE ALTER TABLE "public"."encrypted_value" DROP CONSTRAINT IF EXISTS encryption_key_uuid_fkey CASCADE;
2019-02-11T17:33:14.411000Z NOTICE ALTER TABLE "public"."password_credential" DROP CONSTRAINT IF EXISTS password_parameters_uuid_fkey CASCADE;
2019-02-11T17:33:14.411000Z NOTICE ALTER TABLE "public"."password_credential" DROP CONSTRAINT IF EXISTS password_secret_uuid_fkey CASCADE;
2019-02-11T17:33:14.412000Z NOTICE ALTER TABLE "public"."rsa_credential" DROP CONSTRAINT IF EXISTS rsa_secret_uuid_fkey CASCADE;
2019-02-11T17:33:14.412000Z NOTICE ALTER TABLE "public"."ssh_credential" DROP CONSTRAINT IF EXISTS ssh_secret_uuid_fkey CASCADE;
2019-02-11T17:33:14.413000Z NOTICE ALTER TABLE "public"."user_credential" DROP CONSTRAINT IF EXISTS user_password_parameters_uuid_fkey CASCADE;
2019-02-11T17:33:14.413000Z NOTICE ALTER TABLE "public"."user_credential" DROP CONSTRAINT IF EXISTS user_secret_uuid_fkey CASCADE;
2019-02-11T17:33:14.414000Z SQL TRUNCATE public.certificate_credential,public.credential,public.credential_version,public.encrypted_value,public.encryption_key_canary,public.flyway_schema_history,public.password_credential,public.permission,public.rsa_credential,public.ssh_credential,public.user_credential;
2019-02-11T17:33:14.527000Z NOTICE COPY public.certificate_credential
2019-02-11T17:33:14.527000Z DEBUG Reader started for public.certificate_credential
2019-02-11T17:33:14.536000Z DEBUG start public.certificate_credential   533
2019-02-11T17:33:14.539000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.539000Z NOTICE COPY public.credential
2019-02-11T17:33:14.539000Z NOTICE COPY public.credential_version
2019-02-11T17:33:14.540000Z NOTICE COPY public.encrypted_value
2019-02-11T17:33:14.540000Z DEBUG Reader started for public.credential_version
2019-02-11T17:33:14.540000Z DEBUG start public.credential_version   545
2019-02-11T17:33:14.540000Z NOTICE COPY public.encryption_key_canary
2019-02-11T17:33:14.540000Z NOTICE COPY public.flyway_schema_history
2019-02-11T17:33:14.540000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.540000Z NOTICE COPY public.password_credential
2019-02-11T17:33:14.540000Z NOTICE COPY public.permission
2019-02-11T17:33:14.540000Z NOTICE COPY public.rsa_credential
2019-02-11T17:33:14.540000Z NOTICE COPY public.ssh_credential
2019-02-11T17:33:14.540000Z NOTICE COPY public.user_credential
2019-02-11T17:33:14.549000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.549000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.550000Z SQL SET search_path TO public;
2019-02-11T17:33:14.550000Z INFO pgsql:copy-rows-from-queue[1]: public.certificate_credential (ca certificate
                                                              ca_name uuid
                                                              transitional)
2019-02-11T17:33:14.554000Z SQL MySQL: sending query: SELECT `type`, `uuid`, `version_created_at`, `credential_uuid`, `encrypted_value_uuid` FROM `credential_version`
2019-02-11T17:33:14.554000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.555000Z DEBUG Reader for public.credential_version is done in 0.015000s
2019-02-11T17:33:14.555000Z DEBUG Reader started for public.encryption_key_canary
2019-02-11T17:33:14.555000Z DEBUG start public.encryption_key_canary   560
2019-02-11T17:33:14.555000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.555000Z DEBUG Finished processing READER for "public.credential_version"  0.015000s
2019-02-11T17:33:14.555000Z SQL SET search_path TO public;
2019-02-11T17:33:14.556000Z INFO pgsql:copy-rows-from-queue[2]: public.credential (uuid name checksum)
2019-02-11T17:33:14.557000Z SQL MySQL: sending query: SELECT `ca`, `certificate`, `ca_name`, `uuid`, `transitional` FROM `certificate_credential`
2019-02-11T17:33:14.558000Z DEBUG Reader for public.certificate_credential is done in 0.031000s
2019-02-11T17:33:14.558000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.558000Z DEBUG Finished processing READER for "public.certificate_credential"  0.031000s
2019-02-11T17:33:14.558000Z DEBUG  stop public.certificate_credential   | 533 .. 564 = 0.031d0
2019-02-11T17:33:14.559000Z DEBUG Writer[1] for public.certificate_credential is done in 0.000000s
2019-02-11T17:33:14.559000Z DEBUG Reader started for public.credential
2019-02-11T17:33:14.559000Z DEBUG start public.credential   564
2019-02-11T17:33:14.559000Z DEBUG Finished processing WRITER for "public.certificate_credential"  0.000000s
2019-02-11T17:33:14.559000Z DEBUG writers-counts[public.certificate_credential] = 0
2019-02-11T17:33:14.562000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.562000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.563000Z SQL SET search_path TO public;
2019-02-11T17:33:14.563000Z INFO pgsql:copy-rows-from-queue[0]: public.encrypted_value (uuid encryption_key_uuid
                                                       encrypted_value nonce
                                                       updated_at)
2019-02-11T17:33:14.574000Z SQL MySQL: sending query: SELECT `uuid`, `name`, `checksum` FROM `credential`
2019-02-11T17:33:14.575000Z DEBUG Reader for public.credential is done in 0.017000s
2019-02-11T17:33:14.575000Z DEBUG Reader started for public.encrypted_value
2019-02-11T17:33:14.575000Z DEBUG start public.encrypted_value   581
2019-02-11T17:33:14.575000Z DEBUG Finished processing READER for "public.credential"  0.017000s
2019-02-11T17:33:14.575000Z DEBUG  stop public.credential   | 564 .. 581 = 0.017d0
2019-02-11T17:33:14.576000Z DEBUG Writer[2] for public.credential is done in 0.000000s
2019-02-11T17:33:14.576000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.576000Z DEBUG Finished processing WRITER for "public.credential"  0.000000s
2019-02-11T17:33:14.576000Z DEBUG writers-counts[public.credential] = 0
2019-02-11T17:33:14.580000Z SQL MySQL: sending query: SELECT `uuid`, `encryption_key_uuid`, `encrypted_value`, `nonce`, `updated_at` FROM `encrypted_value`
2019-02-11T17:33:14.581000Z DEBUG Reader for public.encrypted_value is done in 0.006000s
2019-02-11T17:33:14.581000Z DEBUG Reader started for public.flyway_schema_history
2019-02-11T17:33:14.581000Z DEBUG start public.flyway_schema_history   587
2019-02-11T17:33:14.581000Z DEBUG Finished processing READER for "public.encrypted_value"  0.006000s
2019-02-11T17:33:14.582000Z DEBUG  stop public.encrypted_value   | 581 .. 587 = 0.006d0
2019-02-11T17:33:14.582000Z DEBUG Writer[0] for public.encrypted_value is done in 0.000000s
2019-02-11T17:33:14.582000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.582000Z DEBUG Finished processing WRITER for "public.encrypted_value"  0.000000s
2019-02-11T17:33:14.582000Z DEBUG writers-counts[public.encrypted_value] = 0
2019-02-11T17:33:14.585000Z SQL MySQL: sending query: SELECT `encrypted_value`, `nonce`, `uuid`, `salt` FROM `encryption_key_canary`
2019-02-11T17:33:14.586000Z DEBUG Reader for public.encryption_key_canary is done in 0.032000s
2019-02-11T17:33:14.586000Z DEBUG Reader started for public.password_credential
2019-02-11T17:33:14.586000Z DEBUG start public.password_credential   592
2019-02-11T17:33:14.586000Z DEBUG Finished processing READER for "public.encryption_key_canary"  0.032000s
2019-02-11T17:33:14.590000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.590000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.590000Z SQL SET search_path TO public;
2019-02-11T17:33:14.591000Z INFO pgsql:copy-rows-from-queue[2]: public.credential_version (type uuid
                                                          version_created_at
                                                          credential_uuid
                                                          encrypted_value_uuid)
2019-02-11T17:33:14.592000Z DEBUG  stop public.credential_version   | 545 .. 598 = 0.053d0
2019-02-11T17:33:14.592000Z DEBUG Writer[2] for public.credential_version is done in 0.000000s
2019-02-11T17:33:14.592000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.592000Z DEBUG Finished processing WRITER for "public.credential_version"  0.000000s
2019-02-11T17:33:14.592000Z DEBUG writers-counts[public.credential_version] = 0
2019-02-11T17:33:14.595000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.596000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.596000Z SQL SET search_path TO public;
2019-02-11T17:33:14.597000Z INFO pgsql:copy-rows-from-queue[2]: public.encryption_key_canary (encrypted_value
                                                             nonce uuid salt)
2019-02-11T17:33:14.597000Z ERROR Error while formating a row from "public.encryption_key_canary":
2019-02-11T17:33:14.597000Z ERROR Illegal :UTF-8 character starting at position 0.
2019-02-11T17:33:14.598000Z ERROR Argument X is not a NUMBER: NIL
2019-02-11T17:33:14.598000Z WARNING PostgreSQL warning: there is no transaction in progress
2019-02-11T17:33:14.598000Z DEBUG  stop public.encryption_key_canary   | 560 .. 604 = 0.044d0
2019-02-11T17:33:14.598000Z DEBUG Writer[2] for public.encryption_key_canary is done in 0.000000s
2019-02-11T17:33:14.598000Z DEBUG Finished processing WRITER for "public.encryption_key_canary"  0.000000s
2019-02-11T17:33:14.598000Z DEBUG writers-counts[public.encryption_key_canary] = 0
2019-02-11T17:33:14.598000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.600000Z SQL MySQL: sending query: SELECT `uuid`, `password_parameters_uuid` FROM `password_credential`
2019-02-11T17:33:14.601000Z DEBUG Reader for public.password_credential is done in 0.014000s
2019-02-11T17:33:14.601000Z DEBUG Reader started for public.rsa_credential
2019-02-11T17:33:14.601000Z DEBUG start public.rsa_credential   607
2019-02-11T17:33:14.601000Z DEBUG Finished processing READER for "public.password_credential"  0.014000s
2019-02-11T17:33:14.603000Z SQL MySQL: sending query: SELECT `installed_rank`, `version`, `description`, `type`, `script`, `checksum`, `installed_by`, `installed_on`, `execution_time`, `success` FROM `flyway_schema_history`
2019-02-11T17:33:14.604000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.605000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.605000Z SQL MySQL: sending query: SELECT `public_key`, `uuid` FROM `rsa_credential`
2019-02-11T17:33:14.605000Z DEBUG Reader for public.flyway_schema_history is done in 0.024000s
2019-02-11T17:33:14.605000Z DEBUG Finished processing READER for "public.flyway_schema_history"  0.024000s
2019-02-11T17:33:14.605000Z DEBUG Reader started for public.permission
2019-02-11T17:33:14.606000Z DEBUG start public.permission   611
2019-02-11T17:33:14.606000Z SQL SET search_path TO public;
2019-02-11T17:33:14.606000Z DEBUG Reader for public.rsa_credential is done in 0.004000s
2019-02-11T17:33:14.606000Z DEBUG Reader started for public.user_credential
2019-02-11T17:33:14.606000Z DEBUG start public.user_credential   611
2019-02-11T17:33:14.606000Z INFO pgsql:copy-rows-from-queue[0]: public.flyway_schema_history (installed_rank
                                                             version
                                                             description type
                                                             script checksum
                                                             installed_by
                                                             installed_on
                                                             execution_time
                                                             success)
2019-02-11T17:33:14.606000Z DEBUG Finished processing READER for "public.rsa_credential"  0.004000s
2019-02-11T17:33:14.609000Z DEBUG  stop public.flyway_schema_history   | 587 .. 615 = 0.028d0
2019-02-11T17:33:14.610000Z DEBUG Writer[0] for public.flyway_schema_history is done in 0.001000s
2019-02-11T17:33:14.610000Z DEBUG Finished processing WRITER for "public.flyway_schema_history"  0.001000s
2019-02-11T17:33:14.610000Z DEBUG writers-counts[public.flyway_schema_history] = 0
2019-02-11T17:33:14.610000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.610000Z SQL MySQL: sending query: SELECT `uuid`, `actor`, `read_permission`, `write_permission`, `delete_permission`, `read_acl_permission`, `write_acl_permission`, `path` FROM `permission`
2019-02-11T17:33:14.611000Z DEBUG Reader for public.permission is done in 0.006000s
2019-02-11T17:33:14.611000Z DEBUG Finished processing READER for "public.permission"  0.006000s
2019-02-11T17:33:14.611000Z DEBUG Reader started for public.ssh_credential
2019-02-11T17:33:14.611000Z DEBUG start public.ssh_credential   617
2019-02-11T17:33:14.614000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.615000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.615000Z SQL SET search_path TO public;
2019-02-11T17:33:14.616000Z INFO pgsql:copy-rows-from-queue[0]: public.permission (uuid actor read_permission
                                                  write_permission
                                                  delete_permission
                                                  read_acl_permission
                                                  write_acl_permission path)
2019-02-11T17:33:14.617000Z ERROR Database error 22P02: invalid input syntax for uuid: "\x1f2af3ce1450466196155184abd3304c"
CONTEXT: COPY permission, line 1, column uuid: "\x1f2af3ce1450466196155184abd3304c"
2019-02-11T17:33:14.617000Z DEBUG  stop public.permission   | 611 .. 623 = 0.012d0
2019-02-11T17:33:14.617000Z DEBUG Writer[0] for public.permission is done in 0.000000s
2019-02-11T17:33:14.617000Z DEBUG Finished processing WRITER for "public.permission"  0.000000s
2019-02-11T17:33:14.617000Z DEBUG writers-counts[public.permission] = 0
2019-02-11T17:33:14.617000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.619000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.619000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.620000Z SQL SET search_path TO public;
2019-02-11T17:33:14.620000Z INFO pgsql:copy-rows-from-queue[2]: public.password_credential (uuid
                                                           password_parameters_uuid)
2019-02-11T17:33:14.621000Z DEBUG  stop public.password_credential   | 592 .. 627 = 0.035d0
2019-02-11T17:33:14.621000Z DEBUG Writer[2] for public.password_credential is done in 0.000000s
2019-02-11T17:33:14.621000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.621000Z DEBUG Finished processing WRITER for "public.password_credential"  0.000000s
2019-02-11T17:33:14.622000Z DEBUG writers-counts[public.password_credential] = 0
2019-02-11T17:33:14.626000Z SQL MySQL: sending query: SELECT `uuid`, `username`, `salt`, `password_parameters_uuid` FROM `user_credential`
2019-02-11T17:33:14.627000Z DEBUG Reader for public.user_credential is done in 0.022000s
2019-02-11T17:33:14.627000Z DEBUG Finished processing READER for "public.user_credential"  0.022000s
2019-02-11T17:33:14.627000Z INFO COPY ON ERROR STOP
2019-02-11T17:33:14.631000Z SQL MySQL: sending query: SELECT `public_key`, `uuid` FROM `ssh_credential`
2019-02-11T17:33:14.631000Z DEBUG Reader for public.ssh_credential is done in 0.020000s
2019-02-11T17:33:14.631000Z DEBUG Finished processing READER for "public.ssh_credential"  0.020000s
2019-02-11T17:33:14.633000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.633000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.634000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.634000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.635000Z SQL SET search_path TO public;
2019-02-11T17:33:14.635000Z SQL SET search_path TO public;
2019-02-11T17:33:14.635000Z INFO pgsql:copy-rows-from-queue[3]: public.user_credential (uuid username salt
                                                       password_parameters_uuid)
2019-02-11T17:33:14.635000Z INFO pgsql:copy-rows-from-queue[0]: public.ssh_credential (public_key uuid)
2019-02-11T17:33:14.636000Z DEBUG  stop public.user_credential   | 611 .. 642 = 0.031d0
2019-02-11T17:33:14.636000Z DEBUG Writer[3] for public.user_credential is done in 0.000000s
2019-02-11T17:33:14.636000Z DEBUG Finished processing WRITER for "public.user_credential"  0.000000s
2019-02-11T17:33:14.636000Z DEBUG writers-counts[public.user_credential] = 0
2019-02-11T17:33:14.636000Z DEBUG  stop public.ssh_credential   | 617 .. 642 = 0.025d0
2019-02-11T17:33:14.636000Z DEBUG Writer[0] for public.ssh_credential is done in 0.000000s
2019-02-11T17:33:14.637000Z DEBUG Finished processing WRITER for "public.ssh_credential"  0.000000s
2019-02-11T17:33:14.637000Z DEBUG writers-counts[public.ssh_credential] = 0
2019-02-11T17:33:14.637000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.637000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.638000Z SQL SET search_path TO public;
2019-02-11T17:33:14.638000Z INFO pgsql:copy-rows-from-queue[2]: public.rsa_credential (public_key uuid)
2019-02-11T17:33:14.639000Z DEBUG  stop public.rsa_credential   | 607 .. 645 = 0.038d0
2019-02-11T17:33:14.639000Z DEBUG Writer[2] for public.rsa_credential is done in 0.000000s
2019-02-11T17:33:14.639000Z DEBUG Finished processing WRITER for "public.rsa_credential"  0.000000s
2019-02-11T17:33:14.639000Z DEBUG writers-counts[public.rsa_credential] = 0
2019-02-11T17:33:14.640000Z INFO Done with COPYing data, waiting for indexes
2019-02-11T17:33:14.656000Z NOTICE Completing PostgreSQL database.
2019-02-11T17:33:14.656000Z NOTICE Reset sequences
2019-02-11T17:33:14.682000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.683000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.683000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.684000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.706000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.707000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.708000Z DEBUG BEGIN
2019-02-11T17:33:14.712000Z NOTICE ALTER TABLE "public"."certificate_credential" ADD CONSTRAINT certificate_secret_uuid_fkey FOREIGN KEY (uuid) REFERENCES credential_version(uuid) ON DELETE CASCADE
2019-02-11T17:33:14.714000Z NOTICE ALTER TABLE "public"."credential_version" ADD CONSTRAINT credential_encrypted_value_uuid_fkey FOREIGN KEY (encrypted_value_uuid) REFERENCES encrypted_value(uuid)
2019-02-11T17:33:14.716000Z NOTICE ALTER TABLE "public"."credential_version" ADD CONSTRAINT credential_uuid_fkey FOREIGN KEY (credential_uuid) REFERENCES credential(uuid) ON DELETE CASCADE
2019-02-11T17:33:14.717000Z NOTICE ALTER TABLE "public"."encrypted_value" ADD CONSTRAINT encryption_key_uuid_fkey FOREIGN KEY (encryption_key_uuid) REFERENCES encryption_key_canary(uuid)
2019-02-11T17:33:14.718000Z NOTICE ALTER TABLE "public"."password_credential" ADD CONSTRAINT password_parameters_uuid_fkey FOREIGN KEY (password_parameters_uuid) REFERENCES encrypted_value(uuid)
2019-02-11T17:33:14.720000Z NOTICE ALTER TABLE "public"."password_credential" ADD CONSTRAINT password_secret_uuid_fkey FOREIGN KEY (uuid) REFERENCES credential_version(uuid) ON DELETE CASCADE
2019-02-11T17:33:14.721000Z NOTICE ALTER TABLE "public"."rsa_credential" ADD CONSTRAINT rsa_secret_uuid_fkey FOREIGN KEY (uuid) REFERENCES credential_version(uuid) ON DELETE CASCADE
2019-02-11T17:33:14.723000Z NOTICE ALTER TABLE "public"."ssh_credential" ADD CONSTRAINT ssh_secret_uuid_fkey FOREIGN KEY (uuid) REFERENCES credential_version(uuid) ON DELETE CASCADE
2019-02-11T17:33:14.724000Z NOTICE ALTER TABLE "public"."user_credential" ADD CONSTRAINT user_password_parameters_uuid_fkey FOREIGN KEY (password_parameters_uuid) REFERENCES encrypted_value(uuid)
2019-02-11T17:33:14.725000Z NOTICE ALTER TABLE "public"."user_credential" ADD CONSTRAINT user_secret_uuid_fkey FOREIGN KEY (uuid) REFERENCES credential_version(uuid) ON DELETE CASCADE
2019-02-11T17:33:14.733000Z NOTICE Executing SQL block for after load
2019-02-11T17:33:14.755000Z DEBUG SET client_encoding TO 'utf8'
2019-02-11T17:33:14.756000Z DEBUG SET application_name TO 'pgloader'
2019-02-11T17:33:14.757000Z DEBUG BEGIN
2019-02-11T17:33:14.757000Z SQL set session_replication_role = default;
2019-02-11T17:33:14.757000Z LOG report summary reset
                   table name     errors       read   imported      bytes      total time       read      write
-----------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
                  before load          0          1          1                     0.012s    
              fetch meta data          0         21         21                     0.115s    
            Drop Foreign Keys          0         10         10                     0.015s    
                     Truncate          0         11         11                     0.089s    
-----------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
public.certificate_credential          0          0          0                     0.031s     0.031s  
    public.credential_version          0          0          0                     0.053s     0.015s  
 public.encryption_key_canary          2          1          0                     0.044s     0.032s  
            public.credential          0          0          0                     0.017s     0.017s  
       public.encrypted_value          0          0          0                     0.006s     0.006s  
 public.flyway_schema_history          0         65         65     8.3 kB          0.028s     0.024s     0.001s
   public.password_credential          0          0          0                     0.035s     0.014s  
        public.rsa_credential          0          0          0                     0.038s     0.004s  
            public.permission          1          2          0                     0.012s     0.006s  
       public.user_credential          0          0          0                     0.031s     0.022s  
        public.ssh_credential          0          0          0                     0.025s     0.020s  
-----------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
      COPY Threads Completion          0          4          4                     0.099s    
              Reset Sequences          0          0          0                     0.035s    
          Create Foreign Keys          0         10         10                     0.015s    
             Install Comments          0          0          0                     0.000s    
                   after load          0          1          1                     0.024s    
-----------------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
            Total import time          3         68         65     8.3 kB          0.173s    
2019-02-11T17:33:14.793000Z INFO Stopping monitor

[mitchellhuang]

I can confirm this issue on converting binary(16) to uuid. Even with the following type conversion:

cast column user.id to uuid using varbinary-to-string

I am getting:

2019-02-21T21:54:12.600000Z ERROR Error while formating a row from "public.\"user\"":
2019-02-21T21:54:12.600000Z ERROR Illegal :UTF-8 character starting at position 1.
2019-02-21T21:54:12.601000Z ERROR The value
  NIL
is not of type
  NUMBER
when binding SB-KERNEL::X

[mitchellhuang]

I believe the illegal character is the slash in \x that is prefixed by pgloader (or pg/mysql client?)

[djvdorp]

@dimitri is there anything else I could provide to help out more, or to help test maybe?

@mitchellhuang thank you for confirming this issue as well!

[tensberg]

I have the same issue as @mitchellhuang when using the varbinary-to-string conversion. I guess the problem is that the conversion tries to interpret the varbinary as an UTF-8 encoded input string, which is not valid for arbitrary binary data. What is needed is a conversion which outputs a string with each byte of the varbinary in hexadecimal representation. (Basically the "\x88c2982f428a46b7b71b210618ae1658" from the original error report without the \x.)

I successfully imported my data by copy-pasting the bin2hex function I found in http://truledger.com/src/utilities.lisp into a transforms file and using it.

[dimitri]

Hi @tensberg ; thanks for additional input and having a transformation function that works. Can you show us some sample values as found in MySQL and in PostgreSQL after the migration?

[djvdorp]

Thank you @tensberg! Since I am pretty new to pgloader, could you describe how to create and use the bin2hex function from a new transforms file?

[dimitri]

I added a transformation function in pgloader itself, because I think I understood the situation better after seeing what @tensberg is using now. Please try to build from sources and then use a casting rule such as the following:

      column encryption_key_canary.uuid
          to uuid drop typemod using byte-vector-to-hexstring

It should just work, at least it does here:

CL-USER> (pgloader.transforms::byte-vector-to-hexstring #(136 194 152 47 66 138 70 183 183 27 33 6 24 174 22 88))
"88c2982f428a46b7b71b210618ae1658"

And given 0x88C2982F428A46B7B71B210618AE1658 in MySQL, I now have the following in PostgreSQL:

pgloader# table mysql.encryption_key_canary ;                                                                                               ─[ RECORD 1 ]───┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
encrypted_value │ \x1f36f183d7ee47c71453850b756945c16d9d711b2f0594e5d5e54d1ec94e081716ab8642aa60f84b50f69454d098122b7136a0deb3af200c2c5c7500bdfa0bd9689ccbf10a76972374882b304f7f15a227e815989fc87eeb72612396f569c662e72a2a7555e654605a3b83c1c753297832e52c5961e81ebc60dc43d929abab8cb14601defed121604ceb26210ab6d724
nonce           │ \x044aa707df17021e55e9a1e4
uuid            │ 88c2982f-428a-46b7-b71b-210618ae1658
salt            │ \xae7f18028e7984fb5630f7d23fb77999c6ca7cf5355ef0194f3f16521ea7ec503f566229ed8dc5efbbe9c12ba491bddc939fe60fa31fb9af123b2b4d5b7a61fe

[tensberg]

That was fast, thanks! I just tested the build from source and byte-vector-to-hexstring works for me.

Just for reference I wanted to add that this works because PostgreSQL accepts UUID strings without - as well as with them (https://www.postgresql.org/docs/9.4/datatype-uuid.html). I guess an alternative conversion function could convert the byte vector to UUID and then output it as string similar to how sql-server-uniqueidentifier-to-uuid does it but byte-vector-to-hexstring works just as well.

[dimitri]

It seems to me that targeting hex string as an output would be more generally useful? Happy that it works just fine with UUID ;-)

[djvdorp]

@dimitri this seems to work for about 95% of my occurrences, however this one still fails:

• [x] pgloader --version

built from sources
pgloader version "3.6.957caa8"
compiled with SBCL 1.3.1.debian

• [x] did you test a fresh compile from the source tree?
  yes, same issue and output:

pgloader version "3.6.957caa8"
compiled with SBCL 1.3.1.debian

• [x] did you search for other similar issues?
  yes

• [x] how can I reproduce the bug?

  1. mysql source table definition and data (mysqldump with --hex-blob)

+----------------------+--------------+------+-----+---------+-------+
| Field                | Type         | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| uuid                 | binary(16)   | NO   | PRI | NULL    |       |
| actor                | varchar(255) | NO   |     | NULL    |       |
| read_permission      | tinyint(1)   | NO   |     | 0       |       |
| write_permission     | tinyint(1)   | NO   |     | 0       |       |
| delete_permission    | tinyint(1)   | NO   |     | 0       |       |
| read_acl_permission  | tinyint(1)   | NO   |     | 0       |       |
| write_acl_permission | tinyint(1)   | NO   |     | 0       |       |
| path                 | varchar(255) | NO   | MUL | NULL    |       |
+----------------------+--------------+------+-----+---------+-------+

--
-- Table structure for table `permission`
--

DROP TABLE IF EXISTS `permission`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `permission` (
  `uuid` binary(16) NOT NULL,
  `actor` varchar(255) NOT NULL,
  `read_permission` tinyint(1) NOT NULL DEFAULT '0',
  `write_permission` tinyint(1) NOT NULL DEFAULT '0',
  `delete_permission` tinyint(1) NOT NULL DEFAULT '0',
  `read_acl_permission` tinyint(1) NOT NULL DEFAULT '0',
  `write_acl_permission` tinyint(1) NOT NULL DEFAULT '0',
  `path` varchar(255) NOT NULL,
  PRIMARY KEY (`uuid`),
  UNIQUE KEY `permission_path_actor_uindex` (`path`,`actor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `permission`
--

LOCK TABLES `permission` WRITE;
/*!40000 ALTER TABLE `permission` DISABLE KEYS */;
INSERT INTO `permission` VALUES (0x1F2AF3CE1450466196155184ABD3304C,'uaa-client:credhub_admin_client',1,1,1,1,1,'/*'),(0x34AC9E6C435B4D74B108333B41FF1F72,'uaa-client:cc_service_key_client',1,0,0,0,0,'/*');
/*!40000 ALTER TABLE `permission` ENABLE KEYS */;
UNLOCK TABLES;

1. postgres destination table definition (as defined by software vendor)

--
-- Name: permission; Type: TABLE; Schema: public; Owner: cfdbadmin
--

CREATE TABLE public.permission (
    uuid uuid NOT NULL,
    actor character varying(255) NOT NULL,
    read_permission boolean DEFAULT false NOT NULL,
    write_permission boolean DEFAULT false NOT NULL,
    delete_permission boolean DEFAULT false NOT NULL,
    read_acl_permission boolean DEFAULT false NOT NULL,
    write_acl_permission boolean DEFAULT false NOT NULL,
    path character varying(255) NOT NULL
);

ALTER TABLE public.permission OWNER TO xxx;

1. put a uuid in mysql binary(16) type column
2. run pgloader with command file below
3. it won't work because ???: ERROR Database error 22P02: invalid input syntax for uuid: "\x1f2af3ce1450466196155184abd3304c" CONTEXT: COPY permission, line 1, column uuid: "\x1f2af3ce1450466196155184abd3304c"

LOAD DATABASE
        FROM mysql://xxx.rds.amazonaws.com/credhub
        INTO postgresql://xxx.rds.amazonaws.com/credhub

 WITH data only

 ALTER SCHEMA 'credhub' RENAME TO 'public'

cast column permission.uuid to uuid drop typemod using byte-vector-to-hexstring

 BEFORE LOAD DO
 $$ set session_replication_role = replica; $$

 AFTER LOAD DO
 $$ set session_replication_role = default; $$
;

• [x] pgloader output you obtain

WARNING Source column "public"."permission"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."permission"."uuid".
ERROR Database error 22P02: invalid input syntax for uuid: "\x1f2af3ce1450466196155184abd3304c"
CONTEXT: COPY permission, line 1, column uuid: "\x1f2af3ce1450466196155184abd3304c"

• [x] data that is being loaded, if relevant

none, because it has aborted

• [x] How the data is different from what you expected, if relevant
  if pgloader would strip off the \x hexadecimal character escape it should work fine, tested that manually by doing this in psql, see below:

credhub=> insert into permission values ('\x1f2af3ce1450466196155184abd3304c', 'uaa-client:cc_service_key_client', true, true, true, true, true, '/*');
ERROR:  invalid input syntax for uuid: "\x1f2af3ce1450466196155184abd3304c"
LINE 1: insert into permission values ('\x1f2af3ce1450466196155184ab...

vs

credhub=> insert into permission values ('1f2af3ce1450466196155184abd3304c', 'uaa-client:cc_service_key_client', true, true, true, true, true, '/*');
INSERT 0 1

Would you be able to help me out in resolving this last failing occurrence (the remaining 5%), as I am confused why this one is not working but the other ones are, and I would like to make it 100% ?

[dimitri]

The problem here seems to be that your casting rule is not applied to the table/column in the example, and I'm not sure why yet. We can see that in the following warning in the logs. pgloader is then using the default conversion functions, hence the \x... notation for binary values.

WARNING Source column "public"."permission"."uuid" is casted to type "bytea" which is not the same as "uuid", the type of current target database column "public"."permission"."uuid".

[djvdorp]

@dimitri is there anything else I could provide to help out more, or to help test maybe?

[dimitri]

Well I'm not too sure. Can you try with casting the UUID source data type as a whole with the new casting rule?