MariaDB to PostgreSQL -> Doctrine migration breaks ID sequence

[ecwio]

Q	A
Doctrine 3 and DBAL 4

Support Question

Sorry for asking here but I have been on this for days and I cannot find a solution.

I have been migrating from MySQL to PostgreSQL

I import my old MariaDB data into Postgre using PGLoader and my app works just fine right after import. Please note that at this point there are no id_seq files generated BUT my app works just fine. I can add new entries without error.

here is a screen of id column after pgloader

All my entities are like so on the id column:

    #[ORM\Column(name: 'id', type: 'integer', nullable: false)]
    #[Id]
    #[ORM\GeneratedValue(strategy: 'IDENTITY')]
    #[Groups(['ad'])]
    private $id;

I now would like to sync doctrine with the new postgres database with

php bin/console doctrine:migrations:sync-metadata-storage
php bin/console make:migration
php bin/console doctrine:migrations:migrate 

the generated migration has for each table something like this

        $this->addSql('ALTER TABLE post ALTER id TYPE INT');
        $this->addSql('ALTER TABLE post ALTER id DROP DEFAULT');
        $this->addSql('ALTER TABLE post ALTER id ADD GENERATED BY DEFAULT AS IDENTITY');
        $this->addSql('ALTER TABLE post ALTER user_id TYPE INT');
        $this->addSql('ALTER TABLE post ALTER "timestamp" TYPE TIMESTAMP(0) WITHOUT TIME ZONE');
        $this->addSql('ALTER TABLE post ALTER updated TYPE TIMESTAMP(0) WITHOUT TIME ZONE');
        $this->addSql('ALTER INDEX idx_16389_uniq_77e0ed58539b0606 RENAME TO UNIQ_77E0ED58539B0606');
        $this->addSql('ALTER INDEX idx_16389_idx_77e0ed58a76ed395 RENAME TO IDX_77E0ED58A76ED395');

after migration doctrine created TWO sequence files per table named: post_id_seq that has the correct sequence number let say 344 and post_id_seq1 that has a sequence of 1 which is NOT correct

here is after migration

now if I insert a new record I get error

duplicate key value violates unique constraint "idx_16661_primary"

That is because Postgres is following post_id_seq1

So my question is what is going on ? I honestly do not understand what I am doing wrong and why doctrine seems to be creating two sequence files ?

[berkut1]

Did you try this? https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html
Maybe you need to migrate to SERIAL with DBAL3+ORM2 first, and then to DBAL4+ORM3.

[ecwio]

Did you try this? https://www.doctrine-project.org/projects/doctrine-dbal/en/4.0/how-to/postgresql-identity-migration.html Maybe you need to migrate to SERIAL with DBAL3+ORM2 first, and then to DBAL4+ORM3.

Before DBAL4+ORM3 I was on mariadb and upgraded DBAL, ORM and changed to PostgreSQL at the same time so I don't think this applies since it is specific to databases in postgres but mine is a mariadb. So I think this case should not apply; we are just in a case of migrating from MariaDB to Postgres right ?

[berkut1]

I don't think so. MariaDB doesn't support IDENTITY, so I think you should handle it as if you are migrating from SERIAL

[ecwio]

I don't think so. MariaDB doesn't support IDENTITY, so I think you should handle it as if you are migrating from SERIAL

as you see my mariadb was on auto_increment and NOT serial :

`id` int(11) NOT NULL AUTO_INCREMENT,

So are you saying I still need to downgrade ORM and DBAL and convert to serial and run the linked command ?

That seems overly complicated. Surely there must be a simpler way... people migrate all the time and doctrine which is a mature project must have a simple way to do this

The weird think is after PGLOADER I have no sequence files and everything works just fine so Doctrine is doing all the mess by creating two seq files per table so It must be a bug or config issue.

[berkut1]

The weird think is after PGLOADER I have no sequence files and everything works just fine so Doctrine is doing all the mess by creating two seq files per table so It must be a bug or config issue.

This is not a bug. IDENTITY uses its own sequence table, so it makes sense that it needs to be created.

So are you saying I still need to downgrade ORM and DBAL and convert to serial and run the linked command ?

Nope, I'm just suggesting that this might be the simplest solution for you, but you can try following the instructions from the link right away

[ecwio]

Nope, I'm just suggesting that this might be the simplest solution for you, but you can try following the instructions from the link right away

I just tried running the command and it does not even work anyway... wrong syntax ..

I managed to get correct sequence files on import I just need a way to tell doctrine, stop create new sequences and use the one that is there....

[ecwio]

This is not a bug. IDENTITY uses its own sequence table, so it makes sense that it needs to be created.

Is it a bug if the sequence already exists and it creates a new one over it ? because I now have a sequence file created right after pgloader and it is correct

[ecwio]

I also had this problem before. Actually, you need to deploy MariaDB and PSQL first before doing anything else. You can use the Servbay tool to deploy and manage MariaDB and PSQL simultaneously.

Hello, please can you elaborate "you need to deploy MariaDB and PSQL first before doing anything else" I don't understand. I have a pgsql and mariadb running. My issue is with doctrine messing it all up. How did you solve the issue ?