dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.42k stars 546 forks source link

Seems the MySQL casting rules are not being applied #1130

Open myles-mcdonnell opened 4 years ago

myles-mcdonnell commented 4 years ago

Hi

I'm migrating a DB from MySql 8.x to PG 10.2.x When I run:

pgloader mysql://root:example@localhost:3307/goodtorent postgres://postgres:postgres@localhost:5433/postgres

The result is:

2020-04-08T12:31:58.016000+01:00 LOG pgloader version "3.6.2"
2020-04-08T12:31:58.017000+01:00 LOG Data errors in '/private/tmp/pgloader/'
2020-04-08T12:31:58.089000+01:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@localhost:3307/goodtorent {1005218FA3}>
2020-04-08T12:31:58.089000+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@localhost:5433/postgres {100538ED93}>
2020-04-08T12:31:58.517000+01:00 ERROR Database error 42601: syntax error at or near "unsigned"
QUERY: CREATE TABLE goodtorent.admin_call_comments 
(
  id               int unsigned not null,
  created_at       timestamptz,
  updated_at       timestamptz,
  deleted_at       timestamptz,
  commentable_id   int unsigned,
  user_id          int unsigned,
  commentable_type varchar(255),
  comment          text
);
2020-04-08T12:31:58.517000+01:00 FATAL Failed to create the schema, see above.
2020-04-08T12:31:58.517000+01:00 LOG report summary reset

but if I understand the documentation correctly this shouldn't happen due to the default casting rules

https://pgloader.readthedocs.io/en/latest/ref/mysql.html#default-mysql-casting-rules

pgloader mysql://root:example@localhost:3307/goodtorent postgres://postgres:postgres@localhost:5433/postgres

Thank you Myles

dimitri commented 4 years ago

Hi @myles-mcdonnell ; can you please paste the output of the following query done in your MySQL source database?

  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 = 'goodtorent' and table_name = 'admin_call_comments'
order by table_name, ordinal_position;
PSzczepanski1996 commented 4 years ago

I have the same issue now and I use macOS Catalina 10.15.4 with newest pgloader from brew (same as @myles-mcdonnell).

2020-04-17T11:34:48.008000+01:00 LOG pgloader version "3.6.2"
2020-04-17T11:34:48.083000+01:00 LOG Migrating from #<MYSQL-CONNECTION mysql://patryk@localhost:3306/stolarz {1005CC7A33}>
2020-04-17T11:34:48.084000+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://patryk@localhost:5432/stolarz {1005CC9223}>
2020-04-17T11:34:48.268000+01:00 ERROR Database error 42601: syntax error at or near "unsigned"
QUERY: CREATE TABLE public.tagging_taggeditem 
(
  id              int not null,
  object_id       int unsigned not null,
  content_type_id int not null,
  tag_id          int not null
);
2020-04-17T11:34:48.268000+01:00 FATAL Failed to create the schema, see above.
2020-04-17T11:34:48.268000+01:00 LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
  fetch meta data          0        140                     0.060s
   Create Schemas          0          0                     0.001s
 Create SQL Types          0          0                     0.004s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------
PSzczepanski1996 commented 4 years ago

Ok, so I noticed that there is no int variable type (only integer), so I tried to test this script:

LOAD DATABASE
        FROM mysql://patryk:pass1234@localhost/stolarz
        INTO pgsql://patryk:pass1234@localhost/stolarz
WITH
        batch rows = 1000,
        batch size = 512 MB,
        prefetch rows = 20000
CAST
        type datetime to timestamp drop default drop not null using zero-dates-to-null,
        type int when unsigned to bigint drop typemod

ALTER SCHEMA 'stolarz' RENAME TO 'public';

Convertation passed, but I noticed that id were null in my DB (I use Django for my projects), so something went wrong. Gonna check that later.