MySQL type Unsigned Int migrates to a bigint

[abstract-thinking]

Hi @dimitri,

The first time I made a migration from MySQL to PostgreSQL and using pgloader 3.6.7.

After running the command pgloader mysql://mysql_user:mysql_password@localhost/database postgresql://postgresql_user:postgresql_pwassword@localhost/database

2023-05-24T12:56:46.439000Z LOG pgloader version "3.6.7~devel"
2023-05-24T12:56:46.439000Z ERROR PostgreSQL Database error 42804: foreign key constraint fk_id cannot be implemented
DETAIL:  Key columns fk_id and id are of incompatible types: numeric und bigint.
QUERY: ALTER TABLE db.activation ADD CONSTRAINT fk_id FOREIGN KEY(caid) REFERENCES db.ca(id) ON UPDATE RESTRICT ON DELETE RESTRICT

By a deeper look I confused.

First I recognized that the MySQL table id is chagned to bigint.

CREATE TABLE `ca` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
}

CREATE TABLE db.ca (
    id bigint NOT NULL,
)

By reading the default rules I was expecting bigserial. type bigint with extra auto_increment to bigserial

Further the bigint(20) is migrated to the numeric postgresql data type, which produces the problem

CREATE TABLE `activation` (
  `caid` bigint(20) unsigned DEFAULT NULL,
  KEY `fk_id ` (`caid`),
  CONSTRAINT `fk_id ` FOREIGN KEY (`caid`) REFERENCES `ca` (`id`),
}

CREATE TABLE db.activation (
    id bigint NOT NULL,
    caid numeric,
)

Not really sure by reading the rules what I should expecte here. At least I would expect a bigint to be constant with the primary key casting.

Is there a way how I can solve this issue?

Thanks, Markus

[hahyes]

@abstract-thinking type bigint with extra auto_increment to bigserial means "convert bigint from MySQL to bigserial in Postgres if there is extra auto_increment as default value"

You need to make own rules to change it: https://pgloader.readthedocs.io/en/v3.6.7/ref/mysql.html#mysql-database-casting-rules