Pgloader: Problem using the example (sakila) migration from mysql to postgres

[kim-ae]

Hi,
I'm starting to learn how to use your tool and as my first attempt I tried to execute the example script that is in the site:

 LOAD DATABASE  
     FROM      mysql://root@localhost/sakila  
     INTO postgresql://localhost:54393/sakila  

 WITH include drop, create tables, create indexes, reset sequences  

  SET maintenance_work_mem to '128MB',  
      work_mem to '12MB',  
      search_path to 'sakila'  

 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,  
      type date drop not null drop default using zero-dates-to-null,  
      -- type tinyint to boolean using tinyint-to-boolean,  
      type year to integer  

 MATERIALIZE VIEWS film_list, staff_list  

 -- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'  
 -- EXCLUDING TABLE NAMES MATCHING ~<ory>  
 -- DECODING TABLE NAMES MATCHING ~/messed/, ~/encoding/ AS utf8  

 BEFORE LOAD DO  
 $$ create schema if not exists sakila; $$; 

I just modified the information about the postgres to my own (postgresql://postgres@localhost/sakila).
The error is the following:

$ ./pgloader test_loader.load 
2016-03-22T10:53:10.049000-03:00 LOG Main logs in '/tmp/pgloader/pgloader.log'
2016-03-22T10:53:10.052000-03:00 LOG Data errors in '/tmp/pgloader/'
2016-03-22T10:53:10.052000-03:00 LOG Parsing commands from file #P"/home/kim/Documents/database_clima/pgloader_scripts/teste_loader.load"
2016-03-22T10:53:12.456000-03:00 WARNING PostgreSQL warning: table "actor" does not exist, skipping
2016-03-22T10:53:12.456000-03:00 WARNING PostgreSQL warning: table "address" does not exist, skipping
2016-03-22T10:53:12.456000-03:00 WARNING PostgreSQL warning: table "category" does not exist, skipping
2016-03-22T10:53:12.456000-03:00 WARNING PostgreSQL warning: table "city" does not exist, skipping
2016-03-22T10:53:12.657000-03:00 WARNING PostgreSQL warning: table "country" does not exist, skipping
2016-03-22T10:53:12.657000-03:00 WARNING PostgreSQL warning: table "customer" does not exist, skipping
2016-03-22T10:53:12.657000-03:00 WARNING PostgreSQL warning: table "film" does not exist, skipping
2016-03-22T10:53:12.657000-03:00 WARNING PostgreSQL warning: type "film_rating" does not exist, skipping
2016-03-22T10:53:12.658000-03:00 WARNING PostgreSQL warning: type "film_special_features" does not exist, skipping
2016-03-22T10:53:12.658000-03:00 ERROR Database error 42601: syntax error at or near "("
QUERY: CREATE TABLE film 
(
  film_id               serial not null,
  title                 varchar(255) not null,
  description           text,
  release_year          integer(4),
  language_id           smallint not null,
  original_language_id  smallint,
  rental_duration       smallint not null default '3',
  rental_rate           decimal(4,2) not null default '4.99',
  length                smallint,
  replacement_cost      decimal(5,2) not null default '19.99',
  rating                film_rating default 'G',
  special_features      "film_special_features"[],
  last_update           timestamptz not null default CURRENT_TIMESTAMP
);
2016-03-22T10:53:12.658000-03:00 FATAL Failed to create the schema, see above.
       table name       read   imported     errors      total time       read      write
-----------------  ---------  ---------  ---------  --------------  ---------  ---------
      before load          1          1          0          0.030s                     
  fetch meta data         81         81          0          2.272s                     
     create, drop          0          0          0          0.000s                     
-----------------  ---------  ---------  ---------  --------------  ---------  ---------

After trying to execute the create statement direct in the pgadmin I discovered that the problem is the integer(4).

My versions

mysql:

$ mysql -V
mysql  Ver 14.14 Distrib 5.6.28, for debian-linux-gnu (x86_64) using  EditLine wrapper

postgres:

$ psql -V
psql (PostgreSQL) 9.4.6

pgloader:

$ ./pgloader --version
pgloader version "3.3.d1cfe90"
compiled with SBCL 1.2.14.debian

I hope you can help me.
Thanks for your time!

[dimitri]

The original column datatype is year(4) in MySQL, and your CAST rule says that it must be translated to integer(4), which fails. The current default rule for MySQL year datatype is the following, please either remove your own cast rule or at least add the drop typemod part:

type year to integer drop typed

See the current version of the load file at https://github.com/dimitri/pgloader/blob/master/test/sakila.load

[imrantune]

Hi @dimitri

2018-10-04T07:36:04.023505Z LOG Migrating from #<MYSQL-CONNECTION mysql://[email protected]:3306/tune_001 #x302001354D5D>
2018-10-04T07:36:04.024011Z LOG Migrating into #<PGSQL-CONNECTION pgsql://[email protected]:5432/tune_001 #x30200135424D>
2018-10-04T08:11:58.838264Z ERROR Database error 42704: type "datetime" does not exist
QUERY: CREATE TABLE pagila.cb_services 
(
  sid               bigserial not null,
  payment_status    bigint not null,
  service_name      varchar(50) not null,
  userid            bigint not null,
  status            bigint not null,
  deployment_status bigint not null,
  date_added        timestamp,
  date_updated      datetime not null,
  s_type            bigint not null,
  quote_id          varchar(50) not null,
  product_id        varchar(50) not null,
  quote_number      bigint not null,
  account_name      varchar(50) not null,
  account_id        varchar(50) not null
)
WITH (fillfactor = '40');
2018-10-04T08:11:58.854049Z FATAL Failed to create the schema, see above.
2018-10-04T08:11:58.856118Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
      before load          0          3                     0.012s
  fetch meta data          0        349                     0.289s
   Create Schemas          0          0                     0.001s
 Create SQL Types          0        147                     0.142s
    Create tables          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  -------------```

pgloader -V
pgloader version "3.5.2"
compiled with SBCL 1.4.0-1.el7

psql (PostgreSQL) 10.5

I am using current version of load fie https://github.com/dimitri/pgloader/blob/master/test/sakila.load
please help

[dimitri]

@imrantune you didn't paste the PostgreSQL error message, only the query which failed. Please give more information.

[imrantune]

@dimitri

2018-10-04T07:36:04.023505Z LOG Migrating from #<MYSQL-CONNECTION mysql://[email protected]:3306/tune_001 #x302001354D5D> 2018-10-04T07:36:04.024011Z LOG Migrating into #<PGSQL-CONNECTION pgsql://[email protected]:5432/tune_001 #x30200135424D> 2018-10-04T07:36:05.985268Z ERROR Database error 42704: type "datetime" does not exist
complete error here and updated

[dimitri]

I think it's been fixed in https://github.com/dimitri/pgloader/commit/46d14af0d38b26b066876ecf45a2569da31f1d7e ; can you test from a fresh build with current sources?