Pgloader: Mysql => Postgres: Bigint as foreign key - issue with conversion

[nenadalm]

output of the command with error

Loading quicklisp and the pgloader project and its dependencies...
2015-11-06T12:23:05.022000+01:00 LOG Main logs in '/tmp/pgloader/pgloader.log'
2015-11-06T12:23:05.027000+01:00 LOG Data errors in '/tmp/pgloader/'
            table name       read   imported     errors            time
----------------------  ---------  ---------  ---------  --------------
       fetch meta data          3          3          0          3.778s
          create, drop          0          2          0          0.032s
----------------------  ---------  ---------  ---------  --------------
               "table"          0          0          0          0.014s
Index Build Completion          0          0          0          0.013s
----------------------  ---------  ---------  ---------  --------------
        Create Indexes          1          1          0          0.006s
       Reset Sequences          0          1          0          0.017s
          Primary Keys          1          1          0          0.003s
          Foreign Keys          1          0          1          0.001s
              Comments          0          0          0          0.000s
----------------------  ---------  ---------  ---------  --------------
     Total import time          0          0          0          3.858s
2015-11-06T12:23:08.830000+01:00 WARNING PostgreSQL warning: table "table" does not exist, skipping
2015-11-06T12:23:08.831000+01:00 ERROR Database error 42804: foreign key constraint "table_ibfk_1" cannot be implemented
DETAIL: Key columns "table_id" and "id" are of incompatible types: numeric and bigint.
QUERY: ALTER TABLE "table" ADD CONSTRAINT table_ibfk_1 FOREIGN KEY(table_id) REFERENCES "table"(id) ON UPDATE RESTRICT ON DELETE RESTRICT

steps to reproduce

• create mysql structure

-- Adminer 4.2.1 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP DATABASE IF EXISTS `pgloader_test`;
CREATE DATABASE `pgloader_test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci */;
USE `pgloader_test`;

DROP TABLE IF EXISTS `table`;
CREATE TABLE `table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `table_id` (`table_id`),
  CONSTRAINT `table_ibfk_1` FOREIGN KEY (`table_id`) REFERENCES `table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;


-- 2015-11-06 11:26:21

• create postgres db "pgloader_test"
• run migration

$ pgloader.lisp 'mysql://root@localhost/pgloader_test' 'postgresql://postgres:postgres@localhost/pgloader_test'

[nenadalm]

forgot to mention used version

$ pgloader.lisp -V
Loading quicklisp and the pgloader project and its dependencies...
pgloader version "3.2.2"
compiled with SBCL 1.2.11-1.fc22

[dimitri]

With the default MySQL casting rules, id is converted to bigserial (so as to implement auto_increment the standard way, with a sequence, but being lazy and using the PostgreSQL standard's extension serial) whereas table_id is converted to numeric because actually bigint(20) does not fit in the PostgreSQL bigint range.

Add a cast rule for table_id:

make
./build/bin/pgloader --cast "table.table_id to bigint" ...

[nenadalm]

sure - that's how I solved it (sort of - I casted all unsigned bigints from mysql to bigints in postgres). This behaviour just seems a bit strange as it creates invalid table structure.

[dimitri]

Glad you could solve it already. pgloader currently has no code to check that kind of consistency, and it would need to know how to match fkey requirements now. Will consider...

[dimitri]

Closing the issue, please open again with a patch: I will gladly have that implemented.

[fcamblor]

For history, using --cast "table.my_fk_id to bigint" didn't worked for me : with pgloader 3.4.2, I had to :

• add a "column" prefix (a cast needs to start with either "type" or "column"
• remove the typemod otherwise I would get errors during create table statements as bigint(22) was generated and postgres doesn't recognizes this syntax (bigint should be used instead of bigint(22) => typemod needs to be removed

Which makes the following command line working for me : pgloader --cast "column table.my_fk_id to bigin drop typemod"