添加链接
link管理
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接
相关文章推荐
另类的麦片  ·  新白廣城際即將通車 ...·  2 周前    · 
稳重的海豚  ·  解决Vuetify ...·  1 年前    · 
发呆的皮带  ·  php的fastcgi_finish_req ...·  1 年前    · 
逃课的紫菜汤  ·  Enter Parameter Value ...·  1 年前    · 

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

Closed
@djvdorp

Description

  • pgloader --version
  • pgloader version "3.5.2"
    compiled with SBCL 1.3.1.debian
    
  • 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
    

    did you search for other similar issues?

    how can I reproduce the bug?

  • 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;
    
  • 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;
  • put a uuid in mysql binary(16) type column
  • run pgloader with command file below
  • 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".
  • the destination type is uuid in postgres, not bytea, as detected correctly
  • 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; $$
    
  • 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"
    
  • data that is being loaded, if relevant
  • none, because it has aborted
    
  • 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...
    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.