Missing AUTO_INCREMENT to SERIAL conversion from MySQL 8 to Postgres 13

[shoe-diamente]

• [x] pgloader --version

pgloader version "3.6.2"
compiled with SBCL 2.0.11

• [x] did you test a fresh compile from the source tree?

I did not.

• [x] did you search for other similar issues?

Yes. Couldn't find anything.

• [x] how can I reproduce the bug?

This is the minimal mysqldump:

DROP TABLE IF EXISTS `addresses`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `addresses` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11562 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

On MySQL 8.0 (docker image to be precise mysql:8.0). I'm trying to move the data to a Postgres 13 database (docker image postgres:13.1).

The command I've used is:

pgloader mysql://app:root@localhost/app postgresql://app@localhost/app

I would expect the id primary key to become a SERIAL but pgloader just converts it to an INTEGER with no SEQUENCE attached.

According to the documented casting rules it seems it should be converted to a SERIAL int.

I'm going to manually fix this post migration in my case. Just wanted to give you a heads up.

Thanks again for this wonderful tool.

[padinko]

I have same issue migrating from docker mysql:8.0.22 to postgresql 13.2 with pgloader v3.6.7 in docker

i am migrating thousands of tables and some (not all) auto increments are not serials in postgresql

examples:

CREATE TABLE `users` (
  `uid` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=123510 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

creates no serials:

CREATE TABLE users (
	uid int4 NOT NULL,
	CONSTRAINT idx_76096_primary PRIMARY KEY (uid)
);

but

CREATE TABLE `node` (
  `nid` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB AUTO_INCREMENT=40190 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

will generate good with serial

CREATE TABLE node (
	nid bigserial NOT NULL,
	CONSTRAINT idx_73509_primary PRIMARY KEY (nid)
);

---

so maybe missing unsigned is breaking it? can you help me? i can't manually check and fix that thousand tables

[padinko]

I found a problém: mysql column signed int (or signed int4) without defined precision with auto_increment have no mapping to serial in default casting there is definition only for int with precision or unsigned int for me adding this helped:

CAST
  type int when signed with extra auto_increment to serial drop typemod