Migration from MySQL not working as expected given default casts

[cheslijones]

• pgloader --version

  pgloader version "3.6.1"
  compiled with SBCL 1.4.0-1.el7
  

I noticed auto_increments were not coming over as expected (SERIAL) into Postgres. I'm just using the following command to do the migration:

./pgloader mysql://<db_user>:'<database_password>'@localhost/<db_name> postgres://<db_user>:'<database_password>'@localhost/<db_name>`

I might have <db_user> and <db_name> reversed, but anyway...
Looking at the field in question in MySQL shows:

But ends up in Postgres as:

Thought it was a bug until I started reading the documentation and the default casts:

type int with extra auto_increment to serial when (< precision 10)
type int with extra auto_increment to bigserial when (<= 10 precision)
type int to int       when  (< precision 10)
type int to bigint    when  (>= 10 precision)

Based on this, that it came into Postgres as bigint makes sense, since it meets the (>=10 precision) criteria.

However, looking at this one:

type int with extra auto_increment to bigserial when (<= 10 precision)

Should it actually be:

type int with extra auto_increment to bigserial when (>= 10 precision)

It seems like the only criteria for bigserial would just be when int(10) and anything over that would just come over as bigint.

Is my thinking wrong on this?

[phoe]

type int to int       when  (< precision 10)
type int to bigint    when  (>= 10 precision)

IMO that is an obvious bug in the docstring. (>= 10 precision) == (<= precision 10), which is partially covered by the int to int case.

[cheslijones]

Is it also a bug in the defaults of pgloader given that my int(11) AI came into Postgres as bigint, but seems like it should have come over as bigserial? The only way I could think that would happen is that:

type int with extra auto_increment to bigserial when (<= 10 precision)

Is actually in the program config and it should be:

type int with extra auto_increment to bigserial when (>= 10 precision)

At any rate, I'm trying to do a user defined cast and I'm apparently doing something wrong because none of them are working as expected. See below.

The following generate the exact same error. Understandable because bigserial(11) isn't a thing:

--cast "type int with extra auto_increment when (>= precision 10) to bigserial"

2020-04-02T12:47:48.938000-07:00 ERROR Database error 42601: type modifier is not allowed for type "bigint"
QUERY: CREATE TABLE test.advanced_query_log 
(
  id          bigserial(11) not null,
  user_id     bigint not null,
  survey_code varchar(16),
  positions   varchar(255),
  stamp       timestamptz
);
2020-04-02T12:47:48.939000-07:00 FATAL Failed to create the schema, see above.

These successfully transfer over to Postgres, but still show up as bigint instead of bigserial:

--cast "type int with extra auto_increment when (>= precision 10) to bigserial drop typemod"
--cast "type int with extra auto_increment when (>= precision 10) to bigserial drop typemod drop default keep not null"
--cast "type int with extra auto_increment to bigserial drop typemod drop default keep not null"
--cast "type int with extra auto_increment to bigserial drop typemod keep default keep not null"

Probably something I'm doing or overlooking in the documentation. Any suggestions on this?

[cheslijones]

I'm seeing now that there is a foreign key constraint on the field in question. Could this prevent it from coming over correctly and is there a way with pgloader to have it update foreign key constraints to a new type?

[cheslijones]

Ok, more confused now.

I ran the following query in MySQL to find out what tables had AI and int(n) specified, so I could get rid of the int(n) to hopefully clear the issue up:

select TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, COLUMN_TYPE, EXTRA from information_schema.columns
where table_schema = 'test_db' AND extra LIKE '%increment%'
order by table_name,ordinal_position

It returned the following:

• So are the default casts, casts in general, looking at DATA_TYPE, NUMERIC_PRECISION, and EXTRA fields here?
• Not the COLUMN_TYPE like I assumed?
• The (n) in int(n), really COLUMN_TYPE, is basically just being ignored?
• So going through and changing the int(n) will effectively do nothing to fix my issue?

If that is the case, I don't see any reason why the default casts are not working because these fields fit the criteria to come over as BIGSERIAL, but yet they are not.

Any suggestions as to what is going on here?

[dimitri]

Hi @eox-dev ; thanks for using pgloader. I have fixed the typo in the documentation.

Now, your screenshot from the Postgres schema lacks information. Please instead paste the table definition statement in SQL, as obtained by pg_dump or the “DDL” view from a graphical tool (such as OmniDB or DBeaver or one of the many others). You can also consider copy-pasting the result of the \dt users command in psql.