MySQL conversion syntax error at or near "\"

[Lucianod28]

3.6.7~devel

I'm attempting to migrate my MySQL database with the following command: pgloader mysql://root:password@db/ATDS postgresql://postgres:password@postgres/atds but I get:

3.6.7~devel"
2023-11-23T19:06:51.009999Z LOG Data errors in '/tmp/pgloader/'
2023-11-23T19:06:51.059997Z LOG Migrating from #<MYSQL-CONNECTION mysql://root@db:3306/ATDS {1005FA9383}>
2023-11-23T19:06:51.059997Z LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@postgres:5432/atds {1006178FA3}>

2023-11-23T19:06:51.983390Z ERROR Database error 42601: syntax error at or near "\"
QUERY: CREATE TABLE atds.webatds_assettrackinglog 
(
  id                serial not null,
  eta               timestamptz not null,
  distance          double precision not null,
  speed             double precision not null,
  timestamp         timestamptz not null,
  lat               double precision not null,
  lon               double precision not null,
  asset_id          int not null,
  mile_marker_id    int,
  voyage_tracker_id int not null,
  event_type        int not null,
  notes             text not null default '_utf8mb3\'\'',
  miles_made        double precision,
  event_end         timestamptz,
  event_start       timestamptz
);
2023-11-23T19:06:51.993390Z FATAL Failed to create the schema, see above.
2023-11-23T19:06:51.993390Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
  fetch meta data          0       1055                     0.720s
   Create Schemas          0          0                     0.010s
 Create SQL Types          0          0                     0.010s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  -------------

It seems the problem is the '_utf8mb3\'\'' string which uses backslash-escaped single-qoutes. Any idea on how to fix this? I can't seem to find anything. Thanks!

My MySQL schema:

CREATE TABLE `webatds_assettrackinglog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ETA` datetime(6) NOT NULL,
  `distance` double NOT NULL,
  `speed` double NOT NULL,
  `timestamp` datetime(6) NOT NULL,
  `lat` double NOT NULL,
  `lon` double NOT NULL,
  `asset_id` int NOT NULL,
  `mile_marker_id` int DEFAULT NULL,
  `voyage_tracker_id` int NOT NULL,
  `event_type` int NOT NULL,
  `notes` longtext NOT NULL DEFAULT (_utf8mb3''),
  `miles_made` double DEFAULT NULL,
  `event_end` datetime(6) DEFAULT NULL,
  `event_start` datetime(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `webatds_assettrackin_asset_id_16f05be5_fk_webatds_c` (`asset_id`),
  KEY `webatds_assettrackin_voyage_tracker_id_207a1fe7_fk_webatds_s` (`voyage_tracker_id`),
  KEY `webatds_assettrackin_mile_marker_id_806e3a71_fk_webatds_m` (`mile_marker_id`),
  CONSTRAINT `webatds_assettrackin_asset_id_16f05be5_fk_webatds_c` FOREIGN KEY (`asset_id`) REFERENCES `webatds_companyasset` (`id`),
  CONSTRAINT `webatds_assettrackin_mile_marker_id_806e3a71_fk_webatds_m` FOREIGN KEY (`mile_marker_id`) REFERENCES `webatds_milemarker` (`id`),
  CONSTRAINT `webatds_assettrackin_voyage_tracker_id_207a1fe7_fk_webatds_s` FOREIGN KEY (`voyage_tracker_id`) REFERENCES `webatds_statuslog` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

[fmasclef]

Same issue on character {. Quite the same CREATE statement.

2024-02-28T14:16:45.811995Z ERROR Database error 42601: syntax error at or near "{"
QUERY: CREATE TABLE destination.awesome_table
(
  id                       char(36) not null,
  my_awesome_field           json not null default '_utf8mb4\'{}\''
);
2024-02-28T14:16:45.815995Z FATAL Failed to create the schema, see above.

[Gitii]

I am also affected. I worked around this issue by temporally removing the default value and later adding it back (in the destination database).

[alenaobraz]

I have the same issue in my migration:

2024-04-16T13:54:31.640007Z ERROR Database error 42601: syntax error at or near "["
QUERY: CREATE TABLE public.email_template
(
  id               int not null,
  type             varchar(50) not null,
  status           smallint not null,
  version          int not null,
  subject          varchar(250) not null,
  body             text not null,
  created_at       timestamptz not null,
  updated_at       timestamptz not null,
  sender_id        int,
  base_template_id int,
  title            varchar(255),
  settings         json default '_utf8mb4\'[]\''
);
2024-04-16T13:54:31.660007Z FATAL Failed to create the schema, see above.
2024-04-16T13:54:31.668007Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
  fetch meta data          0       1514                     0.520s
   Create Schemas          0          0                     0.004s
 Create SQL Types          0          0                     0.020s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

Tried to fix it with something like before load do $$ alter table email_template alter settings set default null; $$ but had no luck