dimitri / dimitri/pgloader

Regression in MySQL `auto_increment`

Open
#1,767 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Common Lisp
Stars
6.5k
Forks
613
Avg merge
3h 46m
Merged PRs (30d)
1

Description

This configuration file fails:

LOAD DATABASE FROM {{MYSQL_URL}} INTO {{PSQL_URL}}
CAST type bigint with extra auto_increment to bigserial
❯ curl -L -o pgloader.jar \
    https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
❯ java -jar pgloader.jar test.load --dry-run
09:32:21.040 INFO  [main] pgloader.cli - DRY RUN — no data will be copied
09:32:21.044 INFO  [main] pgloader.cli - Parsing commands from file test.load
Error: Parse error at line 6, column 32:
    type bigint with extra auto_increment to bigserial
                               ^
Expected one of:
#"\s+" (followed by end-of-string)
:end-of-string
;
#"\s+"
DECODING
DISTRIBUTE
AFTER
BEFORE
EXCLUDING
INCLUDING
MATERIALIZE
ALTER
CAST
SET
WITH
,
#"\s+"

However, this succeeds. Note the removal of the underscore on line 2.

LOAD DATABASE FROM {{MYSQL_URL}} INTO {{PSQL_URL}}
CAST type bigint with extra auto increment to bigserial

The documentation uses the underscored auto_increment form exclusively and in many places.

I discovered this behavior while trying to figure out why my schema was losing all auto-incrementing primary keys. Removing the underscore allows the rest of the load to succeed, but the resulting database has no auto-incrementing primary keys, or sequences, identity, or serial.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the test.load reproduction and run the documented pgloader.jar dry-run command, comparing auto_increment with auto increment in the CAST clause. Trace the CAST parser and the MySQL-to-PostgreSQL handling for bigint extras; done means the underscored form parses and migrated primary keys retain auto-increment behavior, sequences, serial, or identity.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.