dbal: Doctrine\DBAL\Exception\InvalidFieldNameException when working with DB schema on PostgreSQL 10

Since upgrading to PostgreSQL 10 I have an error in my projects using Doctrine when I trying to generate migrations or just check the schema difference:

$ sf doc:sche:up --dump-sql                                                                                          
  [Doctrine\DBAL\Exception\InvalidFieldNameException]                                                        
  An exception occurred while executing 'SELECT min_value, increment_by FROM "admin"."acl_classes_id_seq"':  
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist                             
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...                                    
                 ^
  [Doctrine\DBAL\Driver\PDOException]                                             
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...         
                 ^
  [PDOException]                                                                  
  SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist  
  LINE 1: SELECT min_value, increment_by FROM "admin"."acl_classes_id_...         
                 ^
doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]]

Seems like it’s related to nextcloud/server#5930 where @justin-sleep wrote:

This happens specifically because of changes to how PostgreSQL 10 handles sequence metadata.

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 33
Comments: 18 (6 by maintainers)

Links to this issue

wallabag/attempt-3-from_source.md at main - wallabag - Codeberg.org

Commits related to this issue

Add compatibility with PostgreSQL 10 Based on https://github.com/doctrine/dbal/issues/2868 — committed to yoimo/dbal by norbert-yoimo 7 years ago
Add PostgreSQL 10 support https://github.com/doctrine/dbal/issues/2868 — committed to yoimo/dbal by norbert-yoimo 7 years ago
Apply fix for migrations on PostgreSQL 10 https://github.com/doctrine/dbal/issues/2868#issuecomment-383111869 — committed to jeffesquivels/dbal by jeffesquivels 6 years ago
Apply fix for migrations on PostgreSQL 10 https://github.com/doctrine/dbal/issues/2868#issuecomment-383111869 — committed to jeffesquivels/dbal by jeffesquivels 6 years ago
Fix #2868 support PostgreSQL 10 when looking up sequence metadata — committed to werrolf/dbal by werrolf 5 years ago

Most upvoted comments

Suggested solution: PostgreSqlSchemaManager.php line 292:

    $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

to :

    $version = floatval($this->_conn->getWrappedConnection()->getServerVersion());

    if ($version >= 10) {
       $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM pg_sequences WHERE schemaname = \'public\' AND sequencename = '.$this->_conn->quote($sequenceName));
    }
    else
    {
        $data = $this->_conn->fetchAll('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
    }

+66

wzator on Sep 26, 2017

when it is going to be migrated? it definently solves the problem, and because of new sequence architecture on postgresql 10 we can’t define a generic method for that.

iquad on Nov 1, 2017

Guys, what the status of the issue?? When we can expect fix?

Zyqsempai on Dec 1, 2017

#2893 has been merged and added Postgres 10 support, closing this one as duplicate.

Majkl578 on Dec 27, 2017

Whats the fastest way for fix to be released? Should I start PR with tests/fixed-code?

First a failing test case

Ocramius on Apr 30, 2018

Hey, i am not really sure about this, as i never before actually touched any doctrine implementation, but after the above fix worked a couple of weeks ago i set up a new project and apparently some changes were made from 2.6 to 2.7.

The erroneous $data assignment is now in line 311 and fetchAssoc() is used instead of fetchAll().

For me, the following adjustment worked: Replace in PostgreSqlSchemaManager.php line 311:


$data      = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));

with:


$version = floatval($this->_conn->getWrappedConnection()->getServerVersion());

if ($version >= 10) {
    $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM pg_sequences WHERE schemaname = \'public\' AND sequencename = '.$this->_conn->quote($sequenceName));
}
else
{
    $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
}
$sequence += $data;

TiFaBl on Apr 20, 2018

PostgreSQL 10 is out of beta now: https://www.postgresql.org/about/news/1786/

justin-sleep on Oct 5, 2017

We are waiting for PSQL10 support in Travis I guess. Meanwhile you can use code from my PR. https://github.com/doctrine/dbal/pull/2893

simPod on Nov 23, 2017