Pgloader: No import and no error message

[fabswt]

Hi,

I'm new to Postgres and pgloader. My goal is to migrate a database from MySQL to Postgres. But, first, I'm trying to run the tutorial on the topic from the pgloader website. _[Quick note in passing: the link to download the test database “download the whole database at ” is broken.]_

I installed MySQL, Postgres and pgloader on an Amazon EC2 instance running Ubuntu 16.04, like so:

sudo apt-get update

sudo apt-get install -y mariadb-server mariadb-client

wget http://ergast.com/downloads/f1db.sql.gz
gunzip f1db.sql.gz
sudo mysql -u root
create database f1db;
USE f1db
source f1db.sql
show tables;

sudo apt-get install -y software-properties-common
sudo add-apt-repository -y "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
sudo apt-get install -y wget
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-9.6
export PATH="${PATH}:/usr/lib/postgresql/9.6/bin"

sudo su - postgres
createdb f1db

sudo apt-get install -y pgloader

pgloader mysql://root@localhost/f1db pgsql://f1db


# And finally check whether the import produced anything
sudo su - postgres
\c f1db
\dt
pg_dump -U username f1db >>exported.sql
ls -al exported.sql

However, \dt returns No relations found. and the exported file is empty. i.e.: the Postgres database is empty and no data was migrated.

Running pgloader mysql://root@localhost/f1db pgsql://f1db only produced this:

2017-07-28T17:51:11.016000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2017-07-28T17:51:11.019000Z LOG Data errors in '/tmp/pgloader/'

And the only log file there is pgloader.log and does not provide any useful information:

2017-07-28T17:51:11.009000Z NOTICE Starting pgloader, log system is ready.
2017-07-28T17:51:11.016000Z LOG Main logs in '/tmp/pgloader/pgloader.log'
2017-07-28T17:51:11.019000Z LOG Data errors in '/tmp/pgloader/'

I think it would be useful to have confirmation information as to whether anything was migrated when running pgloader, for the sake of clarity.

What am I doing wrong? What should I try next?

[fabswt]

Turns out I was missing a slash in the Postgres URL, and so does the tutorial:

This line:

$ pgloader mysql://root@localhost/f1db pgsql://f1db

Should read:

$ pgloader mysql://root@localhost/f1db pgsql:///f1db

Now, I am getting useful error messages.

Still following the tutorial, I'm now running into this:

$ pgloader mysql://root@localhost/f1db pgsql:///f1db 
2017-07-29T09:20:57.015000+01:00 LOG Main logs in '/private/tmp/pgloader/pgloader.log'
2017-07-29T09:20:57.017000+01:00 LOG Data errors in '/private/tmp/pgloader/'
2017-07-29T09:20:57.426000+01:00 ERROR Database error 42601: syntax error at or near "0000"
QUERY: CREATE TABLE races 
(
  raceid    bigserial not null,
  year      bigint not null default '0',
  round     bigint not null default '0',
  circuitid bigint not null default '0',
  name      varchar(255) not null default '''',
  date      date not null default ''0000-00-00'',
  time      time default NULL,
  url       varchar(255) default NULL
);
2017-07-29T09:20:57.426000+01:00 FATAL Failed to create the schema, see above.
       table name       read   imported     errors      total time       read      write
-----------------  ---------  ---------  ---------  --------------  ---------  ---------
  fetch meta data         33         33          0          0.076s                     
 Create SQL Types          0          0          0          0.003s                     
    Create tables          0          0          0          0.000s                     
-----------------  ---------  ---------  ---------  --------------  ---------  ---------

I thought the default MySQL casting rules were supposed to take care of it. Is it an issue with casting or an issue with the double single quotes around the date?

Could you try and run the tutorial yourself @dimitri, if you have the time?

[dimitri]

As seen above I fixed the main issues here. Of course I did try the tutorial myself, but made copy-paste errors in one way or the other, which happens sometimes.

About the lack of error output, it turns out being a nasty race condition when the monitoring thread doesn't live long enough and is asked to stop basically before it started. We were also loosing normal startup messages but for some reason I didn't notice before.

For the last point about the conversion itself, it runs quite fine here. Which version of pgloader are you using? I am obtaining the following table schema:

2017-08-10T21:53:48.765000+02:00 NOTICE CREATE TABLE f1db.races 
(
  raceid    bigserial not null,
  year      bigint not null default '0',
  round     bigint not null default '0',
  circuitid bigint not null default '0',
  name      varchar(255) not null default '',
  date      date not null,
  time      time,
  url       varchar(255)
);

And we can poke into the MySQL database schema I am working with, too:

| races | CREATE TABLE `races` (
  `raceId` int(11) NOT NULL AUTO_INCREMENT,
  `year` int(11) NOT NULL DEFAULT '0',
  `round` int(11) NOT NULL DEFAULT '0',
  `circuitId` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `time` time DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`raceId`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM AUTO_INCREMENT=989 DEFAULT CHARSET=utf8

[fabswt]

Thanks for the update!

Regarding the error I'd mentioned on the f1db database conversion, I'm pretty sure I'd fixed it on my setup (EC2 instance) by granting privileges to theubuntu user, i.e.:

sudo mysql
SELECT User FROM mysql.user;
SHOW GRANTS FOR 'root'@'localhost'; # ALL
GRANT ALL PRIVILEGES ON *.* TO 'ubuntu'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD' with grant option;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'ubuntu'@'localhost'; # SELECT only

And then:

sudo pgloader mysql://ubuntu@localhost/f1db pgsql:///f1db

Thank you for pgloader!

[dimitri]

Closing the issue then. Enjoy PostgreSQL!

[robtic154]

Am facing some issues with pgloader to a remote host

pgloader mysql://root:[email protected]:3306/source_db psql:///source_db

please any help